CODEWITHSUNDEEP
sql-servert-sql
James Patterson
James Patterson

Reputation: 31

Where clause optimization

I am trying to optimize a where clause. It gets ran repeatedly, and I am trying to get the query to finish faster but the or stament is slowing it down significantly. If you guys can help that will be great!

WHERE 
((Date1 >= @startdate
    AND Date1 <= @enddate
    AND @RunBy = 0) 
OR (date2 BETWEEN @startdate AND @enddate
    AND @RunBy = 1
    AND date2 BETWEEN @startdate AND @enddate)
OR (date3 BETWEEN @startdate AND @enddate
    AND @RunBy = 2)
OR (date4 >= @startdate
    AND date4 <= @enddate
    AND @RunBy = 3))

I was trying to put in a case or and if, but it just wasn't working out for me.

Upvotes: 2

Views: 138

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74257

Something like this?

where case @RunBy
      when 1 then Date1
      when 2 then Date2
      when 3 then Date3
      when 4 then Date4
      else        null
      end between @startDate and @endDate

That won't necessarily improve your query plan, as I don't believe the optimizer will be able to use any indices on your Date1-4 columns, since case is a function: indices can't be applied to function expressions.

Upvotes: 0

Amy B
Amy B

Reputation: 110111

If you expect different execution plans, you must use different query text. If there are indexes on date1, date2, date3 and date4, they will not be used with you current "multifaceted" query. Break into 4 queries based on the execution plan you expect to use.

IF @RunBy = 0
BEGIN
  SELECT...
  FROM ...
  WHERE date1 ...
END
ELSE IF @RunBy = 1
BEGIN
  SELECT ...
  FROM ...
  WHERE date2 ...
END
ELSE IF...

Upvotes: 1

Bohemian
Bohemian

Reputation: 425013

Your where clause may be rewritten as:

WHERE CASE @RunBy
  WHEN 0 THEN Date1
  WHEN 1 THEN Date2
  WHEN 2 THEN Date3
  WHEN 3 THEN Date4
END BETWEEN @startdate AND @enddate

That should speed things up because you're minimizing the number of comparisons being executed.

It's also much easier to read and understand, which is the essence of good programming, and it looks cool too!

Upvotes: 4

user2275460
user2275460

Reputation: 339

I see there is a duplication of a condition in date2 part.

OR (date2 BETWEEN @startdate AND @enddate
AND @RunBy = 1 AND date2 BETWEEN @startdate AND @enddate)

And you can stick to one of the syntax - either use BETWEEN or >= and <= combination. Dont think it causes a performance difference though.

Upvotes: 0

Michael J Swart
Michael J Swart

Reputation: 3179

Compare the performance of a query where you "change" ORs into UNION ALLs, e.g.: Instead of 

WHERE (A = @A)
OR (B = @B)

Try 

SELECT ...
FROM ...
WHERE A=@A

UNION ALL 

SELECT ...
FROM ...
WHERE B=@B

Worth a shot.

Upvotes: 0

Related Questions