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

Question

The first thing i would like to say is that this is not exactly what I am trying to achieve. I have dumbed down this query A LOT to get my question across more clearly.

I have a nonclustered index on a table (CallDetail) on two values, TermDate (int) and SourceSystemID (int). To be complete, I will include the precise 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 am having is that when I run two nearly identical queries against this table, I don't get the same results (not to be confused with result set). The first query runs in less than one second and returns about 10,000 rows. The second query, when executed, continues to run until I cancel it after about 30 minutes.

Query 1 (~1 second):

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

Query 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 note is that the query execution plan tells me to 'include' all the columns of this table in the index. I find that to be completely wrong. I would also like to note that if i only select TermDate and SourceSystemID instead of * that I get results in about 1 seconds.

Is there a reason that when using a variable instead of hard coding a value into the where that it is taking so much longer? I am completely stumped on this and any help would be very much appreciated.

Thanks!

Christopher Haws

Answer 1

OK, I reproduced the situation with my query:

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

After reading up on the post by marc_s, I did this:

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

And everything was fast again!

Answer 2

You're most likely suffering from parameter sniffing and here.

Can you compare the query plans for both your queries? The one with the fixed value vs. the one with the parameter? Do they give you the same actual execution plan?

Just remember to do a proper "cleanup" and issue DBCC FREEPROCCACHE between runs so that the two don't influence one another...

Answer 3

UPDATE

Given the comments, I'm stumped. You say you have dumbed down the details to explain the problem, it could be that some of those details are causing the problem. Are you seeing the same issues with the exact examples you show above?

I've seen more complicated queries that contain cast or convert have similar issues -- is this one of the things you dumbed down?

Or maybe it is Parameter Sniffing as Marc suggests.

---

Original Answer

This is because it has to cast each value in the columns table to type int (the TermDate column must be something else.)

In the first query the optimizer is smart and knows it can cast the constant to a the type of the column and does so.

Also, it can't use the index when it has to cast for every row.

Change this line

 DECLARE @TermDate AS INT

To the same type as the column and you will be happy.