SQL Query Performance. IF vs WHERE

Question

hi all,

  I have a query to select data from a table with status as condition. This is a simple query but in the condition i have a constraint. The status is field is integer value so '0' means inactive and '1' means active. I have the parameter status to select the active or inactive data. Also i need to take all data irrespective of status. For this i pass 2 as parameter value. Here i can write the query different ways.

1. Using IF condition

   

Declare @Status INT = 0
IF @Status = 0 OR @Status = 1
BEGIN
 SELECT * FROM Table1 Where [Status] = @Status
END
ELSE
BEGIN
 SELECT * FROM Table2
END

2 Using OR clause in WHERE

Declare @Status INT = 0
SELECT * FROM Table1 Where ISNULL((SELECT CASE @Status WHEN 2 THEN NULL ELSE @Status END), 0) = 0 OR [Status] = @Status

Can anyone tell me which one is better? My friend told that instead of IF condition best is use OR in where by using ISNULL . But here i have CASE statement also to check null so which one is fast and best method

Answer 1

I believe that the first method is better (two separate queries).

Take a look at these blogs:

Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
Catch All Queries - short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

Option recompile

Option recompile discussion thread

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I agree with Naomi that the first technique should perform better.  The issue is that these are two fundementally different queries so the optimizer can generate the most optimal plan with 2 different queries instead of a single query with the functionality of both.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

The point is, by 2nd way, you cannot choose from table2. So 1st way should be better.

---

Many Thanks & Best Regards, Hua Min

Answer 4

Thanks friend...Now some what i am confused . Because in my first version of the product i used dynamic SQL. I read some articles  and coding standard about this like dynamic sql is decrease performance and never used. So i searched other option and got like ISNULL from my friend.

hi Nami, I read the articles and i got from that is dynamic SQL is better. so is this corret way .. so dynamic sql is recommended approach?

Answer 5

Read this

http://ask.sqlservercentral.com/questions/1219/is-dynamic-tsql-a-good-or-bad-idea.html

---

Many Thanks & Best Regards, Hua Min

Answer 6

thanks friend

Answer 7

I got one more option is using OPTION (recompile).  anyone used this?

Answer 8

OPTION (RECOMPILE) is to force SQL Server to get a new plan

Read more: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Using-the-OPTION-(RECOMPILE)-option-for-a-statement.aspx#ixzz26EVClY00

---

Many Thanks & Best Regards, Hua Min

Answer 9

Anyway dynamic sql is good way. Am i right? But using  OPTION(RECOMPILE) i didn't get the clear picture. I read the articles and got the idea. but is it recommend to use for performance? Also this is work in 2008 SP 1 . I read in one article like that

Answer 10

Dynamic SQL is a good solution and OPTION (RECOMPILE) should also work, but it's for latest version of SQL Server.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Thanks Naomi. My final confusion is so in the dynamic sql also include OPTION(RECOMPILE) so it is increase performance am i right?

Answer 12

Anyway dynamic sql is good way. Am i right? But using  OPTION(RECOMPILE) i didn't get the clear picture. I read the articles and got the idea. but is it recommend to use for performance? Also this is work in 2008 SP 1 . I read in one article like that

In this particular case (only 2 statements), I suggest the conditional static SQL approach.  But in a more complicated scenario, such as many optional parameters, dynamic SQL is a better choice since it's impractical to code a separate query for each permutation. 

With either approach, consider the OPTION(RECOMPILE) hint if the optimal plan varies significantly depending on the actual values specified.  For example, let's assume a non-clustered index on Status with 10 rows with value 0 and 10 million rows with value 1.  The optimal plan for value 0 would be a non-clustered index seek and the best plan for value 1 would be a scan of all rows.  If the statement is initially executed with value 0, the seek plan will be cached.  That plan will also be used for value 1, resulting in very bad performance of subsequent value 1 queries.  Similarly, a cached scan plan will be bad for value 0. 

The OPTION(RECOMPILE) hint addresses this issue by generating a new plan each time.  The only downside is the additional costs of compilation (CPU and locking) when a query is executed very frequently (e.g. many times per second).

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 13

Thanks Dan, But i think is costly when SP access frequently. I mean if  i use the same SP in my Web page and also in windows Service to select periodically this option is not advisable am i write?

Now i rewrite the old query like below

CREATE PROCEDURE SampleSP       
  @Status INT,  
  @Debug INT = 0     
AS        
BEGIN        
 SET NOCOUNT ON;        
     
 DECLARE @sSQL NVARCHAR(MAX), @where NVARCHAR(1000) = ''       
 SET @sSQL =     
 'SELECT     
  Name,    
  [Description],  
  [Status]    
 FROM Table1 '    
      
  IF @Status = 0 OR @Status = 1    
   SET @where = @where + 'AND [Status] = @_Status '     
    
 IF LEN(@Where) > 0    
  SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)    
     
 IF @Debug = 1    
  PRINT @sSQL    
 ELSE    
  EXEC sp_executesql @sSQL,    
   N'@_Status INT',    
   @_Status = @Status  
END 

   

Answer 14

Thanks Dan, But i think is costly when SP access frequently. I mean if  i use the same SP in my Web page and also in windows Service to select periodically this option is not advisable am i write?

As I mentioned earler, I don't think dynamic SQL is needed here but it doesn't do any harm either, except you need to either grant direct permissions on the underlying tables or sign the procedure as described in Erland's articles.

Regarding OPTION(RECOMPILE), how many times per second is the query executed?  Should the optimal execution plans vary depending on the @Status value specified?

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 15

Thanks Dan. I got what you told..

Regarding OPTION(RECOMPILE), how many times per second is the query executed?  Should the optimal execution plans vary depending on the @Status value specified?

 This may depends on how many users hit at the time.  Any way times per second we can say as 2 to 3 per second .. I didn't get the second question as i am not an expert in SQL

Answer 16

This may depends on how many users hit at the time.  Any way times per second we can say as 2 to 3 per second .. I didn't get the second question as i am not an expert in SQL

Do you have an index on Status?  How many rows have value 0?  How many with value 1?

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 17

This is status field and i didn't create index on this. Maximum records are in '1' status as this means active. '0' means inactive

Answer 18

This is status field and i didn't create index on this. Maximum records are in '1' status as this means active. '0' means inactive

If you have no index on Status, SQL Server must scan every row in the table even though only the rows with the specified status are returned.

What are the results of the query below?  I suspect an index on Status (with the other columns included) will improve performance of these queries.

SELECT Status, COUNT(*) AS StatusCount
FROM dbo.Table1
GROUP BY Status;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 19

ya that is fine. i will create index on this field. My discussion is a general best practice for better performance.

Answer 20

ya that is fine. i will create index on this field. My discussion is a general best practice for better performance.

When in comes to performance, useful indexes are always #1.  Then comes query tuning to allow the best use of those indexes.  The general best practice in a stored procedure is to use static SQL with parameterized statements.  But there are additional considerations you should need to be aware of for special situations.

Add OPTION(RECOMPILE) to statements where the best plan for the same query will vary depending on the actual values specified.  See my eariler example (10 rows with Status 0 and 10 million rows with Stus 1).  In that case, OPTION(RECOMPILE) will better allow SQL Server to use an index seek on Status for a when value 0 is specified and a scan when value 1 is specified.  On the other hand, if both Status values return the same number of rows, OPTION(RECOMILE) is not needed because the same plan will be optimal for both values.  That is why I asked you about the Status counts.

Dynamic SQL is appropriate when the number of permutations of different WHERE clauses make static SQL unwieldly.  For example, if you have 5 optional parameters, over 32 different static SQL statements would be needed.  Dynamic SQL would be a better approach in that situation.

Like many things in SQL Server, "it depends". 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 21

That is great helpful reply. Because the scenario is like you told i have 100 of records (take an example) and the status '0'  means the record is not useful or deleted . So in that 100 of records the status '0' is very few like 5- 15 or like that . So like you told OPTION(RECOMPILE) will help me better.

The confusion is in the page there is filter Combo for selecting inactive and active. So people also check the inactive records. so both parameter is passing alternatively.So OPTION(RECOMPILE) is based on number of records or query fetching frequency?

Also in the dynamic sql when there is no parameter like getting all records , OPTION(RECOMPILE) is useful or not needed?