Query optimization - where - ISNULL

[krishnaroopa]

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:

SELECT *
FROM tableA 
WHERE
ISNULL(col1, @col1) = @col1 AND
ISNULL(col2, @col2) = @col2 AND
ISNULL(col3, @col3) = @col3 AND
ISNULL(col4, @col4) = @col4 AND
@col5 BETWEEN ISNULL(col5, @col5) AND ISNULL(col5, @col5) AND 
@coldate BETWEEN ISNULL(effDate, @coldate) AND ISNULL(expDate, @coldate)
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.

[ck9663]

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