Query uses Index Scan instead of Seek when OR @param = 1 is added to WHERE clause
I am going through stored procedures to make them available and I noticed something unexpected about how the index was being used.
There's a non-clustered index on the DateColumn and a clustered index on the table (no direct reference in the query).
Whereas the following uses an index lookup for a non-clustered index that has a DateColumn as the index column:
DECLARE @timestamp as datetime
SET @timestamp = '2014-01-01'
SELECT column1, column2 FROM Table WHERE DateColumn > @timestamp
However, the following uses an index scan:
DECLARE @timestamp as datetime
DECLARE @flag as bit
SET @timestamp = '2014-01-01'
SET @flag = 0
SELECT column1, column2 FROM Table WHERE (DateColumn > @timestamp) OR (@flag = 1)
I put parentheses just in case, but of course it didn't make any difference.
Since @flag = 1
it has nothing to do with the table, I expected a search in both cases. Out of interest, if I change it to 0 = 1
, it uses index lookup again. The @flag value is a parameter to a procedure that tells the request to return all records, so I can't really hard-code the code.
Can I use search instead of scanning? The only option I can think of is the following, however, the queries are actually much more complex, so this kind of duplication hurts readability and maintainability:
DECLARE @timestamp as datetime
DECLARE @flag as bit
SET @timestamp = '2014-01-01'
SET @flag = 0
IF @flag = 1
BEGIN
SELECT column1, column2 FROM Table
END
ELSE
BEGIN
SELECT column1, column2 FROM Table WHERE DateColumn > @timestamp
END
source to share
Your dynamic solution is actually better because you won't get caught when you first pass @flag = 1 and that's what you get for all subsequent calls. As @RaduGheorghiu says, scanning is better than searching in these cases.
If it were me, I would have 2 treatments, one for "get everything" and one for "get on a date". Two procedures, two uses, two query plans. If repetition bothers you, you can enter a performance.
source to share
Just for completeness, I'm going to post an option I just realized works in my specific situation. This is probably what I'm going to use due to its simplicity, however this probably won't work 99.9% of the time, so I don't think this is a better answer than dynamic SQL.
declare @flag as int
declare @date as datetime
set @flag = 1
set @date = '2015-08-11 09:12:08.553'
set @date = (select case @flag when 1 then '1753-01-01' else @date end)
select Column1, Column2
from Table_1
where DateColumn > @date
This works because the DateColumn stores the modified date for the record (I am returning deltas). It defaults to getdate () and is set to getdate () for updates. This means that in this particular case, I know that @date = '1753-01-01'
all records will be returned for the value .
source to share