January 13, 2011 at 3:42 am
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:
[Code]
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
[/Code]
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.
January 13, 2011 at 3:58 am
Have to write it this way ,like this if the condition is true it does not continues for secound
if col1 is null it does not check if col1 = @col1
SELECT *
FROM tableA
WHERE
(col1 is null or col1 = @col1) AND
(col2 is null or col2 = @col2) AND
(col3 is null or col3 = @col3) AND
(col3 is null or col4 = @col4) AND
ORDER BY colrk DESC
January 13, 2011 at 4:05 am
Will this give performance boost
January 13, 2011 at 4:06 am
The use of isnull function should avoid the use if indexes..
use of functions on the query predicates avoids the use of indexes
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 13, 2011 at 4:20 am
krishnaroopa (1/13/2011)
Will this give performance boost
Will improve performance
January 13, 2011 at 4:25 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply