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 9, 2006 10:32 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:26 PM
Points: 676, Visits: 433

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.




Post #314161
Posted Monday, October 9, 2006 6:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 27, 2013 7:13 AM
Points: 79, Visits: 19

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 NULL
BEGIN
    SET @sql = @sql + N' AND CustomerName = ''' + REPLACE(@CustomerName,'''','''''') + ''''
END




Post #314215
Posted Monday, October 9, 2006 7:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150

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

quote
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 DATETIME
DECLARE @CustID INT
SET @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 FREEPROCCACHE
DECLARE @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 FREEPROCCACHE
DECLARE @IntMin INT
DECLARE @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)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #314218
Posted Monday, October 9, 2006 7:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150

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?



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #314219
Posted Monday, October 9, 2006 8:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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.




Post #314225
Posted Monday, October 9, 2006 8:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150

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.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #314227
Posted Monday, October 9, 2006 9:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 3, 2012 12:19 AM
Points: 22, Visits: 68
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.


Post #314229
Posted Monday, October 9, 2006 9:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150

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 ON
go

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

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

SELECT *
FROM Customers
WHERE 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 Customers
WHERE (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 Customers
WHERE 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...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #314230
Posted Monday, October 9, 2006 9:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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.




Post #314232
Posted Tuesday, October 10, 2006 1:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

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!



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #314255
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse