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