Self Eliminated Parameters

  • Jeff Moden

    SSC Guru

    Points: 996651

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • laperrej

    SSC Journeyman

    Points: 94

    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.

  • Jeff Moden

    SSC Guru

    Points: 996651

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • laperrej

    SSC Journeyman

    Points: 94

    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.

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    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/

  • Jeff Moden

    SSC Guru

    Points: 996651

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DaveItz

    SSCrazy

    Points: 2146

    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 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply