472,145 Members | 1,571 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

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
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 2326
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
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.