I have two nearly identical queries with VERY different execution plans and execution times. This is on SQL 2000.
--QUERY 1:
select A
from tb_1
where B = 'SomeUnicodeString'
and C = {guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'}
--QUERY 2:
declare @.B nvarchar(50), @.C UniqueIdentifier
select @.B = 'SomeUnicodeString', @.C = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
Select A
from tb_1
where B = @.B
and C = @.C
Indexes on tb_1
are ndx1 non-unique non-clustered (A,B)
and ndx2 non-unique non-clustered (B,C)
First query takes about 6 seconds. Second query runs in under 1/2 second.
Thoughts anyone?
In the first you might need to make SomeUnicodeString into an Unicode string by changing it to:
B = N'SomeUnicodeString'
This could be the difference, but you really need to look at the plan to know. In query analyzer, look in the query menu and run the queries with the include actual plans option. It should be showing in the plan.
It could be a cached plan issue. Consider flushing the procedure cache with DBCC FREEPROCCACHE and see if that helps too.
|||Those two queries will be treated different by the query optimizer. SQL Server will try to parameterize the first one, if is not possible, then it will use the constants as they appear in the statement. The query optimizer probably will use the statistics from the histogram in the second index, to estimate the rows matching the filter. For the second one, it will probably use the density of the columns (B, C) from the second index, to estimate the rows matching the filter, and not the histogram, because the processor will not use the variables to analize the statistics.
Can you post both execution plans and the "All density" block from the result of "dbcc show_statistics (tb_1, ndx2)"?
If you want to parameterize the statement, use sp_executesql.
declare @.sql nvarchar(4000)
declare @.B nvarchar(50), @.C UniqueIdentifier
set @.B = N'SomeUnicodeString'
set @.C = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
set @.sql = N'Select A from tb_1 where B = @.B and C = @.C'
exec sp_executesql @.sql, N'@.B nvarchar(50), @.C UniqueIdentifier', @.B, @.C
go
AMB
