SQL Server Query Processing Puzzle: LIKE vs ?

re: SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Tue, 22 Apr 2008 17:05:56 GMT

To people responding: Don't just send me a solution. Explain to me WHY it works. Why does your change allow the query processor to work more efficiently? 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. Enjoy!

re: SQL Server Query Processing Puzzle: LIKE vs ?

Alexander Kuznetsov — Wed, 23 Apr 2008 14:55:43 GMT

Hi Paul,

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?

re: SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Wed, 23 Apr 2008 15:37:58 GMT

Paul D., next time think before you post.

re: SQL Server Query Processing Puzzle: LIKE vs ?

david wei — Wed, 23 Apr 2008 18:17:05 GMT

Adam, do you think change blat1 to VARCHAR(5) will make this more challenging? People may take the advantage of CHAR(5). :-)

re: SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Wed, 23 Apr 2008 19:05:40 GMT

David:

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 :-)

re: SQL Server Query Processing Puzzle: LIKE vs ?

david wei — Wed, 23 Apr 2008 19:51:47 GMT

Adam, not the length issue, is the trailing space issue.

Trailing space makes the difference.

for example:

if 'abcde' like 'a%'

print 'Y' -- returns Y

if 'abcde' like 'a %'

print 'Yes' -- does not return Yes

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).

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 :-)

INSERT b1

SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1

FROM AdventureWorks.Person.Address

-- Note. you can comment out some sensitive part in my post if you think this will skew the contest.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Wed, 23 Apr 2008 20:07:15 GMT

David,

Length is an issue here as well, due to some of the data. 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). 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.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Denis Gobo — Wed, 23 Apr 2008 20:21:27 GMT

David, that didn't make a difference, I am getting the same number of rows back

SELECT blat1,len(blat1),datalength(blat1),b1.blat1 ,left(b2.blat2,5),

len(blat2),datalength(blat2)

FROM b1

JOIN b2 ON <very secret indeed>

len and datalength reveal that there are some rows with 5 and 4 chars

re: SQL Server Query Processing Puzzle: LIKE vs ?

david wei — Wed, 23 Apr 2008 20:24:32 GMT

Adam, still the trailing space issue, because the minimal length in b2 is 5, not 7.

select min(len((blat2))) from b2

So when you change to 7, blat1 automatic added 2 trailing space to make it CHAR(7); that cause result difference.

I have sent my solution.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Michelle — Wed, 23 Apr 2008 20:36:02 GMT

So Adam,

Do we need to resend you a new solution using CHAR(7), or do we still have a chance to win...? :-)

Thanks,

Michelle.

re: SQL Server Query Processing Puzzle: LIKE vs ?

david wei — Wed, 23 Apr 2008 20:44:40 GMT

Denis, it does make the difference. The len() does not count the trailing space, but they affect the like operator.

Try create the b3 table use trimmed varchar(5) as below:

(CREATE TABLE b3 (blat3 VARCHAR(5) NOT NULL)

INSERT b3

SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1

FROM AdventureWorks.Person.Address

SELECT count(*) FROM b3 inner JOIN b2 ON b2.blat2 LIKE b3.blat3 + '%'

--Trimmed VARCHAR(5) returns 111181 rows

SELECT count(*) FROM b1 inner JOIN b2 ON b2.blat2 LIKE b1.blat1 + '%'

-- original CAHR(5) returns 109984 rows

re: SQL Server Query Processing Puzzle: LIKE vs ?

Denis Gobo — Wed, 23 Apr 2008 20:51:28 GMT

David,

I see I was talking about LEFT not LIKE, with LEFT it doesn't matter if it is char or varchar

re: SQL Server Query Processing Puzzle: LIKE vs ?

Fabiano Neves Amorim — Fri, 25 Apr 2008 09:51:56 GMT

Umm, Denis, I think with the answer you "spilled your beans" about your secret :-(

re: SQL Server Query Processing Puzzle: LIKE vs ?

Gordon — Fri, 02 May 2008 05:23:47 GMT

It's all about iteration.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Igor Mikhalyev — Fri, 02 May 2008 16:35:44 GMT

So where are three best queries? Who are the winners?

re: SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Mon, 05 May 2008 16:14:08 GMT

Igor: Patience, please. I got a -lot- of responses... Trying to figure out who won now.

re: SQL Server Query Processing Puzzle: LIKE vs ?

too embarrassed to say — Fri, 09 May 2008 21:23:13 GMT

Criminy. I didn't even NOTICE the char vs. varchar part. Now my answer could be wrong. Blah.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Igor Mikhalyev — Tue, 03 Jun 2008 21:55:56 GMT

So where are three best queries? Who are the winners?

re: SQL Server Query Processing Puzzle: LIKE vs ?

Magneto — Thu, 05 Jun 2008 11:24:16 GMT

We are waiting .......

re: SQL Server Query Processing Puzzle: LIKE vs ?

Jonathan Kehayias — Fri, 13 Jun 2008 02:23:03 GMT

If you follow this blog, you should understand that Adam has been both busy, and experienced a major setback like the drive failure. 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. 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.

re: SQL Server Query Processing Puzzle: LIKE vs ?

BPhilip — Tue, 17 Jun 2008 15:49:45 GMT

set statistics io on

SELECT *FROM b1 inner JOIN b2 ON left(b2.blat2,5) = b1.blat1

I used this query and got results in 2 seconds. Following are my scan results.

(109984 row(s) affected)

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.

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.

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.

re: SQL Server Query Processing Puzzle: LIKE vs ?

Andrew — Thu, 19 Jun 2008 10:43:18 GMT

It would be very interesting to see the top performing query...

Solution for the "LIKE vs. ?" Puzzle

Adam Machanic — Wed, 01 Oct 2008 19:43:04 GMT

In late April, I posted a puzzle to test readers' knowledge of SQL Server query processing internals

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:37:55 GMT

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

re: SQL Server Query Processing Puzzle: LIKE vs ?

gyanendra — Tue, 03 Feb 2009 18:19:03 GMT

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.

re: SQL Server Query Processing Puzzle: LIKE vs ?

NotNowJohn — Wed, 18 Feb 2009 00:12:55 GMT

Since the competition is already finished I would post here my explanation.

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 => LEFT solves this problem and hash join is efficient in this scenario.

BTW. the estimated plan for the query

SELECT * FROM b1

JOIN b2 ON b2.blat2 > b1.blat1

says that this estimated number of rows for the nested loops would be more than 85M!

T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 18:24:54 GMT

It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response

re: SQL Server Query Processing Puzzle: LIKE vs ?

SqlGuru — Mon, 02 Mar 2009 23:06:43 GMT

Please try this its 3sec

select * from b1

inner join b2

on b1.blat1=substring(b2.blat2,1,5)

re: SQL Server Query Processing Puzzle: LIKE vs ?

SqlGuru — Mon, 02 Mar 2009 23:07:49 GMT

typo mistake sorry its 1sec

re: SQL Server Query Processing Puzzle: LIKE vs ?

Vimvq1987 — Sat, 07 Mar 2009 08:48:16 GMT

SELECT DISTINCT *

FROM b1

JOIN b2 ON

--b2.blat2 LIKE b1.blat1 + '%'

b1.blat1 = LEFT(b2.blat2,5)

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:

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.)

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:

b1.blat1 = LEFT(b2.blat2,5)

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.

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.

1, place Beaubernard

1, place de Brazaville

1, place de la République

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.

Hope this right!

re: SQL Server Query Processing Puzzle: LIKE vs ?

Brian Tkatch — Wed, 22 Jul 2009 13:57:46 GMT

Just saw this now. So i'll post:

SELECT *

FROM b1

WHERE b1.blat1 IN (SELECT LEFT(b2.blat2, 5) FROM b2);

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.

That my guess at least. :)

re: SQL Server Query Processing Puzzle: LIKE vs ?

Divya — Mon, 21 Jun 2010 06:25:07 GMT

Try this

SELECT *

FROM b1

JOIN b2 ON

LEFT(b2.blat2,5) =b1.blat1

re: SQL Server Query Processing Puzzle: LIKE vs ?

Olivier Comte — Mon, 02 May 2011 12:50:41 GMT

These solutions aren't equivalent to (SELECT *FROM b1JOIN b2 ON b2.blat2 LIKE b1.blat1 + '%') if b1.blat1 contain a "special" character like '%'.

For example, if there is one record in b1 with blat1='%a',and a record in b2 with blat2='a',

SELECT * FROM b1 JOIN b2 ON b2.blat2 LIKE b1.blat1 + '%'

will return this record but

SELECT * FROM b1 JOIN b2 ON LEFT(b2.blat2,5) =b1.blat1

won't.