Geek Answers Handbook

Why does my SQL statement take N times more time to run when I set the value as a variable?

The first thing I would like to say is what I'm not trying to achieve. I skipped this LOT request to get my question clearer.

I have a non-clustered index in a table (CallDetail) for two values: TermDate (int) and SourceSystemID (int). To be complete, I will include the exact definition of the index here:

CREATE NONCLUSTERED INDEX [CallDetail_TermDateSourceSystemID] ON [dbo].[CallDetail] 
(
    [TermDate] ASC,
    [SourceSystemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The problem that I encountered is that when I run two almost identical queries on this table, I do not get the same results (not to be confused with the result set). The first query is completed in less than one second and returns about 10,000 rows. The second request, when executed, continues to work until I canceled it after about 30 minutes.

Request 1 (~ 1 second):

SELECT
    *
FROM
    CallDetail
WHERE
    CallDetail.TermDate >= 1101221 AND
    SourceSystemID = 1

Request 2 (> 30 minutes):

DECLARE @TermDate AS INT
SET @TermDate = 1101221

SELECT
    *
FROM
    CallDetail
WHERE
    CallDetail.TermDate >= @TermDate AND
    SourceSystemID = 1

Something I would like to point out is that in terms of query execution, I need to “include” all the columns of this table in the index. I think this is completely wrong. I would also like to note that if I select only TermDate and SourceSystemID instead of *, I get the results after about 1 second.

Is there a reason that when using a variable instead of hard coding, the value is where it takes much longer? I am completely shy about this and any help would be greatly appreciated.

Thank!

Christopher Hawes

+5
source share
3 answers

OK, I reproduced the situation with my request:

declare @a as int
set @a = 12972100
select * from MyTable where (MyColumn > @a)

marc_s :

declare @a as int
set @a = 12972100
select * from MyTable where (MyColumn > @a) option (recompile)

!

+4

, sniffing .

? ? ?

"" DBCC FREEPROCCACHE , ...

+2

, . , , , , . , ?

, , , - , ?

, , Sniffing, .

, int ( TermDate - .)

, .

, , .

 DECLARE @TermDate AS INT

, , .

+1

More articles:


All Articles