Post reply

Estimated Execution Plan

  • December 27, 2006 at 12:32 pm

    #69373

    I have the following sql script used in a stored procedure:

    declare  @ordernumber  varchar(7),

      @UPCCode  varchar(14)

     SELECT * from tblSerial

      WHERE orderNumber=@ordernumber AND

      UPCCode=@UPCCode AND

      Stage='TOP' AND

      Caseid='NOID' OR

      orderNumber=@ordernumber and

      UPCCode=@UPCCode and

      Stage='LBL' AND

      caseID='NOID' 

    The Estimated Execution Plan shows a 41% Bookmark Lookup and a 59% Index Seek.

    If I change the script as follows:

    declare  @ordernumber  varchar(7),

      @UPCCode  varchar(14)

     SELECT * from tblSerial

      WHERE ((orderNumber=@ordernumber AND

      UPCCode=@UPCCode AND Caseid='NOID') and

      (Stage='TOP' OR Stage='LBL'))

    The Estimated Execution Plan shows a 1% Bookmark Lookup and a 98% Index Seek.

     

    Which of the above is better and why?

    Thanks for the feedback,  Richard

     

  • December 27, 2006 at 1:12 pm

    #680166

    Have you run them both? I'll guess that they won't give you the same data.

    The WHEREs aren't looking for the same data.

    This is the first where:

    WHERE orderNumber=@ordernumber AND

    UPCCode=@UPCCode AND

    Stage='TOP' AND

    Caseid='NOID' OR

    orderNumber=@ordernumber and

    UPCCode=@UPCCode and

    Stage='LBL' AND

    caseID='NOID'

    It is NOT:

    WHERE orderNumber=@ordernumber AND

    UPCCode=@UPCCode AND

    Stage='TOP' AND

    Caseid='NOID'

    OR

    orderNumber=@ordernumber and

    UPCCode=@UPCCode and

    Stage='LBL' AND

    caseID='NOID'

    That would be (notice parenthesis):

    WHERE (orderNumber=@ordernumber AND

    UPCCode=@UPCCode AND

    Stage='TOP' AND

    Caseid='NOID') OR

    (orderNumber=@ordernumber and

    UPCCode=@UPCCode and

    Stage='LBL' AND

    caseID='NOID')

    Instead it is the equivelant of this:

    WHERE (orderNumber=@ordernumber AND

    UPCCode=@UPCCode AND

    Stage='TOP') AND

    (Caseid='NOID' OR

    orderNumber=@ordernumber)

    and

    (UPCCode=@UPCCode and

    Stage='LBL' AND

    caseID='NOID')

    -SQLBill

  • December 27, 2006 at 2:57 pm

    #680184

    I changed the first sql script, with additional parenthesis which should now have the same result as the second script, is as follows:

     SELECT * from tblSerial 

      WHERE ((orderNumber=@ordernumber AND UPCCode=@UPCCode AND

      Stage='TOP' AND Caseid='NOID')

    OR

      (orderNumber=@ordernumber and UPCCode=@UPCCode and

      Stage='LBL' AND caseID='NOID'))

    Then I modified the script to have the columns match the index:

     SELECT * from tblSerial

      WHERE ((Caseid='NOID' AND orderNumber=@ordernumber AND UPCCode=@UPCCode) and

      (Stage='TOP' OR Stage='LBL'))

    The first script execution plan has 40% bookmark lookup and 60% index seek.

    The second script execution plan has 1% bookmark lookup and 98% index seek.

    Once again, which execution plan is better and why?

  • December 27, 2006 at 10:15 pm

    #680209

    Hi

    you shud avoid bookmark lookups as much as possible as they can degrade performance while an index seek will imporve performance especially when dealing with a large number of rows.

    Again I may not be absolutely correct... hope some of the experts can throw more light on this.

    "Keep Trying"

  • December 28, 2006 at 2:27 am

    #680226

    I guess the second should be faster, however the result of these 2 queries will not be the same, so it is hard to say will which one is faster, anyway, bookmark is slow.

  • December 28, 2006 at 8:04 am

    #680258

    Percentages differ from the cost. You need to check the total cost first, and then focus at the high percentage part to enhance performance.

  • December 28, 2006 at 8:49 am

    #680270

    From what I know (and my previous experience), the bookmarks should be avoided.

    Also,

    1. Look at the real execution plan

    2. Look at the sub-tree cost

    3. SET STATISTICS IO ON and then run the query to see how many reads are done by each query and compare

  • December 29, 2006 at 8:24 am

    #680509

    Thanks for all the feedback.  I actually used real data in my test database and both sql statements return the same expected results...based on the execution plan results, I will be using the second sql code as this has the lowest bookmark lookup...

    SET STATISTICS IO ON

    SELECT * from tblSerial

      WHERE ((orderNumber = '005303' AND UPCCode = '01654223374' AND

      Stage='TOP' AND Caseid='NOID')

    OR

      (orderNumber = '005303' and UPCCode = '01654223374' AND

      Stage='LBL' AND caseID='NOID'))

    --(5 row(s) affected)

    --Table 'tblSerial'. Scan count 2, logical reads 31, physical reads 0, read-ahead reads 0.

    --Bookmark Lookup 99%,subtree cost .269; Index Seek 1%,subtree cost .00338

    SELECT * from tblSerial

      WHERE ((Caseid='NOID' AND orderNumber='005303' AND UPCCode= '01654223374') AND

      (Stage='TOP' OR Stage='LBL'))

    --(5 row(s) affected)

    -- Table 'tblSerial'. Scan count 2, logical reads 21, physical reads 0, read-ahead reads 0.

    --Bookmark Lookup 4%,subtree cost .00343; Index Seek 96%,subtree cost .00328

  • December 29, 2006 at 8:28 am

    #680512

    What is the execution time difference?

    Bookmark lookups are not always bad...

     

    MohammedU
    Microsoft SQL Server MVP

  • January 2, 2007 at 1:11 pm

    #680821

    I would definitely go with the 2nd one:

    - # logical reads is lower

    - sub-tree cost is lower

    These are 2 main metrics I currently use for measuring the efficiency of my query/stored procedure.

     

    If anyone has better metrics to suggest, please share ...

Viewing 10 posts - 1 through 9 (of 9 total)

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