jeudi 8 octobre 2015

Query optimization with variables

First, I apologize if this question is a common one... I can't seem to find the correct terms to search...

I have a somewhat large table that has an incremental ID and an inserted datetime value. There are a couple indexes on it, including a primary key on the ID and an non-clustered index on the inserted time and ID. So, when I write a query like the following, it's very quick:

select min(ID), max(ID)
from tbl
where inserted between '2015-10-07' and '2015-10-08'

However, if I variablize the where clause conditions (as below), it's quite a bit slower:

declare @sTime datetime, @eTime datetime
select @sTime = '2015-10-07', @eTime = '2015-10-08'

select min(ID), max(ID)
from tbl
where inserted between @sTime and @eTime

When I look at the two query plans I see the obvious problem. The first query utilizes a single seek on the non-clustered index of the inserted time and ID. However, the second query instead performs 2 scans on the primary key (which is just the ID).

So, my question is two-fold: 1) Why is sql server performing these optimizations, and 2) how can I fix it?

Aucun commentaire:

Enregistrer un commentaire