Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help removing cursors from query Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:28 AM
Points: 4, Visits: 171
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

Post #1489613
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse