Eliminate CURSOR!!! SLOW

[jadams0173]

using sql 2000

I've been working through a report and managed to get to where I need. Only problem is I used a cursor! It is VERY slow. Anyone willing to try to help me get rid of the cursor.

Here is the query.

[COLOR=blue]Declare[/color] @Parent [COLOR=blue]Varchar[/color](25),@ChildPer [COLOR=blue]int[/color],@HLA_REQ [COLOR=blue]int[/color],@LLANeeded [COLOR=blue]int[/color],@ChildPN [COLOR=blue]Varchar[/color](25)

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]

[COLOR=#FF00FF]truncate[/color] [COLOR=blue]table[/color] ChildPN

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] ChildPN (CHILD_PN,REL_PLAN,ATR,WIP,FLEX_SFG,SUM_OPICKS)
[COLOR=blue]SELECT[/color]  [COLOR=#FF00FF]DISTINCT[/color]  A.ITEM, A.REL_PLAN,A.ATR ,A.WIP ,A.FLEX_SFG ,B.SUM_OP
[COLOR=blue]FROM[/color] V_Parent_Child_No_Ind_demand_JH A
 [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=green]--WE DO THIS BECAUSE A LLA IS USED ON MULTIPLE HLA'S.
[/color]			[COLOR=green]--WE WANT TO SUM THE OPEN PICKS FOR EACH LLA
[/color]			[COLOR=blue]SELECT[/color]  [COLOR=#FF00FF]DISTINCT[/color] ITEM, SUM(OPENPICKS) "SUM_OP"
			[COLOR=blue]FROM[/color] V_Parent_Child_No_Ind_demand_JH
			[COLOR=blue]WHERE[/color] RELATIONSHIP = [COLOR=red]'CHILD'[/color] [COLOR=green]--AND ITEM = '1070012204407C'
[/color]			[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] ITEM
		  ) B
[COLOR=blue]ON[/color] A.ITEM = B.ITEM

[COLOR=green]--UPDATE LLA_AVAIL
[/color][COLOR=blue]UPDATE[/color] TV
[COLOR=blue]SET[/color] TV.LLA_AVAIL = A.LLA_AVAIL
[COLOR=blue]FROM[/color] ChildPN TV
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
		(
			[COLOR=blue]SELECT[/color]  CHILD_PN,  sum(B.REL_PLAN + B.ATR + B.WIP + B.FLEX_SFG - B.SUM_OPICKS) "LLA_AVAIL"
			[COLOR=blue]FROM[/color] ChildPN B
			[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] B.CHILD_PN
		) A
[COLOR=blue]ON[/color] TV.CHILD_PN = A.CHILD_PN

[COLOR=green]/*	
[/color][COLOR=green]Cursor here
[/color][COLOR=green]*/[/color]
[COLOR=blue]declare[/color] c1 [COLOR=blue]cursor[/color] FAST_FORWARD [COLOR=blue]for[/color]

	[COLOR=blue]Select[/color] TOP 15 Child_PN [COLOR=blue]FROM[/color] ChildPN

[COLOR=blue]FOR[/color] [COLOR=#FF00FF]READ[/color] [COLOR=blue]ONLY[/color]
[COLOR=#FF00FF]open[/color] c1
[COLOR=blue]while[/color] (1=1)
[COLOR=blue]begin[/color]

	[COLOR=blue]fetch[/color] [COLOR=blue]next[/color] [COLOR=blue]from[/color] c1 [COLOR=blue]into[/color] @ChildPN

	[COLOR=blue]if[/color](@@FETCH_STATUS <> 0)
		[COLOR=blue]break[/color]

[COLOR=blue]SELECT[/color]  @Parent = A.PARENT , @HLA_REQ = SUM(A.REL_PLAN + A.ATR + A.WIP) 
[COLOR=blue]FROM[/color] V_Parent_Child_No_Ind_demand_JH A
	[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] (
				[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]distinct[/color] PARENT,quantity
				[COLOR=blue]FROM[/color] V_Parent_Child_No_Ind_demand_JH
				[COLOR=blue]WHERE[/color] item = @ChildPN[COLOR=green]--'05114500002B' --put child here to get parent demand
[/color]			 ) B
[COLOR=blue]ON[/color] A.ITEM = B.parent
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] A.PARENT
[COLOR=green]--ORDER BY A.PARENT
[/color]
[COLOR=green]--hopefully this will get us the parent, child and child quantity. Multiply the child qty
[/color][COLOR=green]--times the @HLA_REQ. This is the number of childern needed by LLA to build the demand.
[/color][COLOR=blue]Select[/color] @LLANeeded = (@HLA_REQ * Quantity)
[COLOR=blue]from[/color] V_Parent_Child_No_Ind_demand_JH
[COLOR=blue]Where[/color] Parent = @Parent and Item = @ChildPN[COLOR=green]--'05114500002B'
[/color]
[COLOR=green]/*
[/color][COLOR=green]now update the demand column in ChildPN. Add the current value to @LLANeeded 
[/color][COLOR=green]*/[/color]

[COLOR=blue]Update[/color] ChildPN
[COLOR=blue]Set[/color] LLA_DEMAND = [COLOR=#FF00FF]Coalesce[/color](LLA_DEMAND,0) + @LLANeeded
[COLOR=blue]WHERE[/color] CHILD_PN = @CHILDPN

[COLOR=blue]end[/color]
[COLOR=blue]close[/color] c1
[COLOR=blue]deallocate[/color] c1

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]off[/color]

If sample data is needed I'll post some. ANY advice is welcomed!

 

[TheBugSlayer]

Hi, your query to replace the cursor should look something like this:

SELECT   A.PARENT , A.ITEM, SUM(A.REL_PLAN + A.ATR + A.WIP)*B.Quantity AS LLANeeded
   INTO #YourTempTable
   FROM V_Parent_Child_No_Ind_demand_JH A    
   INNER JOIN (                
      SELECT distinct PARENT,quantity                
      FROM V_Parent_Child_No_Ind_demand_JH V 
      JOIN ChildPN C ON C.Child_PN = V.item             
   ) B ON A.ITEM = B.parent GROUP BY A.PARENT
   
   UPDATE ChildPN C
   INNER JOIN #YourTempTable T ON C.Parent = T.Parent AND C.Item = T.Item
   SET C. LLA_DEMAND =  Coalesce(LLA_DEMAND,0) + T.LLANeeded

Disclaimer: the code was not tested, it' s only intended to give you a general idea.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[jadams0173]

Thanks TheBugSlayer. I'll give it a shot.