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 = 1Inventory 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
_____________
Code for TallyGenerator -
Thanks for your reply.
I tried that, but it still gives same result.
Attaching screenshots
-
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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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 = 1I get execution plan as below:
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:
What could be the reason?
Thanks,
-
Did you also create that index in Prod ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
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.
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.
_____________
Code for TallyGenerator -
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.
_____________
Code for TallyGenerator -
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.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply