Click here to monitor SSC
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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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