SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help removing cursors from query


Need help removing cursors from query

Author
Message
j.snowden
j.snowden
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 362
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 @C int
DECLARE @R int
DECLARE @Q int
DECLARE @CT CHAR(2) -- QFE: neilb: 2007-09-03
DECLARE @NCAT int
SELECT @R=0
DECLARE @SALES TABLE
(
SNUMBER char(20),
catalogid int,
categoryid int,
highercatalogid int,
ctype char(1),
qty int,
rank int,
MastQty int,
MastRank int,
[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()-60 THEN 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.
IF @LCT <> @CT
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
SELECT @LCT = @CT

FETCH NEXT FROM C1 INTO @C,@Q,@CT
END[highlight=#ffff11][/highlight]
CLOSE C1
DEALLOCATE C1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search