Self Eliminated Parameters
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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. -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
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.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply