Sargeable and/or Optimization Question 3

[monksnake]

I have a question for the masses.

I won't be able to respond until tomorrow, so that will be why you don't see a response.

I'm pretty sure using the LIKE clause and LEFT statement on chars and varchars are not sargeable. If one of them is, please tell me (I know LIKE isn't).

What would be a faster way to reference a string??

If I have a small string 'rat' and I want to return any values in a char field that have the first 3 letters = 'rat', which is faster??

Select col from table1 where col like 'rat%'

OR

Select col from table1 where left(col, 3) = 'rat'

I'm thinking the 2nd query, but I have no fact to base this upon. Thanks for your input.







<.

 

[gmmastros]

Assuming you have an index on the 'col' field, the first one is sargable, and the second on isn't.

The ONLY reason it's sargable is because of the search pattern: 'rat%' Because you are searching on what the data starts with. If you had '%rat%', then it wouldn't be sargable.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here's a test:

Create Table TestSarg(ID int, Data VarChar(100))
Create index idx_Data On TestSarg(Data)

Insert Into TestSarg Values(1, 'fat')
Insert Into TestSarg Values(1, 'cat')
Insert Into TestSarg Values(1, 'rat')
Insert Into TestSarg Values(1, 'bat')
Insert Into TestSarg Values(1, 'sat')
Insert Into TestSarg Values(1, 'mat')

Now, open another query window and press CTRL-K before running the following.

Select * From TestSarg Where Data Like 'rat%'

Select * From TestSarg Where Left(Data, 3) = 'rat'

Both generate the same output, but the 'Like' query performs an index seek while the 'left' version does a table scan. If you run them together, you'll see that the 'Like' query cost is approximately 25% and the 'left' query cost is approximately 75%.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Like with an ending % only is sargable
any function (left, right, convert etc etc etc) on the left side of the operator is not sargable

instead of whete left(col,1) ='a'

do

where col like 'a%'

some more examples here:

http://sqlservercode.blogspot.com/2005/12/sql-query-optimizations.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ESquared]

Just to make things interesting, an index on a computed column may make certain operations sargable that weren't otherwise.

For example, if we altered the above table

alter table TestSarg add FirstLetter As (Left(Data, 1))
Create index idx_FirstLetter On TestSarg(FirstLetter)

then

select * from TestSarg where FirstLetter = 'r'

might work. But I haven't tested this... time for bed.

Of course, then you add the overhead of a calculation to each update of the Data column. But now a complex calculation might be sargable.

Now that I think about this, I'm pretty sure it works. I've seen a technique for selecting extremely long text values that don't make good index fodder. Add a calculated column with the checksum of the long column, plus index, and in the where clause specify both the checksum and the big value. The checksum has pretty high selectivity, being no worse than about 1/2³² chance of collision with another checksum (assuming SQL Server returns 32-bit checksums?), and then the other part of the where clause ensures returning the correct result. Not exactly the same thing as above, but sort of related...

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[SQLDenis]

true, if you needed to check for last character(s) you would create a computed column with the reverse function and you would create an index on that

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[monksnake]

Cool thank you all for your help, I have learned something.

<.

 

[monksnake]

Maybe I should actually run those queries and look at the execution plan before I post

<.