SQL Server Performance Forum – Threads Archive

Help needed to troubleshoot a performance issue

Hi, This stored procedure is taking a hell lot of a time. This SP returns only 2000 records but it takes close to one minute… I suspect this is because of the 5 update statement which we use. Can we make it as a single update statement? Reads is around 50k for this SP, CPU time is also huge. Any pointers/suggestions?? ALTER PROCEDURE [Product].[GetProductsAttributePRC]
(
@productFamily VARCHAR(100),
@prodType VARCHAR(100),
@majorModel VARCHAR(10),
@minorModel VARCHAR(10),
@custCode VARCHAR(3)
)
as –Temporary Table to hold all the value viz. DocNo, Title, RevNo, RevDate of the products
DECLARE @AttribValue TABLE
(
ProductId INT,
DocNo VARCHAR(100),
Title VARCHAR(100),
RevNo VARCHAR(100),
Revdate VARCHAR(100),
FileLocation VARCHAR(200),
PrevRevisionNo VARCHAR(100))
INSERT INTO @AttribValue (ProductId)
SELECT DISTINCT a.productid FROM product p (NOLOCK)
JOIN productType pt (NOLOCK) ON pt.productTypeId=p.productTypeid AND pt.[Name] = ‘test’
AND p.status = ‘A’
JOIN attribute a (NOLOCK) ON a.productId=p.productId AND LTRIM(RTRIM(a.Value)) = ‘TT’
AND a.attributeTypeID = 7
JOIN PRODUCTCONFIGURATIONUSAGE pcu (NOLOCK) ON pcu.ProductId=p.productId
JOIN configurationItemUsage ciu (NOLOCK) ON ciu.configId=pcu.configId
JOIN item i (NOLOCK) ON i.ItemId = ciu.ItemId AND i.ItemTypeId=1 and i.ItemValue = ‘abc’
JOIN configurationItemUsage ci (NOLOCK) ON ci.configId=pcu.configId
JOIN item it (NOLOCK) ON it.ItemId = ci.ItemId AND it.ItemTypeId = 2 and it.ItemValue = ‘xxx’
UPDATE @AttribValue SET DocNo = a.[Value] FROM attribute a (NOLOCK) JOIN @AttribValue at ON a.productId=at.productId
WHERE a.attributeTypeid=1 –Updates the value of the revNo column
UPDATE @AttribValue SET RevNo = a.[Value] FROM attribute a (NOLOCK) JOIN @AttribValue at ON a.productId=at.productId
WHERE a.attributeTypeid=2 –Updates the value of the revDate column
UPDATE @AttribValue SET Revdate = REPLACE(CONVERT(Varchar(10),CONVERT(datetime,a.[Value]),110),’-‘,’/’) FROM attribute a (NOLOCK) JOIN @AttribValue at ON a.productId=at.productId
WHERE a.attributeTypeid=3 –Updates the value of the Title column
UPDATE @AttribValue SET Title = a.[Value] FROM attribute a (NOLOCK) JOIN @AttribValue at ON a.productId=at.productId
WHERE a.attributeTypeid=4 –Updates the value of the Title column
UPDATE @AttribValue SET FileLocation = a.[Value] FROM attribute a (NOLOCK) JOIN @AttribValue at ON a.productId=at.productId
WHERE a.attributeTypeid=5 SELECT DISTINCT * FROM @AttribValue ORDER BY 2 TIA
Sri

---

My additions below in blue … Looks like the updates can easily be replaced with JOINs in the original SELECT statement. Perhaps the person who created this query didn’t know that you can read from multiple ‘copies’ of the same table in a single query? This would also eliminate the need for a temp table. Looking at the query, it also looks as if the concept of outer joins was unknown – assuming that the UPDATE is used to insert values where there is not always a match. – note also that I added INNER to the existing JOINs, just to keep things tidy SELECT DISTINCT a.productid, Doc.Value AS DocNo, Rev.Value AS RevNo
FROM product p (NOLOCK)
INNER JOIN productType pt (NOLOCK) ON pt.productTypeId=p.productTypeid AND pt.[Name] = ‘test’
AND p.status = ‘A’
INNER JOIN attribute a (NOLOCK) ON a.productId=p.productId AND LTRIM(RTRIM(a.Value)) = ‘TT’
AND a.attributeTypeID = 7
INNER JOIN PRODUCTCONFIGURATIONUSAGE pcu (NOLOCK) ON pcu.ProductId=p.productId
INNER JOIN configurationItemUsage ciu (NOLOCK) ON ciu.configId=pcu.configId
INNER JOIN item i (NOLOCK) ON i.ItemId = ciu.ItemId AND i.ItemTypeId=1 and i.ItemValue = ‘abc’
INNER JOIN configurationItemUsage ci (NOLOCK) ON ci.configId=pcu.configId
INNER JOIN item it (NOLOCK) ON it.ItemId = ci.ItemId AND it.ItemTypeId = 2 and it.ItemValue = ‘xxx’
LEFT JOIN attribute Doc (NOLOCK) ON p.productId = Doc.productId AND Doc.attributeTypeid=1
LEFT JOIN attribute Rev (NOLOCK) ON p.productId = Rev.productId AND Rev.attributeTypeid=2 ******** Just a thought: if you’re having to do this type of query all the time, then perhaps this is normalization taken one step too far.

---

]]>