April 12, 2005 at 12:54 pm
I've got a table, tbMAIN which contains a VARCHAR(32) field 'Ticker' as well as an INT field InstTypeEnumID. The int field references the primary key of a lookup table, tbLKP. With me so far? Now it gets interesting.
The InstTypeEnumID is related (although not formally) to the first 2 characters of the 'Ticker' field. (EG: when left(Ticker, 2) = 'st', then InstTypeEnumID should always be 14.)
Given that, there is currently a lot of stored code that uses the following where clause:
where Ticker like 'cy%'
For cleanliness and consistency, I thought I'd create a function that could compare the values of InstTypeEnumID to the Lookup table's in order to alleviate the "like" comparison. Here's the function I came up with:
create function fnEnumS (@EnumName dtString)
returns int
as
begin
declare @EnumID int
select@EnumID = E.EnumID
fromtbEnum E
whereE.EnumName = @EnumName
return (@EnumID)
end
So, I was ready to replace all occurrences of
where Ticker like 'cy%'
with
where InstTypeEnumID = dbo.fnEnumS('cy')
Then I ran the following queries just for grins...
declare @starttime datetime
declare @endtime datetime
set @starttime = getdate()
select count(*) from tbMAIN
where Ticker not like 'cy%'
set @endtime = getdate()
select @endtime - @starttime
-- average time: 00:00:00.000 (IOW < .0015, right?)
declare @starttime datetime
declare @endtime datetime
set @starttime = getdate()
select count(*) from tbMAIN
where InstTypeEnumID <> dbo.fnEnumS('cy')
set @endtime = getdate()
select @endtime - @starttime
-- average time: 00:00:00.543
Now, the tbMAIN.Ticker field DOES have a unique index on it, and the InstTypeEnumID column in tbMAIN IS NOT indexed, but I figured the integer compare against a PK would be much faster. The opposite is true! Why is this the case?!?
April 12, 2005 at 1:01 pm
Your function and <> is converting the expression in not "SARG"able the not Like 'cy%' can be converted into like '[^cy]%' which is SARGable therefore your speed!
* Noel
April 12, 2005 at 1:45 pm
by that rationale, if I switched to 'like' and '=' instead of 'not like' and '', I should see the performance difference fade, right? I'm not seeing that in practice. is there a good article on this type of issue? SARGability and what not? tx... greg
April 12, 2005 at 1:55 pm
There are two parts to the problem.
If you look at the query plan on SQL Server you will be surprised that sometimes (when it is possible) your where expressions are changed for you to be able to exploit indexes (that's the SARG part) BTW: Inside SQL 2000 has an indepth description on this.
The other is that you are incorporationg a function that was not present in the previous query that even though is evaluated only once it adds up and prevents conversion!
hth
* Noel
April 12, 2005 at 2:06 pm
thanks noeld. appreciated.
http://www.sql-server-performance.com/sql_server_performance_audit8.asp
had some good words as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply