CODEWITHSUNDEEP
sql-servert-sql
impact
impact

Reputation: 13

Optimization of SQL query with multiple if statements

I am trying to select different values of a table based on a parameter passed in.

I have code that works correctly, it is shown below.

declare @type as int
set @type = 3

if @type = 1
    select * from t
else if @type = 2
    select * from t WHERE theType = 2 or theType = 3
else if @type = 3
    select * from t where theType = 3

This works very well, however i want to get rid of the if statements if possible.

Is there any way to make this code have the same result, but run better?

Thanks!

Upvotes: 0

Views: 868

Answers (2)

Thom A
Thom A

Reputation: 95830

A different approach might be Dynamic SQL. If the example we have is overly simplified then using OR logic (like in Tim's answers) could end up with a cached query plan that isn't good for the query being run. This does appear to be true as @type = 1 returns all rows, and when it has a value of 3 it is filter to a single value. This means that the estimates for 1 and 3 will be very different.

This gives you something like this:

DECLARE @type int;
SET @type = 3;

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT * FROM t' +
           CASE @type WHEN 1 THEN N';'
                      WHEN 2 THEN N' WHERE theType = 2 or theType = 3;'
                      WHEN 3 THEN N' WHERE theType = 3'
           END;
EXEC sp_executesql @SQL;

This is not to say that Tim's answer is wrong, but that you could end up with poor performance for certain values.`

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You could try rewriting this to be a single query:

select *
from t
where
    @type = 1 or
    (@type = 2 and theType in (2, 3)) or
    (@type = 3 and theType = 3);

I don't know if this would actually run faster than what you currently have, but it does get rid of the if statements.

Upvotes: 2

Related Questions