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 123»»»

Self Eliminated Parameters Expand / Collapse
Author
Message
Posted Monday, October 2, 2006 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:19 PM
Points: 27, Visits: 311
Comments posted here are about the content posted at temp
Post #312755
Posted Sunday, October 8, 2006 8:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 3, 2012 12:19 AM
Points: 22, Visits: 68

This technique, although interesting, should be used sparingly as it affects performance. The optimizer will perform full table scans on all tables that have a smart filter on them. Therefore, I believe this technique is only acceptable if you're expecting your query to need full table scans anyway. Switch on Execution Plan viewing and inspect the execution plans for the SQL below. The SELECT with the smart filter will always read the entire Customers table, the SELECT you would normally arrive at doing it the conventional way (by building up your SQL according to the filter values that were not null or not the default value) will use a clustered index seek.

DECLARE @CustomerID nchar(5)
SET @CustomerID = 'AROUT'

SELECT *
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)

SELECT *
FROM Customers
WHERE CustomerID = @CustomerID




Post #314028
Posted Sunday, October 8, 2006 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 7, 2007 9:38 PM
Points: 1, Visits: 1

Another way i commonly use is an extension on the above

 

WHERE
((@customerid is not null and customerid = @customerid) or @customerid is null)

Post #314030
Posted Monday, October 9, 2006 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 7, 2010 12:40 AM
Points: 8, Visits: 17

If NULL is the default value used, the following construct can also be used:

WHERE (Column_Name = ISNULL(@Param_Name, Column_Name))

Post #314031
Posted Monday, October 9, 2006 2:10 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:18 AM
Points: 139, Visits: 4,622

Or the use of the SQL standard COALESCE. Example:

WHERE Column_Name=COALESCE(@Param_Name, Column_Name)
Post #314036
Posted Monday, October 9, 2006 3:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

I experimented with this many years ago, if tables are small the benefits are good but on anything else the performance is apalling in terms of i/o and data cache flush. The more optional parameters there are the worse it gets. However, if the proc doesn't get called too often e.g. it's not in an oltp system and you need to avoid dynamic sql because of the security issues and views don't do much then I agree it's a good solution.

From my view in performance tuning developers usually find this and use it without testing on higher numbers of rows ( I won't say large table as it's all relative )  - so it works fine in test - in production tables get into double figure thousands of rows ( or worse ) and suddenly you're looking at a simple proc which is doing very high i/o to return ( usually ) 1 row. So basically what I'd say is this solution is good but it doesn't scale, so use with care.

I'm not having a pop here Alex btw .. but this illustrates that you should test with execution plans when developing code solutions and remember to scale up and out as part of testing.

I hope we don't get into another one of these silly dynamic sql arguments either!!



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #314040
Posted Monday, October 9, 2006 3:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

Alex - were you aware of another of Erland's excellent articles which discusses just this problem (in a good deal of detail)?

http://www.sommarskog.se/dyn-search.html

 




Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #314044
Posted Monday, October 9, 2006 7:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 7, 2013 11:44 AM
Points: 51, Visits: 40
Thanks for the tip, I've come at this problem in the past without any good solutions. For smaller tables, this is perfect!


Post #314065
Posted Monday, October 9, 2006 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 12:41 PM
Points: 1, Visits: 2

how is this technique all that clever? If anything, using

select *

from Customers

where customerid=isnull(@customerid,customerId)

 

is more efficient, easier to read and takes up less space.

Post #314069
Posted Monday, October 9, 2006 9:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 3,475, Visits: 584
I found the example difficult to follow. To demonstrate the technique you could use one table with one WHERE without XML processing that is not easy for entry-level developers and professional level administrators and without joins. Also there is too much stress on NULL as a defult value. Do not forget that we mostly are in the described situation when users have to select something and they do it through the front end. The developers  often set default value for the dropdown boxes to something meaningful like "please, select the value from the list" or in many cases "All"


Regards,
Yelena Varshal

Post #314135
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse