Ever wondered why sql will not use the index and still perform very slow. Here are few points where the queries will ignore index.
Consider following stored procedure
CREATE STORED PROCEDURE [UpdateAllItems] @days int = 0 BEGIN declare @date date; set @date = DATEADD(day, @day, GETUTCDATE()); UPDATE Projects SET ... ... WHERE @days = 0 OR DateUpdated > @date END
The above query looks like we want to either update all projects or only the projects which were modified in before given days. So we expect this query to run very long if
@days is 0, but we expect it to run fast when
@days is 1. Unfortunately, query engine will not use index when
Because query engine will use index only if query references column without
OR, Irrespective of which path it will take at runtime. Query analyzer cannot change query based on the input parameters.
So above query should be rewritten as following.
CREATE STORED PROCEDURE [UpdateAllItems] @days int = 0 BEGIN declare @date date; set @date = DATEADD(day, @day, GETUTCDATE()); IF @days = 0 UPDATE Projects SET ... ...; ELSE UPDATE Projects SET ... ...; WHERE DateUpdated > @date END
In above example, server knows that it needs to use index in the ELSE path.
I have seen many people write following query to compute something for given year, for example lets say we want count of all
projects updated in given year and we have index of
DateUdated on the Projects table.
SELECT count(*) from Projects WHERE DATEPART(YEAR, DateUpdated) = 2020;
Above query will cause
INDEX SCAN which is slower than
INDEX SEEK. To speed up above query, we can write following query.
SELECT count(*) from Projects WHERE DateUpdated > '2020-01-01' AND DateUpdated < '2021-01-01';
Above query will do INDEX SEEK and will perform faster than the previous one. If tables are very small, the difference will not be visible, but when we have millions of rows, the SCAN will be much slower than the SEEK.