SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspxHow creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [&lt;do_not_mail&gt; @ do_not_mail.com]. Make sure to include an explanation of why it works, as well as yourenCommunityServer 2.1 SP2 (Build: 61129.1)re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6347Tue, 22 Apr 2008 17:05:56 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6347Adam Machanic<p>To people responding: Don't just send me a solution. &nbsp;Explain to me WHY it works. &nbsp;Why does your change allow the query processor to work more efficiently? &nbsp;The goal of this challenge is not just to modify a query and happen upon a faster version, but rather to think about the reasons behind it and gain a deeper understanding of the QP. &nbsp;Enjoy!</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6358Wed, 23 Apr 2008 14:55:43 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6358Alexander Kuznetsov<p>Hi Paul,</p>
<p>Why did you post your solution here instead of e-mailing it to Adam? Don't you think it might have skewed the contest just a little bit?</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6360Wed, 23 Apr 2008 15:37:58 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6360Adam Machanic<p>Paul D., next time think before you post.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6362Wed, 23 Apr 2008 18:17:05 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6362david wei<p>Adam, do you think change blat1 to VARCHAR(5) will make this more challenging? &nbsp;People may take the advantage of CHAR(5). :-)</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6363Wed, 23 Apr 2008 19:05:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6363Adam Machanic<p>David:</p>
<p>If you want more of a challenge, try changing all of the 5s to 7s ... I'm not sure if this will be more or less challenging than the VARCHAR(5) change; I'll let you tell me :-)</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6365Wed, 23 Apr 2008 19:51:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6365david wei<p>Adam, not the length issue, is the trailing space issue.</p>
<p>Trailing space makes the difference.</p>
<p>for example:</p>
<p>if 'abcde' like 'a%'</p>
<p> print 'Y' -- returns Y</p>
<p>if 'abcde' like 'a &nbsp;%'</p>
<p> print 'Yes' -- does not return Yes</p>
<p>So if we know the blat1 is fixed 5 characters, we can simply use the left(balt2,5) = balt1 for the join, and a simple HASH join hint will only sacn two tables once, avoid nested loop joins on two 19K rows tables(which caused 1.8M reads).</p>
<p>But if we change blat1 to VARCHAR(5), and truncate the trailing space to make the length vary (from 1 to 5), this will add the chalenge, hence you can not just compare the first 5 characters :-)</p>
<p>INSERT b1</p>
<p>SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1</p>
<p>FROM AdventureWorks.Person.Address</p>
<p>-- Note. you can comment out some sensitive part in my post if you think this will skew the contest.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6366Wed, 23 Apr 2008 20:07:15 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6366Adam Machanic<p>David,</p>
<p>Length is an issue here as well, due to some of the data. &nbsp;Try creating everything with length 7, and play with some of the solutions (assuming you've come up with one -- I don't see a submission from you in my inbox). &nbsp;If your solution happens to be the most common one people have been sending, you'll find that it will no longer return the same results as the example query.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6367Wed, 23 Apr 2008 20:21:27 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6367Denis Gobo<p>David, that didn't make a difference, I am getting the same number of rows back</p>
<p>SELECT blat1,len(blat1),datalength(blat1),b1.blat1 ,left(b2.blat2,5), </p>
<p>len(blat2),datalength(blat2)</p>
<p>FROM b1</p>
<p>JOIN b2 ON &nbsp;&lt;very secret indeed&gt;</p>
<p>len and datalength reveal that there are some rows with 5 and 4 chars</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6368Wed, 23 Apr 2008 20:24:32 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6368david wei<p>Adam, still the trailing space issue, because the minimal length in b2 is 5, not 7. </p>
<p>select min(len((blat2))) from b2</p>
<p>So when you change to 7, blat1 automatic added 2 trailing space to make it CHAR(7); that cause result difference.</p>
<p>I have sent my solution.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6370Wed, 23 Apr 2008 20:36:02 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6370Michelle<p>So Adam,</p>
<p>Do we need to resend you a new solution using CHAR(7), or do we still have a chance to win...? &nbsp; :-)</p>
<p>Thanks,</p>
<p>Michelle.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6371Wed, 23 Apr 2008 20:44:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6371david wei<p>Denis, it does make the difference. The len() does not count the trailing space, but they affect the like operator. </p>
<p>Try create the b3 table use trimmed varchar(5) as below: </p>
<p>(CREATE TABLE b3 (blat3 VARCHAR(5) NOT NULL)</p>
<p>INSERT b3</p>
<p>SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1</p>
<p>FROM AdventureWorks.Person.Address</p>
<p>SELECT count(*) FROM b3 inner JOIN b2 ON &nbsp; &nbsp;b2.blat2 LIKE b3.blat3 + '%'</p>
<p>--Trimmed VARCHAR(5) returns 111181 rows</p>
<p>SELECT count(*) FROM b1 inner JOIN b2 ON &nbsp; &nbsp;b2.blat2 LIKE b1.blat1 + '%'</p>
<p>-- original CAHR(5) returns 109984 rows</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6373Wed, 23 Apr 2008 20:51:28 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6373Denis Gobo<p>David,</p>
<p>I see I was talking about LEFT not LIKE, with LEFT it doesn't matter if it is char or varchar</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6412Fri, 25 Apr 2008 09:51:56 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6412Fabiano Neves Amorim<p>Umm, Denis, I think with the answer you &quot;spilled your beans&quot; about your secret :-(</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6577Fri, 02 May 2008 05:23:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6577Gordon<p>It's all about iteration.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6597Fri, 02 May 2008 16:35:44 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6597Igor Mikhalyev<p>So where are three best queries? &nbsp;Who are the winners?</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6630Mon, 05 May 2008 16:14:08 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6630Adam Machanic<p>Igor: Patience, please. &nbsp;I got a -lot- of responses... Trying to figure out who won now.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#6742Fri, 09 May 2008 21:23:13 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6742too embarrassed to say<p>Criminy. I didn't even NOTICE the char vs. varchar part. Now my answer could be wrong. Blah.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#7125Tue, 03 Jun 2008 21:55:56 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7125Igor Mikhalyev<p>So where are three best queries? &nbsp;Who are the winners?</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#7147Thu, 05 Jun 2008 11:24:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7147Magneto<p>We are waiting .......</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#7274Fri, 13 Jun 2008 02:23:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7274Jonathan Kehayias<p>If you follow this blog, you should understand that Adam has been both busy, and experienced a major setback like the drive failure. &nbsp;If you don't speak publicly, I would recommend giving it a shot, and then you will understand just how much time is dedicated to doing a meaningful presentation. &nbsp;I too was curious about the answers and emailed Adam a few weeks back, and he responded that he is trying to work this out, but he had to start over after the drive crash.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#7343Tue, 17 Jun 2008 15:49:45 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7343BPhilip<p>set &nbsp;statistics io on</p>
<p>SELECT *FROM b1 inner JOIN b2 ON &nbsp; &nbsp;left(b2.blat2,5) = b1.blat1 </p>
<p>I used this query and got results in 2 seconds. Following are my scan results.</p>
<p>(109984 row(s) affected)</p>
<p>Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
<p>Table 'b2'. Scan count 1, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
<p>Table 'b1'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#7386Thu, 19 Jun 2008 10:43:18 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7386Andrew<p>It would be very interesting to see the top performing query...</p>
Solution for the "LIKE vs. ?" Puzzlehttp://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#9165Wed, 01 Oct 2008 19:43:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9165Adam Machanic<p>In late April, I posted a puzzle to test readers' knowledge of SQL Server query processing internals</p>
A year in review, The 31 best blog posts on SQLBlog for 2008http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#10858Wed, 31 Dec 2008 15:37:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10858Denis Gobo<p>Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#11701Tue, 03 Feb 2009 18:19:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:11701gyanendra<p>how we can make like query and in like condition i want to give a hindi text which are exist in the database collumn. and the collumn type is nvarchar.</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#11961Wed, 18 Feb 2009 00:12:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:11961NotNowJohn<p>Since the competition is already finished I would post here my explanation. </p>
<p>The execution plan of the query shows that nested loop join has been used. The reason why is simple - only this join operator is possible without an equijoin predicat. The solution for this problem would be to force usage of hash join operator and it requires at least one equijoin predicat. Simple transformation LIKE =&gt; LEFT solves this problem and hash join is efficient in this scenario.</p>
<p>BTW. the estimated plan for the query</p>
<p>SELECT * FROM b1</p>
<p>JOIN b2 ON b2.blat2 &gt; b1.blat1 </p>
<p>says that this estimated number of rows for the nested loops would be more than 85M!</p>
T-SQL Challenge: Grouped String Concatenationhttp://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#12308Fri, 27 Feb 2009 18:24:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12308Adam Machanic<p>It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#12396Mon, 02 Mar 2009 23:06:43 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12396SqlGuru<p>Please try this its 3sec</p>
<p>select * from b1</p>
<p>inner join b2 </p>
<p>on b1.blat1=substring(b2.blat2,1,5)</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#12397Mon, 02 Mar 2009 23:07:49 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12397SqlGuru<p>typo mistake sorry its 1sec</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#12457Sat, 07 Mar 2009 08:48:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12457Vimvq1987<p>SELECT DISTINCT *</p>
<p>FROM b1</p>
<p>JOIN b2 ON</p>
<p> &nbsp; &nbsp;--b2.blat2 LIKE b1.blat1 + '%'</p>
<p> &nbsp; &nbsp;b1.blat1 = LEFT(b2.blat2,5)</p>
<p>Sorry, I'm late. I just know this blog today. After 10 minutes trying to figure out what really happened inside the query, I found something:</p>
<p>JOIN (or Inner Join) return matching common values between two tables. So, in the original query, SQL Server has to read a row in the b1 table, and then reads all rows in the b2 table to find matching strings, and then, repeats until end of table b1. LIKE Operator is relatively slow, so It'll take a long time to execute the query (About 1m41s in my computer :P.)</p>
<p>One way to improve performance is replace LIKE operator with operator =. We already knew that blat1 column contains only CHAR(5) string, so that, Join condition can be rewrite like this:</p>
<p>b1.blat1 = LEFT(b2.blat2,5)</p>
<p>Even with LEFT function, this new query outperform the old one, it costs about 2s to run in my computer, but still affects 109984 rows, equals to the old one.</p>
<p>The fact is, when we insert values into blat1 column, it has been trimmed into CHAR(5). In example, the following addresses make sense in blat2 column, but they don't make any in blat1 column.</p>
<p>1, place Beaubernard</p>
<p>1, place de Brazaville</p>
<p>1, place de la R&#233;publique</p>
<p>They'll be inserted into blat1 with the same value as '1, pl',and when executing the query, SQL server has to repeat Join operator many times to same values that are exactly same. So I added DISTINCT keyword to prevent it from wasting time. Now my query returns only 13567 rows and takes only near 0s to run.</p>
<p>Hope this right!</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#15413Wed, 22 Jul 2009 13:57:46 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15413Brian Tkatch<p>Just saw this now. So i'll post:</p>
<p>SELECT *</p>
<p>FROM b1</p>
<p>WHERE b1.blat1 IN (SELECT LEFT(b2.blat2, 5) FROM b2);</p>
<p>It is faster because it can do 1 read on b2. The LIKE doesn't know that b1 is five chars and therefor, it has to re-evaluate the expression in b2 for each record in b1. When IN is used, it generates a sub-table first.</p>
<p>That my guess at least. :)</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#26314Mon, 21 Jun 2010 06:25:07 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:26314Divya<p>Try this</p>
<p>SELECT *</p>
<p>FROM b1</p>
<p>JOIN b2 ON</p>
<p>LEFT(b2.blat2,5) =b1.blat1</p>
re: SQL Server Query Processing Puzzle: LIKE vs ?http://www2.sqlblog.com/blogs/adam_machanic/archive/2008/04/22/sql-server-query-processing-puzzle-like-vs.aspx#35389Mon, 02 May 2011 12:50:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35389Olivier Comte<p>These solutions aren't equivalent to (SELECT *FROM b1JOIN b2 ON &nbsp; &nbsp;b2.blat2 LIKE b1.blat1 + '%') if b1.blat1 contain a &quot;special&quot; character like '%'.</p>
<p>For example, if there is one record in b1 with blat1='%a',and a record in b2 with blat2='a',</p>
<p>SELECT * FROM b1 JOIN b2 ON &nbsp; &nbsp;b2.blat2 LIKE b1.blat1 + '%'</p>
<p>will return this record but</p>
<p>SELECT * FROM b1 JOIN b2 ON LEFT(b2.blat2,5) =b1.blat1</p>
<p>won't.</p>