Will index be used when using OR clause in where

Question

I wrote a stored procedure with optional parameters.

 CREATE PROCEDURE dbo.GetActiveEmployee
   @startTime DATETIME=NULL,
   @endTime   DATETIME=NULL
 AS
   SET NOCOUNT ON

   SELECT columns
   FROM table
   WHERE (@startTime is NULL or table.StartTime >= @startTime) AND
         (@endTIme is NULL or table.EndTime <= @endTime)

I'm wondering whether indexes on StartTime and EndTime will be used?

Answer 1

Yes they will be used (well probably, check the execution plan - but I do know that the optional-ness of your parameters shouldn't make any difference)

If you are having performance problems with your query then it might be a result of parameter sniffing. Try the following variation of your stored procedure and see if it makes any difference:

CREATE PROCEDURE dbo.GetActiveEmployee
    @startTime DATETIME=NULL,
    @endTime   DATETIME=NULL
AS
    SET NOCOUNT ON

    DECLARE @startTimeCopy DATETIME
    DECLARE @endTimeCopy DATETIME
    set @startTimeCopy = @startTime
    set @endTimeCopy = @endTime

    SELECT columns
    FROM table
    WHERE (@startTimeCopy is NULL or table.StartTime >= @startTimeCopy) AND
         (@endTimeCopy is NULL or table.EndTime <= @endTimeCopy)

This disables parameter sniffing (SQL server using the actual values passed to the SP to optimise it) - In the past I've fixed some weird performance issues doing this - I still can't satisfactorily explain why however.

Another thing that you might want to try is splitting your query into several different statements depending on the NULL-ness of your parameters:

IF @startTime is NULL
BEGIN
    IF @endTime IS NULL
        SELECT columns FROM table
    ELSE
        SELECT columns FROM table WHERE table.EndTime <= @endTime
END    
ELSE
    IF @endTime IS NULL
        SELECT columns FROM table WHERE table.StartTime >= @startTime
    ELSE
        SELECT columns FROM table WHERE table.StartTime >= @startTime AND table.EndTime <= @endTime
BEGIN

This is messy, but might be worth a try if you are having problems - the reason it helps is because SQL server can only have a single execution plan per sql statement, however your statement can potentially return vastly different result sets.

For example, if you pass in NULL and NULL you will return the entire table and the most optimal execution plan, however if you pass in a small range of dates it is more likely that a row lookup will be the most optimal execution plan.

With this query as a single statement SQL server is forced to choose between these two options, and so the query plan is likely to be sub-optimal in certain situations. By splitting the query into several statements however SQL server can have a different execution plan in each case.

(You could also use the exec function / dynamic SQL to achieve the same thing if you preferred)

Answer 2

Yes, based on the query provided indexes on or including the StartTime and EndTime columns can be used.

However, the [variable] IS NULL OR... makes the query not sargable. If you don't want to use an IF statement (because CASE is an expression, and can not be used for control of flow decision logic), dynamic SQL is the next alternative for performant SQL.

IF @startTime IS NOT NULL AND @endTime IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE 
   WHERE starttime >= @startTime
       AND endtime <= @endTime

END
ELSE IF @startTime IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE 
   WHERE endtime <= @endTime

END    
ELSE IF @endTIme IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE
   WHERE starttime >= @startTime

END
ELSE
BEGIN

  SELECT columns
     FROM TABLE

END

Answer 3

I don't think you can guarantee that the index will be used. It will depend a lot on the size of the table, the columns you are showing, the structure of the index and other factors.

Your best bet is to use SQL Server Management Studio (SSMS) and run the query, and include the "Actual Execution Plan". Then you can study that and see exactly which index or indices were used.

You'll often be surprised by what you find.

This is especially true if there in an OR or IN in the query.

Answer 4

There is a great article to do with dynamic search criteria in SQL. The method I personally use from the article is the X=@X or @X IS NULL style with the OPTION (RECOMPILE) added at the end. If you read the article it will explain why

http://www.sommarskog.se/dyn-search-2008.html

Answer 5

Probably not. Take a look at this blog posting from Tony Rogerson SQL Server MVP:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx

You should at least get the idea that you need to test with credible data and examine the execution plans.

Answer 6

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

The portion of the above articles that apply to this query is Umachandar's Bag of Tricks, but it is basically defaulting the parameters to some value to eliminate needing to use the OR. This will give the best index usage and overall performance:

CREATE PROCEDURE dbo.GetActiveEmployee
    @startTime DATETIME=NULL,
    @endTime   DATETIME=NULL
AS
    SET NOCOUNT ON

    DECLARE @startTimeCopy DATETIME
    DECLARE @endTimeCopy DATETIME
    set @startTimeCopy = COALESCE(@startTime,'01/01/1753')
    set @endTimeCopy = COALESCE(@endTime,'12/31/9999')

    SELECT columns
    FROM table
    WHERE table.StartTime >= @startTimeCopy AND table.EndTime <= @endTimeCopy)