none
Who wrote this Article on SQL Server best practices

    Question

  • I saw an article on SQL Server Best Practices.  This article reported

    When possible, use WHERE clauses that compare a column to a value. Example: WHERE SUBSTRING(firstname,1,1) = 'm' can be rewritten as WHERE firstname like 'm% or WHERE DATEDIFF(yy,dateofbirth,GETDATE()) > 21 should be rewritten as WHERE dateofbirth < DATEADD(yy,-21,GETDATE()). This will allow the query to use an index seek to accelerate the processing rather relying on a table scan.

    However I've not been able to relocate that article for purposes of citation.

    Can anyone recall seein that article?


    Edward R. Joell MCSD MCDBA

    Tuesday, July 23, 2013 8:34 PM

Answers

  • Yet to my surprise, my test runs using "include client statistics" showed server time taking many milliseconds longer for the index seek than the table scan. Does anyone have any suggestions as to why this may be?

    You and the article are correct.  The WHERE-clause predicate should not wrap the column in an expression.  If you do then the expression is not sargable, and SQL Server is prevented from using indexes.

    There's no guarantee that fixing the WHERE-clause predicate will result in a different or better plan.  That will depend on the details of the query and the physical data structures.

    BTW client statistics are not the right way to measure query performance.  Instead you want to look at the server-side use of CPU and Logical IO.  Like this:

    set statistics io on
    set statistics time on
    
    --your query goes here
    
    set statistics io off
    set statistics time off

    The problem with client statistics is that a parallel query can use vastly more resources yet complete more quickly than a serial plan. 

    You can also look at the actual execution plan of the query using Query/Include Actual Execution Plan menu item in SSMS.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, July 24, 2013 2:06 PM
  • No that is not the article.  I am betting that it is one of the favorites I have that is now returning 404s.  Sad.  It was a good article full of very valuable advice.

    Edward R. Joell MCSD MCDBA

    • Marked as answer by joeller Monday, August 12, 2013 2:05 PM
    Wednesday, July 24, 2013 9:04 PM

All replies