sql server 2008 stored proc optimization best practice of multiple or conditions

Question pertains to performance and best practice. (sql server 2008 stored procedure)

If a stored proc contains code like this

result 开发者_如何学C= select count(*) from tableA where (some condition1)

if (result) -- meaning we got something from above query
   return result
else
   result = select count(*) from tableA where (some other condition2)
and so on until
result = select count(*) from tableA where (some other conditionN)

Is it better to put all the conditions in one big query like this

   resultn = select count(*) from tableA 
   where (condition1 or condition1 or... conditionN)

Will a table scan be done for every condition or is the table scan done once no matter how many conditions. What is an elegant way to handle this type of logic. thanks

Your two code blocks are not logically equivalent. I'm going to assume that IF(result) really means, IF(@result > 0). In that case the first non-zero count that you find will be returned. In your second code block you will actually get back the total of all of the counts for all of your criteria.

In your second code block SQL Server can only pick one query plan for the query. If you have indexes on all of your columns it can still only use one of them. I don't think that it will do parallel processing using an index for each query condition. I haven't specifically tested this though, so maybe someone can correct me if I'm wrong. You might be able to alleviate some of the problem with this by using the WITH RECOMPILE directive in your stored procedure. This had some bugs in SQL 2005, but since you're on SQL 2008 you should be OK.

In your first code block SQL Server can have separate query plans for each query.

In any case, which one is actually best is going to depend on a lot of factors. Your best bet is going to be to test them side by side with test data that pretty closely mimics your actual production data.

Also, don't forget about maintainability and readability of code. If this is code that isn't run often or if the performance difference is small enough then you might be better off using the code that is most easily maintained and understood, especially if you have a lot of conditions or might be changing them often.

Finally, do the conditions rely on parameters of the stored procedure? Will some of these parameters often be left out implying that the condition is not relevant? If so, then you should very carefully read this article on dynamic search conditions by Erland Sommarskog. At one client I found dynamic SQL to be the most performant method by far for this situation, but of course a lot of care had to be taken to make this code airtight when it came to security.

It is better to put different conditions in IN clause, because it will make its plan only once and then check for multiple conditions, but if you will execute seperaly it will have to perform plan each time.

It is impossible to say if a full scan will occur, it depends on the cardinality of your data, and if you have a suitable indexes.

Assuming each select will full scan...

and your WHERE clauses are different you will have to incorporate this logic in the select using CASE WHEN

see: http://msdn.microsoft.com/en-us/library/aa258235(v=sql.80).aspx

then you will need a conditional block to return the correct value.

The benefit is this will only full scan once.