SQLServerCentral / Discuss Content Posted by Alex Grinberg / Article Discussions / Article Discussions by Author / Self Eliminated Parameters / Latest Posts

RE: Self Eliminated Parameters

Dave I — Tue, 10 Oct 2006 08:38:00 GMT

That assumes that the Column_Name field doesn't allow null values otherwise, I believe, if the value does happen to be null setting the @Param_Name to null won't "eliminate" it. Null is not equal to Null.

RE: Self Eliminated Parameters

Jeff Moden — Tue, 10 Oct 2006 06:20:00 GMT

Maybe that's the difference... I'm running SQL Sever 2000 Enterprise Edition with SP4. Just kidding... I get the same execution plans on an SP3a box as well. I don't know what the difference is because I havn't altered Northwind on either of mine either.

RE: Self Eliminated Parameters

colin.Leversuch-Roberts — Tue, 10 Oct 2006 01:22:00 GMT

There's not enough data in northwind as it is to properly test this and you must test this like the author suggested using multiple ( optional ) parameters - testing with one where clause does not fully illustrate the issues, the idea of using this approach, quite correctly and I agree with Alex's solution, is that you wish to typically deal with many parameters , please choose at least 6 for testing, which may or may not be passed thus making multiple procs ( one to match each combination ) or multiple if statements unworkable. Then see how the performance degrades with respect to data size and how difficult it is to index. And don't always just have one matching result per where clause - real life often isn't like that.

btw - good one Alex you've sparked an interesting thread!

RE: Self Eliminated Parameters

laperrej — Mon, 09 Oct 2006 21:45:00 GMT

Fyi, this is my execution plan for the smart filter query:

StmtText ------------------------------------------------------------------------------------------------------------------------------------------- |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Northwind].[dbo].[Customers])) |--Index Scan(OBJECT[Northwind].[dbo].[Customers].[City]), WHERE[Customers].[CustomerID]=[@CustomerID] OR [@CustomerID]=NULL))

My Northwind database is unaltered (I never used it until today). I updated the statistics (UPDATE STATISTICS customers WITH FULLSCAN) but I get the same query plan. I'm running SQL 2000 SP3a btw.

RE: Self Eliminated Parameters

Jeff Moden — Mon, 09 Oct 2006 21:40:00 GMT

I ran your code against the Northwind database (assume that's where you were running it, as well)... Just to be sure, this is your code, isn't it (I added the ShowPlan)

SET SHOWPLAN_TEXT ONgo

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

SELECT *FROM CustomersWHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)

SELECT *FROM CustomersWHERE CustomerID = @CustomerID

When I run it, I get the same thing as when I show the graphical execution plan...

StmtText ------------------------------ SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText ----------------------- SET SHOWPLAN_TEXT ON

(1 row(s) affected)

StmtText --------------------------------------------------------------------------------------

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

SELECT *FROM CustomersWHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)

(2 row(s) affected)

StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------- |--Clustered Index Scan(OBJECT[Northwind].[dbo].[Customers].[PK_Customers]), WHERE[Customers].[CustomerID]=[@CustomerID] OR [@CustomerID]=NULL))

(1 row(s) affected)

StmtText ----------------------------------------------------------------

SELECT *FROM CustomersWHERE CustomerID = @CustomerID

(1 row(s) affected)

StmtText ------------------------------------------------------------------------------------------------------------------------------------------------ |--Clustered Index Seek(OBJECT[Northwind].[dbo].[Customers].[PK_Customers]), SEEK[Customers].[CustomerID]=[@CustomerID]) ORDERED FORWARD)

(1 row(s) affected)

StmtText ----------------------------- SET STATISTICS PROFILE OFF

(1 row(s) affected)

No city index... no table scan... I'm thinking that something is wrong with your Northwind Customers table... it must be missing an index or PK or something...

RE: Self Eliminated Parameters

laperrej — Mon, 09 Oct 2006 21:24:00 GMT

Please look again . The code I posted does an index scan on the City index. The primary key is CustomerID. The statistics must tell SQL Server that scanning the City index to find CustomerIDs is cheaper than doing a full table scan.

RE: Self Eliminated Parameters

Jeff Moden — Mon, 09 Oct 2006 20:52:00 GMT

City Index? Neither the code you posted or my code would even come near a "city" index if the Primary Key was the CustomerID column. Certainly, it wouldn't do a table scan if an index were present on CustomerID... Index Scan, yes... table scan, no.

The only reason I made the disclaimer about INT was because I didn't test it using VARCHAR.

RE: Self Eliminated Parameters

laperrej — Mon, 09 Oct 2006 20:33:00 GMT

Note that it has nothing to do with the fact that it's an integer ID btw. Because we're looking for CustomerID, which is the clustered index, and every non-clustered index contains the clustered index column(s) as the last column(s), SQL Server will scan the smallest index, in this case the City index. If CustomerID was a varchar, the execution plan would be just the same.

RE: Self Eliminated Parameters

Jeff Moden — Mon, 09 Oct 2006 19:49:00 GMT

Alex,

Nice article especially for the proverbial "newbie". I do agree that the examples were a bit overdone but how are ya going to learn if you don't try?

RE: Self Eliminated Parameters

Jeff Moden — Mon, 09 Oct 2006 19:39:00 GMT

I've found that that is NOT true... not with INT ID's anyway... In my testing of the various solutions posted against this article, I get INDEX SCAN's, not table scans using WHERE (CustID = @CustID OR @CustID IS NULL).

In fact, the method advertised is the second fastest method (direct and dynamic SQL tied for first fastest) of those posted and some not posted. Considering the flexibility of the code and the fact that you don't have to juggle the dynamic SQL code to make it "injection proof", it's not a bad method.

Still, I do like the dynamic SQL because of the blazing speed even if I do have to add a little code to keep Gumby and his brothers out...

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.

More efficient? Like a previous respondent stated, you may want to test the code before making those type of statements. The "efficient" method you spoke of forces a table scan resulting in a 43 second time of return. The method Alex wrote about (colname=@variable OR @variable IS NULL) only took 453 MILLI-seconds. That's about 94 times faster give or take a clock cycle.

Here's the code I used on the lowly 1.4 million record Customer table on the Test Server at work... some of the print statements also serve as the documentation for what I found for performance on a quiet server...

DECLARE @StartTime DATETIMEDECLARE @CustID INTSET @CustID = 1650

PRINT 'BASELINE... simple direct lookup (0.016 seconds - Index Seek)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET @StartTime = GETDATE() SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE CustID = @CustID PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

PRINT 'Using the technique from the article (0.453 seconds - Index Scan)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET @StartTime = GETDATE() SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE (CustID = @CustID OR @CustID IS NULL) PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

PRINT 'Using dynamic sql (extendable) (0.016 seconds - Index Seek)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHEDECLARE @SQL VARCHAR(8000) SET @StartTime = GETDATE() SET @SQL = ' SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE 1=1' + ISNULL(' AND CustID = '+STR(@CUSTID),'') EXEC (@SQL) PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

PRINT 'Using ISNULL (one of the alternatives offered) (43.110 seconds - Table Scan)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET @StartTime = GETDATE() SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE CustID = ISNULL(@CustID,CustID) --REAL SLOW!!! TABLE SCAN!!! PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

PRINT 'Using semi-static method (42.703 seconds - Table Scan)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHEDECLARE @IntMin INTDECLARE @IntMax INT SET @IntMin = -2147483648 SET @IntMax = 2147483647 SET @StartTime = GETDATE() SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE CustID BETWEEN ISNULL(@CustID,@IntMin) AND ISNULL(@CustID,@IntMax) PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

PRINT 'Using CASE method (42.610 seconds - Table Scan)' DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET @StartTime = GETDATE() SELECT * FROM dbo.Customer WITH (NOLOCK) WHERE CustID = CASE WHEN @CustID IS NULL THEN CustID ELSE @CustID END PRINT DATEDIFF (ms,@StartTime,GETDATE()) PRINT REPLICATE('-',78)

RE: Self Eliminated Parameters

roygelerman — Mon, 09 Oct 2006 18:14:00 GMT

Be careful with this one.

we have tables with 6 million rows and doing

WHERE (CustomerName = @CustomerName OR @CustomerName IS NULL)killed perfomance. The query took more than 2 minutes.

User dynamic SQL with EXEC or SP_EXECUTESQL.watch out for single quotes with EXEC, to prevent SQL Injection and to deal with certain last names.O'Malley in SQL is 'O''Malley'IF @CustomerName IS NOT NULLBEGIN SET @sql = @sql + N' AND CustomerName = ''' + REPLACE(@CustomerName,'''','''''') + ''''END

RE: Self Eliminated Parameters

cliffb — Mon, 09 Oct 2006 10:32:00 GMT

I do not beleive the author says the solution is clever. It is just offered as a solution to a common problem. Personally, I have used your technique as it works well for our environment. In some circumstances, we have used Dynamic SQL (Prior to learning this technique).

Overall, the article examples should be simplified for beginners to follow. The technique is valid however.

RE: Self Eliminated Parameters

Yelena Varshal — Mon, 09 Oct 2006 09:03:00 GMT

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"

RE: Self Eliminated Parameters

Bilal Dinc — Mon, 09 Oct 2006 07:18:00 GMT

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.

RE: Self Eliminated Parameters

jwainz — Mon, 09 Oct 2006 07:02:00 GMT

Thanks for the tip, I've come at this problem in the past without any good solutions. For smaller tables, this is perfect!

RE: Self Eliminated Parameters

RyanRandall — Mon, 09 Oct 2006 03:53:00 GMT

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

RE: Self Eliminated Parameters

colin.Leversuch-Roberts — Mon, 09 Oct 2006 03:17:00 GMT

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!!

RE: Self Eliminated Parameters

Josep — Mon, 09 Oct 2006 02:10:00 GMT

Or the use of the SQL standard COALESCE. Example:WHERE Column_Name=COALESCE(@Param_Name, Column_Name)

RE: Self Eliminated Parameters

Michiel Rens — Mon, 09 Oct 2006 00:27:00 GMT

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

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

RE: Self Eliminated Parameters

Thomas James — Sun, 08 Oct 2006 23:49:00 GMT

Another way i commonly use is an extension on the above

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

RE: Self Eliminated Parameters

laperrej — Sun, 08 Oct 2006 20:57:00 GMT

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 CustomersWHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)

SELECT *FROM CustomersWHERE CustomerID = @CustomerID

Self Eliminated Parameters

Alex Grinberg-230981 — Mon, 02 Oct 2006 14:29:00 GMT

Comments posted here are about the content posted at temp