June 3, 2008 at 2:41 pm
Hey guys
My friend said this was the site to come to for SQL information.
Im looking for some Optimizing tips for a slow running query.
I added over 20,000 new rows and it has slowed down quite a bit.
It was taking 8 seconds to run, but after rewriting the query and adding a few indexes its down to 3 seconds which is still very high.
The problem im looking at is that a Clustard Index Seek over 21,048 rows is consuming 67% of the SP's time.
This is a data paging query which only returns 50 rows at a time to the user.
I attached the Query and the execution plan.
Any help would be appreciated on speeding this bad boy up.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 3, 2008 at 2:45 pm
Any chance of you posting the code in the forum, instead of in a zip file? I'm sure you'll understand that I'm a little wary of opening a zip file posted on an open forum.
If you can post the code in the forum, I'll be very happy to help you with it, as will many others.
P.S.: Welcome to the site. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 2:58 pm
The clustered index seek in the execution plan refers to these 2 lines in the sql below
"AND (dbo.CSK_Store_Product.statusID <> 99)
AND (dbo.CSK_Store_Product.isDeleted = 0)"
SELECT
ProductList.productID,
ProductList.sku,
ProductList.productName,
ProductList.shortDescription,
ProductList.manufacturerID,
ProductList.statusID,
ProductList.productTypeID,
ProductList.shippingTypeID,
ProductList.shipEstimateID,
ProductList.taxTypeID,
ProductList.stockLocation,
ProductList.ourPrice,
ProductList.retailPrice,
ProductList.weight,
ProductList.currencyCode,
ProductList.unitOfMeasure,
ProductList.adminComments,
ProductList.length,
ProductList.height,
ProductList.width,
ProductList.dimensionUnit,
ProductList.isDeleted,
ProductList.listOrder,
ProductList.ratingSum,
ProductList.totalRatingVotes,
ProductList.createdOn,
ProductList.createdBy,
ProductList.modifiedOn,
ProductList.modifiedBy,
ProductList.imageFile,
dbo.CSK_Store_ShippingEstimate.shippingEstimate,
dbo.CSK_Store_ShippingEstimate.leadTimeDays,
dbo.CSK_Store_Manufacturer.manufacturer,
dbo.CSK_Store_ProductStatus.status,
dbo.CSK_Tax_Type.taxType,
dbo.CSK_Tax_Type.taxCode,
dbo.CSK_Store_ShippingType.shippingType,
dbo.CSK_Store_ShippingType.shippingCode
from
(
SELECT TOP 100 ROW_NUMBER() OVER
(
ORDER BY dbo.CSK_Store_Product.listOrder asc , dbo.CSK_Store_Product.productID
) as RowIndex,
dbo.CSK_Store_Product.productID,
dbo.CSK_Store_Product.sku,
dbo.CSK_Store_Product.productName,
dbo.CSK_Store_Product.shortDescription,
dbo.CSK_Store_Product.manufacturerID,
dbo.CSK_Store_Product.statusID,
dbo.CSK_Store_Product.productTypeID,
dbo.CSK_Store_Product.shippingTypeID,
dbo.CSK_Store_Product.shipEstimateID,
dbo.CSK_Store_Product.taxTypeID,
dbo.CSK_Store_Product.stockLocation,
dbo.CSK_Store_Product.ourPrice,
dbo.CSK_Store_Product.retailPrice,
dbo.CSK_Store_Product.weight,
dbo.CSK_Store_Product.currencyCode,
dbo.CSK_Store_Product.unitOfMeasure,
dbo.CSK_Store_Product.adminComments,
dbo.CSK_Store_Product.length,
dbo.CSK_Store_Product.height,
dbo.CSK_Store_Product.width,
dbo.CSK_Store_Product.dimensionUnit,
dbo.CSK_Store_Product.isDeleted,
dbo.CSK_Store_Product.listOrder,
dbo.CSK_Store_Product.ratingSum,
dbo.CSK_Store_Product.totalRatingVotes,
dbo.CSK_Store_Product.createdOn,
dbo.CSK_Store_Product.createdBy,
dbo.CSK_Store_Product.modifiedOn,
dbo.CSK_Store_Product.modifiedBy,
dbo.CSK_Store_Product.defaultImage AS imageFile,
(SELECT TOP (1) categoryID
FROM dbo.CSK_Store_Category
WHERE (dbo.CSK_Store_Product.productID = dbo.CSK_Store_Product.productID)
ORDER BY categoryID) AS categoryID,
CONVERT(money, dbo.CSK_Store_Product.ratingSum) / CONVERT(money,
dbo.CSK_Store_Product.totalRatingVotes) AS rating,
dbo.CSK_Store_Product.defaultImage,
dbo.CSK_Store_Product.productGUID,
dbo.CSK_Store_Product.attributeXML
FROM dbo.CSK_Store_Product WITH (NOLOCK)
WHERE
(
productID IN
(
SELECT productID
FROM CSK_Store_Product_Category_Map WITH (NOLOCK)
WHERE
(
categoryID IN
(
SELECT Id FROM dbo.GetChildren(42) AS GetChildren_1
)
)
)
)
AND (dbo.CSK_Store_Product.statusID <> 99)
AND (dbo.CSK_Store_Product.isDeleted = 0)
) as ProductList,
dbo.CSK_Store_ShippingEstimate WITH (NOLOCK),
dbo.CSK_Store_Manufacturer WITH (NOLOCK),
dbo.CSK_Store_ProductStatus WITH (NOLOCK),
dbo.CSK_Tax_Type,dbo.CSK_Store_ShippingType WITH (NOLOCK)
WHERE RowIndex > 50 AND RowIndex <= 100
and
(ProductList.shipEstimateID = dbo.CSK_Store_ShippingEstimate.shipEstimateID)
and
(ProductList.manufacturerID = dbo.CSK_Store_Manufacturer.manufacturerID)
and
(ProductList.statusID = dbo.CSK_Store_ProductStatus.statusID)
and
(ProductList.taxTypeID = dbo.CSK_Tax_Type.taxTypeID)
and
(ProductList.shippingTypeID = dbo.CSK_Store_ShippingType.shippingTypeID)
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 3, 2008 at 6:34 pm
What is the definition of the clustered index?
Can you get the dbo.GetChildren(42) function out of the where clause? Put the result in a temp table and join it.
June 3, 2008 at 7:12 pm
I'm not sure how to get that information. I create all my indexes in SQL Management Studios table designer.
I think your on to something. I removed it completely from the where clause and the query ran instantly even though now does a Clustered Index Scan over 4 times as many rows now. Ill let you know how it does after I modify it to do a join.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 3, 2008 at 9:20 pm
changing that into a inner join produces the same execution plan
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 4, 2008 at 2:42 am
I don't have the time right now to do a full analysis, but one thing I noticed from the exec plan is that the row estimates are off. Way off.
Estimated rows 32, actual rows 21000. Specifically that's the Product Category map, but there are similar things elsewhere.
Do you have auto update stats switched off in this DB? Try doing an update statistics on all the tables involved.
The Table valued function's not going to help in this regard, seeing as it doesn't have statistics
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2008 at 1:05 pm
Can you post the code for that function?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 1:28 pm
Auto Update for stats are turned on.
I updated the stats on all tables using this script
http://qa.sqlservercentral.com/scripts/Index+Management/31823/
but the estimated rows are still the same.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 4, 2008 at 1:34 pm
The function runs very fast.
It returns all categories that are under the parent category.
In the case of category 42 - it returns 965 subcategories , sub-sub categories and so on.
[dbo].[GetChildren] (@Id int)
RETURNS @result TABLE (Id int, ParentId int, Level smallint)
AS
BEGIN
DECLARE @Level smallint
-- get the top level node (magic requirement)
-- get starting node
SET @Level = 1
INSERT @result
SELECT categoryID, ParentId, @Level FROM CSK_Store_Category WITH (NOLOCK) WHERE categoryID = @Id
WHILE @Level < 1000 BEGIN -- weak condition to catch infinite recursion
-- get child nodes of current level's nodes
INSERT @result
SELECT t.categoryID, t.ParentId, @Level + 1 FROM CSK_Store_Category t WITH (NOLOCK)
JOIN @result r ON t.ParentId = r.Id AND @Level = r.Level
-- no child nodes ==> all done
IF @@ROWCOUNT = 0 BREAK
-- advance one level
SET @Level = @Level + 1
END
RETURN
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 4, 2008 at 2:12 pm
Try this:
;WITH
ProdCat (ProdID, CategoryID) as
(SELECT productID, min(categoryID)
FROM dbo.CSK_Store_Category
GROUP BY productID),
ChildCats (CatID, ParentID) as
(SELECT categoryID, null
FROM CSK_Store_Category WITH (NOLOCK)
WHERE categoryID = 42
UNION ALL
SELECT t2.categoryID, t2.parentID
FROM CSK_Store_Category t2
INNER JOIN ChildCats
ON t2.parentID = ChildCats.CatID),
ProdIDs (ProdID) as
(SELECT productID
FROM CSK_Store_Product_Category_Map WITH (NOLOCK)
INNER JOIN ChildCats
ON categoryID = CatID)
SELECT
ProductList.productID,
ProductList.sku,
ProductList.productName,
ProductList.shortDescription,
ProductList.manufacturerID,
ProductList.statusID,
ProductList.productTypeID,
ProductList.shippingTypeID,
ProductList.shipEstimateID,
ProductList.taxTypeID,
ProductList.stockLocation,
ProductList.ourPrice,
ProductList.retailPrice,
ProductList.weight,
ProductList.currencyCode,
ProductList.unitOfMeasure,
ProductList.adminComments,
ProductList.length,
ProductList.height,
ProductList.width,
ProductList.dimensionUnit,
ProductList.isDeleted,
ProductList.listOrder,
ProductList.ratingSum,
ProductList.totalRatingVotes,
ProductList.createdOn,
ProductList.createdBy,
ProductList.modifiedOn,
ProductList.modifiedBy,
ProductList.imageFile,
dbo.CSK_Store_ShippingEstimate.shippingEstimate,
dbo.CSK_Store_ShippingEstimate.leadTimeDays,
dbo.CSK_Store_Manufacturer.manufacturer,
dbo.CSK_Store_ProductStatus.status,
dbo.CSK_Tax_Type.taxType,
dbo.CSK_Tax_Type.taxCode,
dbo.CSK_Store_ShippingType.shippingType,
dbo.CSK_Store_ShippingType.shippingCode
from
(
SELECT TOP 100 ROW_NUMBER() OVER
(
ORDER BY dbo.CSK_Store_Product.listOrder asc , dbo.CSK_Store_Product.productID
) as RowIndex,
dbo.CSK_Store_Product.productID,
dbo.CSK_Store_Product.sku,
dbo.CSK_Store_Product.productName,
dbo.CSK_Store_Product.shortDescription,
dbo.CSK_Store_Product.manufacturerID,
dbo.CSK_Store_Product.statusID,
dbo.CSK_Store_Product.productTypeID,
dbo.CSK_Store_Product.shippingTypeID,
dbo.CSK_Store_Product.shipEstimateID,
dbo.CSK_Store_Product.taxTypeID,
dbo.CSK_Store_Product.stockLocation,
dbo.CSK_Store_Product.ourPrice,
dbo.CSK_Store_Product.retailPrice,
dbo.CSK_Store_Product.weight,
dbo.CSK_Store_Product.currencyCode,
dbo.CSK_Store_Product.unitOfMeasure,
dbo.CSK_Store_Product.adminComments,
dbo.CSK_Store_Product.length,
dbo.CSK_Store_Product.height,
dbo.CSK_Store_Product.width,
dbo.CSK_Store_Product.dimensionUnit,
dbo.CSK_Store_Product.isDeleted,
dbo.CSK_Store_Product.listOrder,
dbo.CSK_Store_Product.ratingSum,
dbo.CSK_Store_Product.totalRatingVotes,
dbo.CSK_Store_Product.createdOn,
dbo.CSK_Store_Product.createdBy,
dbo.CSK_Store_Product.modifiedOn,
dbo.CSK_Store_Product.modifiedBy,
dbo.CSK_Store_Product.defaultImage AS imageFile,
ProdCat.categoryID,
CONVERT(money, dbo.CSK_Store_Product.ratingSum) / CONVERT(money,
dbo.CSK_Store_Product.totalRatingVotes) AS rating,
dbo.CSK_Store_Product.defaultImage,
dbo.CSK_Store_Product.productGUID,
dbo.CSK_Store_Product.attributeXML
FROM dbo.CSK_Store_Product WITH (NOLOCK)
INNER JOIN ProdCat
ON CSK_Store_Product.productID = ProdCat.ProdID
INNER JOIN ProdIDs
ON CSK_Store_Product.productID = ProdIDs.ProdID
WHERE
AND (dbo.CSK_Store_Product.statusID <> 99)
AND (dbo.CSK_Store_Product.isDeleted = 0)
) as ProductList
INNER JOIN dbo.CSK_Store_ShippingEstimate WITH (NOLOCK)
ON ProductList.shipEstimateID = dbo.CSK_Store_ShippingEstimate.shipEstimateID
INNER JOIN dbo.CSK_Store_Manufacturer WITH (NOLOCK)
ON ProductList.manufacturerID = dbo.CSK_Store_Manufacturer.manufacturerID
INNER JOIN dbo.CSK_Store_ProductStatus WITH (NOLOCK)
ON ProductList.statusID = dbo.CSK_Store_ProductStatus.statusID
INNER JOIN dbo.CSK_Tax_Type,dbo.CSK_Store_ShippingType WITH (NOLOCK)
ON ProductList.taxTypeID = dbo.CSK_Tax_Type.taxTypeID
AND ProductList.shippingTypeID = dbo.CSK_Store_ShippingType.shippingTypeID
WHERE RowIndex > 50 AND RowIndex <= 100
Also, make sure there is an index on dbo.CSK_Store_Product ProductID, StatusID, isDeleted.
See if that runs better than what you currently have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 9:17 pm
Thank you very much!
I could not get what you gave me to work exactly as it is, but the execution time is now 2.437 seconds on average vs 5.966 seconds.
This is now an acceptable wait for querying everything under a main category.
I created an index with those columns but it is not being used according to the execution plan.
with childCats (categoryID, ParentId)
as
(
select categoryID, ParentId
from CSK_Store_Category WITH (NOLOCK)
where categoryID = 42
union all
select CSK_Store_Category.categoryID, CSK_Store_Category.ParentId
from CSK_Store_Category WITH (NOLOCK)
inner join childCats
on CSK_Store_Category.ParentId = childCats.categoryID
)
,
ProdIDs (productID)
as
(
SELECT productID
FROM CSK_Store_Product_Category_Map WITH (NOLOCK)
INNER JOIN childCats
ON CSK_Store_Product_Category_Map.categoryID = childCats.categoryID
)
SELECT
ProductList.productID,
ProductList.sku,
ProductList.productName,
ProductList.shortDescription,
ProductList.manufacturerID,
ProductList.statusID,
ProductList.productTypeID,
ProductList.shippingTypeID,
ProductList.shipEstimateID,
ProductList.taxTypeID,
ProductList.stockLocation,
ProductList.ourPrice,
ProductList.retailPrice,
ProductList.weight,
ProductList.currencyCode,
ProductList.unitOfMeasure,
ProductList.adminComments,
ProductList.length,
ProductList.height,
ProductList.width,
ProductList.dimensionUnit,
ProductList.isDeleted,
ProductList.listOrder,
ProductList.ratingSum,
ProductList.totalRatingVotes,
ProductList.createdOn,
ProductList.createdBy,
ProductList.modifiedOn,
ProductList.modifiedBy,
ProductList.imageFile,
dbo.CSK_Store_ShippingEstimate.shippingEstimate,
dbo.CSK_Store_ShippingEstimate.leadTimeDays,
dbo.CSK_Store_Manufacturer.manufacturer,
dbo.CSK_Store_ProductStatus.status,
dbo.CSK_Tax_Type.taxType,
dbo.CSK_Tax_Type.taxCode,
dbo.CSK_Store_ShippingType.shippingType,
dbo.CSK_Store_ShippingType.shippingCode
from
(
SELECT TOP 100 ROW_NUMBER() OVER
(
ORDER BY dbo.CSK_Store_Product.listOrder asc , dbo.CSK_Store_Product.productID
) as RowIndex,
dbo.CSK_Store_Product.productID,
dbo.CSK_Store_Product.sku,
dbo.CSK_Store_Product.productName,
dbo.CSK_Store_Product.shortDescription,
dbo.CSK_Store_Product.manufacturerID,
dbo.CSK_Store_Product.statusID,
dbo.CSK_Store_Product.productTypeID,
dbo.CSK_Store_Product.shippingTypeID,
dbo.CSK_Store_Product.shipEstimateID,
dbo.CSK_Store_Product.taxTypeID,
dbo.CSK_Store_Product.stockLocation,
dbo.CSK_Store_Product.ourPrice,
dbo.CSK_Store_Product.retailPrice,
dbo.CSK_Store_Product.weight,
dbo.CSK_Store_Product.currencyCode,
dbo.CSK_Store_Product.unitOfMeasure,
dbo.CSK_Store_Product.adminComments,
dbo.CSK_Store_Product.length,
dbo.CSK_Store_Product.height,
dbo.CSK_Store_Product.width,
dbo.CSK_Store_Product.dimensionUnit,
dbo.CSK_Store_Product.isDeleted,
dbo.CSK_Store_Product.listOrder,
dbo.CSK_Store_Product.ratingSum,
dbo.CSK_Store_Product.totalRatingVotes,
dbo.CSK_Store_Product.createdOn,
dbo.CSK_Store_Product.createdBy,
dbo.CSK_Store_Product.modifiedOn,
dbo.CSK_Store_Product.modifiedBy,
dbo.CSK_Store_Product.defaultImage AS imageFile,
(SELECT TOP (1) categoryID
FROM dbo.CSK_Store_Category
WHERE (dbo.CSK_Store_Product.productID = dbo.CSK_Store_Product.productID)
ORDER BY categoryID) AS categoryID,
CONVERT(money, dbo.CSK_Store_Product.ratingSum) / CONVERT(money,
dbo.CSK_Store_Product.totalRatingVotes) AS rating,
dbo.CSK_Store_Product.defaultImage,
dbo.CSK_Store_Product.productGUID,
dbo.CSK_Store_Product.attributeXML
FROM dbo.CSK_Store_Product WITH (NOLOCK)
INNER JOIN
ProdIDs
ON dbo.CSK_Store_Product.productID = ProdIDs.productID
WHERE
(dbo.CSK_Store_Product.statusID <> 99)
AND (dbo.CSK_Store_Product.isDeleted = 0)
) as ProductList,
dbo.CSK_Store_ShippingEstimate WITH (NOLOCK),
dbo.CSK_Store_Manufacturer WITH (NOLOCK),
dbo.CSK_Store_ProductStatus WITH (NOLOCK),
dbo.CSK_Tax_Type,dbo.CSK_Store_ShippingType WITH (NOLOCK)
WHERE RowIndex > 50 AND RowIndex <= 100
and
(ProductList.shipEstimateID = dbo.CSK_Store_ShippingEstimate.shipEstimateID)
and
(ProductList.manufacturerID = dbo.CSK_Store_Manufacturer.manufacturerID)
and
(ProductList.statusID = dbo.CSK_Store_ProductStatus.statusID)
and
(ProductList.taxTypeID = dbo.CSK_Tax_Type.taxTypeID)
and
(ProductList.shippingTypeID = dbo.CSK_Store_ShippingType.shippingTypeID)
RETURN
Nerd, Geek, Online Guru, Programming Master! Need I say more?
June 6, 2008 at 12:30 am
you had better replace 'productID IN ' clause with 'exist ()', the performace will be better if so.
June 6, 2008 at 12:48 pm
One of the things that I changed in my original suggestion was getting rid of "WHERE dbo.CSK_Store_Product.productID = dbo.CSK_Store_Product.productID". You do realize that that will ALWAYS be true, right? It makes it into a cross join (of sorts), which is going to slow the thing down.
The other thing was changing the join format to ANSI-92 standard (join conditions in the From clause, instead of the Where clause). That also will sometimes result in better speed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 3:36 pm
Thanks. I did not notice that.
That column is not even being used in the source code or even being returned by the main query to begin with ,so its been removed now. I'm pretty sure a bunch of those other columns are not even being used either. It used to select from a view that is used by quite a few other queries.
Select top 100 ROW_NUMBER() OVER.... from dbo.vwProduct
I left the joins how they were because after changing them and looking at the execution plan, It was performing the joins on the TOP X rows instead of only on the rows I am returning. However I did this before I removed the call to the getChildren function.
I just made the change to the ANSI join and now its joining only the rows I'm returning. Im not seeing a speed increase as the execution plan is still the same.
The estimated rows are still way off, but they are much better after removing the getChildren function.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply