December 5, 2008 at 1:18 pm
The following query takes 30 seconds to run:
exec sp_executesql N'SELECT [FileName] FROM MyView WITH (NOLOCK) WHERE docid = @did AND foldernumber =
@fn AND packagetype = @pt AND drawer=@drawer',N'@did int,@fn nvarchar(8),@pt int,@drawer nvarchar(2)'
,@did=17,@fn=N'AGL80087',@pt=10300,@drawer=N'ES'
This one takes 1 millisecond:
exec sp_executesql N'SELECT [FileName] FROM MyView WITH (NOLOCK) WHERE docid = @did AND foldernumber =
@fn AND packagetype = @pt AND drawer=@drawer',N'@did int,@fn varchar(8),@pt int,@drawer varchar(2)'
,@did=17,@fn='AGL80087',@pt=10300,@drawer='ES'
What exactly is going on here? It's the same exact query outside of the nvarchar/varchar switch!
December 5, 2008 at 1:33 pm
I'm going to guess that the columns are varchar and there's an index on at least one of them. If you compare a varchar column to an nvarchar parameter, SQL first has to cast the varchar column to nvarchar and then do the comparison. The conversion on the column means that index seeks are not possible. Any function on a column, including cast is not sargable and will result in an index scan (at best) rather than an index seek.
If you look at the exec plan of the two, you should see the difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2008 at 1:41 pm
That makes sense.
Thanks for the quick reply!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply