469,645 Members | 1,559 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Query optimization - where - ISNULL

I have a sequence of 20 stored procedures executed to produce the final output. In almost all procedures, i have a table lookup. The query is something similar to shown below:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tableA 
  3. WHERE
  4. ISNULL(col1, @col1) = @col1 AND
  5. ISNULL(col2, @col2) = @col2 AND
  6. ISNULL(col3, @col3) = @col3 AND
  7. ISNULL(col4, @col4) = @col4 AND
  8. @col5 BETWEEN ISNULL(col5, @col5) AND ISNULL(col5, @col5) AND 
  9. @coldate BETWEEN ISNULL(effDate, @coldate) AND ISNULL(expDate, @coldate)
  10. ORDER BY colrk DESC
  11.  
The idea here is, tables have a default value will null in the column. if the given values does not match, it has to return the null valued record.

Is this query optimized? Does using ISNULL() function affects the performance.

We have non clustered index on the table, including all columns in where clause.
Jan 13 '11 #1
1 2253
ck9663
2,878 Expert 2GB
For the most part, using function in your where CLAUSE will affect the performance. You should also watch out the BETWEEN operator, make sure the first expression is lower than the second expression being compared to. If you don't need to order the result, remove the ORDER BY. And only return the needed column, not all (*) of them.

Good Luck!!!

~~ CK
Jan 13 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Bacci | last post: by
2 posts views Thread by ensnare | last post: by
3 posts views Thread by aroy | last post: by
5 posts views Thread by AC Slater | last post: by
2 posts views Thread by Eugene | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.