SQLServerCentral.com / Performance Tuning / SQL Server 7,2000 / Need help removing cursors from query / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:00:28 GMT20Need help removing cursors from queryhttp://www.sqlservercentral.com/Forums/Topic1489613-65-1.aspxHi, 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 intDECLARE @R intDECLARE @Q intDECLARE @CT CHAR(2) -- QFE: neilb: 2007-09-03DECLARE @NCAT intSELECT @R=0DECLARE @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 SNUMBERFROM MailOrderManager..cmsINNER JOIN MailOrderManager..ITEMS ON MailOrderManager..CMS.ORDERNO = MailOrderManager..ITEMS.ORDERNOINNER JOIN MailOrderManager..STOCK ON MailOrderManager..ITEMS.ITEM = MailOrderManager..STOCK.NUMBERINNER JOIN MailOrderManager..ST_EXTRA ON MailOrderManager..STOCK.NUMBER = MailOrderManager..ST_EXTRA.NUMBERINNER JOIN MailOrderManager..CUST ON MailOrderManager..CMS.CUSTNUM = MailOrderManager..CUST.CUSTNUMWHERE 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 varientsDECLARE C1 CURSOR FOR SELECT highercatalogid,sum(Qty),cType FROM @Sales WHERE HigherCatalogid != 0 GROUP BY HigherCatalogid,cType order by highercatalogidOPEN C1FETCH NEXT FROM C1 INTO @C,@Q,@CTWHILE @@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 ENDCLOSE C1DEALLOCATE 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 rankDECLARE C1 CURSOR FOR SELECT DISTINCT catalogid,MastQty,cType FROM @Sales WHERE HigherCatalogid = 0 ORDER BY cType, MastQty DESCOPEN C1FETCH NEXT FROM C1 INTO @C,@Q,@CTWHILE @@FETCH_STATUS = 0 BEGIN -- Organise the ranking based on ctype. IF @LCT &lt;&gt; @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 C1DEALLOCATE C1Thu, 29 Aug 2013 03:00:17 GMTj.snowden