August 29, 2013 at 3:00 am
Hi,
I have a query that is performing like a dog (written way back before my time) I've identified that this query is running for 9+ hrs every night. I've checked the query itself and found that it has 4 inline cursors one after the other. As well as other performance killers like functions in the where clause.
I have decided to remove the cursors one at a time, i've managed to get rind of the first but i'm having trouble with the second. Its incrementing a rank value in a column but i cant seem to figure out exactly how to replicate what its doing using set based logic.
Below is an excerpt of the query with the first two cursors in (the first cursor is commented out and my replacement code for that one directly follows it) any help/examples/suggestions with how i can get rid of that second cursor would be much appreciated. thanks.
DECLARE @Cint
DECLARE@rint
DECLARE @Qint
DECLARE @CTCHAR(2)-- QFE: neilb: 2007-09-03
DECLARE @NCATint
SELECT @r=0
DECLARE @SALES TABLE
(
SNUMBERchar(20),
catalogidint,
categoryidint,
highercatalogidint,
ctypechar(1),
qtyint,
rankint,
MastQtyint,
MastRankint,
[2WeekSales]int,
[Mast2WeekSales]int
)
/* Query the sales in the last 0 to 30 days*/
INSERT INTO @SALES
(catalogid,highercatalogid,ctype,qty,SNUMBER)
SELECT
cast(STOCK_ID as int) AS catalogid,
cast(colHIGHERCATALO as int) AS Highercatalogid,
cType,
sum(cast(QUANTO as int) *
CASE
WHEN MailOrderManager..CMS.odr_date BETWEEN getdate()-30 AND getdate() THEN 2
WHEN MailOrderManager..CMS.odr_date BETWEEN getdate()-60 AND getdate()-31 THEN 1
--WHEN MailOrderManager..CMS.odr_date BETWEEN getdate()-90 AND getdate()-60THEN 1
END) /3 AS Qty,
MailOrderManager..STOCK.NUMBER AS SNUMBER
FROM
MailOrderManager..cms
INNER JOIN MailOrderManager..ITEMS ON MailOrderManager..CMS.ORDERNO = MailOrderManager..ITEMS.ORDERNO
INNER JOIN MailOrderManager..STOCK ON MailOrderManager..ITEMS.ITEM = MailOrderManager..STOCK.NUMBER
INNER JOIN MailOrderManager..ST_EXTRA ON MailOrderManager..STOCK.NUMBER = MailOrderManager..ST_EXTRA.NUMBER
INNER JOIN MailOrderManager..CUST ON MailOrderManager..CMS.CUSTNUM = MailOrderManager..CUST.CUSTNUM
WHERE
order_st2='SH'
AND
MailOrderManager..CMS.odr_date BETWEEN getdate()-90 AND getdate()
GROUP BY
STOCK_ID,
colHIGHERCATALO,
cType,
MailOrderManager..STOCK.NUMBER
/*
-- Reset the Qty sold for items with varients to include the varients
DECLARE C1 CURSOR FOR
SELECT highercatalogid,sum(Qty),cType FROM @Sales WHERE HigherCatalogid != 0 GROUP BY HigherCatalogid,cType order by highercatalogid
OPEN C1
FETCH NEXT FROM C1 INTO @C,@Q,@CT
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @SALES SET
MastQty = IsNull(Qty + @Q,0)
WHERE
catalogid=@C
AND
cType = @CT-- QFE: neilb: 2007-09-03
FETCH NEXT FROM C1 INTO @C,@Q,@CT
END
CLOSE C1
DEALLOCATE C1
*/
-- Reset the Qty sold for items with varients to include the varients without cursor
SELECT highercatalogid,sum(Qty) AS NewQTY,cType INTO #temp FROM @Sales WHERE HigherCatalogid != 0 GROUP BY HigherCatalogid,cType order by highercatalogid
UPDATE @SALES
SET MastQty = IsNull(qty + t.NewQTY,0)
FROM @SALES AS s JOIN #temp t
on catalogid = t.highercatalogid
AND s.ctype = t.ctype
SELECT * FROM @SALES
DECLARE @LCT CHAR(2)
SELECT @LCT = ''
-- Loop through and organise the rank
DECLARE C1 CURSOR FOR
SELECT DISTINCT catalogid,MastQty,cType FROM @Sales WHERE HigherCatalogid = 0 ORDER BY cType, MastQty DESC
OPEN C1
FETCH NEXT FROM C1 INTO @C,@Q,@CT
WHILE @@FETCH_STATUS = 0
BEGIN
-- Organise the ranking based on ctype.
BEGIN
SELECT @r = 0
END
-- Increment the Rank
SELECT @r=@R + 1
UPDATE @SALES SET
Rank=IsNull(@R,0)
WHERE
catalogid=@C
AND
cType = @CT-- QFE: neilb: 2007-09-03
/* QFE: neilb: 2007-09-03 */
UPDATE @SALES SET
Rank = IsNull(@R,0)
WHERE
Highercatalogid=@c
AND
cType = @CT
-- Increment the @LCT variable
FETCH NEXT FROM C1 INTO @C,@Q,@CT
END[highlight=#ffff11][/highlight]
CLOSE C1
DEALLOCATE C1
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply