July 13, 2012 at 10:24 am
I thought I'd investigate the performance differences between two similar queries (written against AdventureWorks) :
select FirstName, LastName from Person.Contact
where LEFT(LastName,2) = 'Mc'
-- cost .877619
-- after index.0931743
select FirstName, LastName from Person.Contact
where LastName Like 'Mc%'
-- cost .875662 - missing index - add a nonclustered index including first name
-- after index: .0033256
I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index including FirstName. The LEFT Plan did not recommend any new indexes, yet after I created it the estimated performance increased dramatically, thought not nearly as dramatically as for the LIKE query.
One lessons to take from this is that that you can't always relay on Estimated Execution Plan to tell you where new indexes should be. How accurate are the costs in general? Other lessons?
July 13, 2012 at 10:38 am
Please see
LEFT() is a function it would result in an index scan where like '% %' would result in index seek which is more efficient.
July 13, 2012 at 3:41 pm
Loner (7/13/2012)
Please seeLEFT() is a function it would result in an index scan where like '% %' would result in index seek which is more efficient.
Not quite.
LIKE 'xxx%' can do a seek.
LIKE '%xxx%' cannot.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 13, 2012 at 3:48 pm
dan-572483
I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index including FirstName. The LEFT Plan did not recommend any new indexes, yet after I created it the estimated performance increased dramatically, thought not nearly as dramatically as for the LIKE query.
One lessons to take from this is that that you can't always relay on Estimated Execution Plan to tell you where new indexes should be. How accurate are the costs in general? Other lessons?
You should avoid using functions on table columns if at all possible, because function use prevents SQL from using any indexes to satisfy the query. That is why with the LEFT() SQL didn't recommend any index -- that query can't use any index anyway.
Thus, LIKE 'xxx%' is always preferable to LEFT(col, nn).
Avoiding functions requires particular care for datetimes. One often sees:
WHERE CONVERT(varchar(10), datetime_column, 101) = '07-13-2012'
But that's the WRONG way to compare a date column, so DON'T do that.
DO THIS instead:
WHERE datetime_column >= '07-13-2012' AND
datetime_column < '07-14-2012'
You should never rely on the Estimated Execution Plan or even the Actual Execution plan to control index creation. A knowledgeable person should always review the recommendations.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2014 at 11:09 pm
0 down vote
"Left" vs "Like" -- one should always use "Like" when possible where indexes are implemented because "Like" is not a function and therefore can utilize any indexes you may have on the data.
"Left", on the other hand, is function, and therefore cannot make use of indexes. This web page describes the usage differences with some examples. What this means is SQL server has to evaluate the function for every record that's returned.
"Substring" and other similar functions are also culprits.
October 15, 2015 at 3:17 am
_watching (7/6/2014)
0 down vote"Left" vs "Like" -- one should always use "Like" when possible where indexes are implemented because "Like" is not a function and therefore can utilize any indexes you may have on the data.
"Left", on the other hand, is function, and therefore cannot make use of indexes. This web page describes the usage differences with some examples. What this means is SQL server has to evaluate the function for every record that's returned.
"Substring" and other similar functions are also culprits.
ChrisM@Work (10/7/2015)
It's quite a comprehensive test. A note about SARGable LIKE queries within the article would improve it - or a link to the same statement elsewhere in the blog. If you're Dave Lozinski, then your blog post suggestion that temp tables always get written to disk (and table variables don't) requires an update[/url].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply