Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathans
Aged Yak Warrior
938 Posts |
 Posted - 2005-08-04 : 12:09:31
|
In my test case it looks like in the first query SQL performs a table scan (applying the LEFT call) then filters on the where for like values ion that subset.In the second query it performs the table scan without an additional filter, scanning for the like values initially in the existing subset.Is this because of the application of the LEFT function? It must apply the function to each evaluated row?Do you guys see a performance difference in the following two queries:declare @term char(6)set @term = '5D2766'select *FROM #test_1WHERE @term LIKE LEFT(test_guid, 6) + '%' select *FROM #test_1WHERE test_guid LIKE @term + '%' Nathan Skerl |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-04 : 12:15:32
|
| the 2nd example is the more standard approach. You don't need to use the LEFT function, so it is potentially a little more efficient.put an index (not a clustered index though) on test_guid and it should perform about as good as you can get. -ec |
 |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-04 : 16:11:24
|
| In the first scenario mssql cannot use index to find resultset. In the second it can. So the first one is less efficient not only because it must evaluate function but also because execution plan is build without using index |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-08-04 : 17:06:40
|
| Thanks for the input guys.Nathan Skerl |
|
|
|
|
|
|
Performance difference in "LIKE" syntax ?