Index scan

  • Hi,

    I have a query as below:

    ;With P As 
    (Select ProductID
    --Query that returns around 600 rows
    )
    Select P.ProductID, I.QTY As Qty
    From P
    Join Inventory AS I On I.ProductID = P.ProductID and I.WarehouseID = 1

    Inventory table has ProductID + WarehouseID as Primary key.

    WarehouseID value can be only 1 or 2 in the table.

    Total number records with WarehouseID = 1 are around 240K.

    Query plan does index scan on PK and uses only WarehouseID as predicate, As a result, it processes all 240K records.

    How can I write query so that both WarehouseID and ProductID are used as predicates and so only those 600 records are read and processed from Inventory table?

    I have confirmed that all statistics are up-to-date.

    Please let me know if I need to provide more info.

    Thanks in Advance,

     

  • Try using GROUP BY ProductId in CTE query

  • Thanks for your reply.

    I tried that, but it still gives same result.

    Attaching screenshots

     

    Attachments:
    You must be logged in to view attached files.
  • It's likely your PK on Inventory should be ( WarehouseID, ProductID ), at least based on that query (of course if you have a significant number of queries that use ProductID only, then that wouldn't apply).

    That structure would allow a seek into the Inventory table and avoid reading all 240K rows.

    Another way would be to create an index on ( WarehouseID, ProductID ) INCLUDE ( Qty ).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hi Scott,

    I tried creating an index on (WarehouseID, ProductID) INCLUDE(Qty). It still does the same.

    On Dev database, for this query:

    ;With P As 
    (Select CP.ProductID
    From CatProduct CP
    Join Product P On CP.ProductID = P.ProductID
    Join ProductType pt with(nolock)
    On P.ItemType = pt.ProductTypeID
    And pt.IsOnline = 1
    Where
    CP.CategoryID = 1234
    And p.Active = 1 And p.ActiveOnline = 1
    And P.ApproveForOnline = 1
    )
    Select P.ProductID,ISNULL(inv.QTYonHand, 0)
    From P
    Join Inventory AS inv On inv.ProductID = P.ProductID and inv.WarehouseID = 1

    I get execution plan as below:

    EXcPlanDev

    As you can see above, only the records from CTE get fed for Inventory table. That's what I want to happen in PROD..but it is not working.

    On PROD, I get it as below:

    EXcPlanPROD

    What could be the reason?

    Thanks,

     

     

  • Did you also create that index in Prod ?

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • You seem to be focused on the join between the CTE and the Inventory table - which is not where the problem exists.  The problem exists in the CTE portion of the query.

    The plan from your test environment shows us that the tables CasProduct and Product are joined first...then a key lookup from the Product table, then the ProductType table is joined...and finally that is joined to Inventory.

    A bit concerning are the estimates on the Inventory table and the results from the CTE - where both estimate 201 rows to be returned and actual rows are 379.

    The plan from production starts with the Inventory table and returns all of the rows.  Presumably that is because the new index is being utilized and almost all rows are returned.  The version in PROD is essentially returning all rows and joining - then filtering.

    In TEST - validate your estimates are up to date first...then review the CTE query and validate the joins on each table are correct.  It looks to me like there may be a missing join predicate on the ProductType or the statistics are incorrect for that table also - as it expects 14 rows and is only returning 12.

    Once you have that...then verify you have the same indexes in PROD and statistics are up to date on all tables.  Compare the plans after confirming statistics and if they are not the same - we can take a look to see what differences there are and why.  Most likely, the difference (if any) will be due to the amount of data in PROD compared to TEST.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • People don't understand that a CTE is NOT executed first and then the rest of the query works against that as if it were a table with the possible exception if the CTE contains a blocking operator.

    With that, I'll suggest that you use the query currently contained in the CTE to create a Temp Table and then join to that using the outer query but being pointed to the Temp Table instead of a CTE (Divide'n'Conquer method, which works some serious PFM in many cases).

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    People don't understand that a CTE is NOT executed first and then the rest of the query works against that as if it were a table with the possible exception if the CTE contains a blocking operator.

    With that, I'll suggest that you use the query currently contained in the CTE to create a Temp Table and then join to that using the outer query but being pointed to the Temp Table instead of a CTE (Divide'n'Conquer method, which works some serious PFM in many cases).

    It's not gonna help here.

    In my 1st reply I suggested add GROUP BY to the CTE query - it should have materialised the output of CTE and do exactly what a temp table is called to do. OP replied that it did not help.

    So, it's another problem here. Most likely the cost of bookmark lookup for the estimated number of rows (based on current statistics) considered higher then simple index scan.

    Another problem - the query returns P.ProductID instead of inv.ProductID.

    And the last issue here - the way the query is written. Actually, the whole CTE part must be moved into WHERE EXISTS and only Inventory table should be left in the main query.

     

     

  • A significant part of both PROD and test queries if Key Lookup on Product table. It might affect the plan selection too.

    Including ProductTypeId into the index IX_Active_ActiveOnline might help a lot here.

  •  

    This is how the query should look like:

    SELECT inv.ProductID, ISNULL(inv.QTYonHand, 0)
    FROM Inventory AS inv
    WHERE inv.WarehouseID = 1
    AND EXISTS ( select *
    from Product P
    inner join CatProduct CP on CP.ProductID = P.ProductID and CP.CategoryID = 1234
    inner join ProductType pt on pt.ProductTypeID = P.ItemType And pt.IsOnline = 1
    where p.Active = 1 And p.ActiveOnline = 1 And P.ApproveForOnline = 1
    and inv.ProductID = P.ProductID
    )

    And yes, having INCLUDE(ItemType) in the definition of IX_Active_ActiveOnline  should help a lot.

     

  • Thank you @sergiy, @jeff Moden, @Jeffrey Williams, @scott Pletcher..You all provided some great suggestions and enhanced my knowledge.

    I will try your suggestions and post my results here again.

     

Viewing 12 posts - 1 through 12 (of 12 total)

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