Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 3:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 9:41 PM
Points: 179, Visits: 157
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.
Post #1047130
Posted Thursday, January 13, 2011 3:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 4:52 AM
Points: 54, Visits: 195
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
Post #1047145
Posted Thursday, January 13, 2011 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 9:41 PM
Points: 179, Visits: 157
Will this give performance boost
Post #1047151
Posted Thursday, January 13, 2011 4:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 583, Visits: 966
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 | MCSA SQL Server 2012
Post #1047152
Posted Thursday, January 13, 2011 4:20 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 4:52 AM
Points: 54, Visits: 195
krishnaroopa (1/13/2011)
Will this give performance boost


Will improve performance
Post #1047161
Posted Thursday, January 13, 2011 4:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 1,949, Visits: 8,292
Try this link

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/




Clear Sky SQL
My Blog
Kent user group
Post #1047162
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse