• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Need to replace a SQL Cursor

Hi,

I have come across a stored procedure that analyses and summarises existing data into a table ready for an export to a SQL Cube.

The problem is, it's all being slowed down by a cursor, but I don't really know how I can alter this to not use the cursor.

What it does is creates a temporary table that lists all of the ID's that need to be summarized.

A cursor is created from this temporary table that contains all of the references for each ID (an ID can have several references)

The reference has an amount, and the cursor will check the previous amount, and increment an Increase flag if the amount has increased, and increment a Decrease flag if it is lesser.

So the ID has amount of Increases and decreases saved to it, depending on the history of its references.

I can't really see a way in which the cursor can be written out, due to the task it has to perform, but I am not that experienced in SQL Server and was hoping someone on here who knows their stuff can tell me where I'm going wrong.  I've attached the section of the stored procedure that needs to be re-written

Thanks
cursor.txt
0
krapvag
Asked:
krapvag
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is the "non-" cursor way to implement this:
DECLARE @Base as table
(
	  UniqueRef            int    identity(1,1),
	  ID   int,
	  Reference           varchar(15)
)


insert INTO @Base
SELECT [@tempSummary].ID, 
 [@tempSummary].Ref
where [@tempSummary].CountOfRecords > 1       

SELECT top 1 @ID = id, @Reference = reference  
  from @base

WHILE @@ROWCOUNT > 0
BEGIN
  SELECT top 1 @ID = id, @Reference = reference  
    from @base
 
  IF @@ROWCOUNT > 0
  BEGIN
	declare @BaseReferences as table
	 (	 ID                   int identity(1,1),		
		 StartDate     datetime,
		 Amount        decimal(18,2),
		 REF varchar(8),
		 RN   int
	 )

	insert into @BaseReferences
		 select distinct RG.StartDate, RG.Amount,@Reference 
		 ,ROW_NUMBER() OVER (PARTITION BY left(RG.RReference, 8) 
			 ORDER BY RG.StartDate) RN
		 from RG
		 where left(RG.Reference,8) = @Reference
		 order by RG.StartDate

	select @RowCount =1, @MaxRowCount=SUM(N) 
	from 
	(
	SELECT	
		 ISNULL(count(1),0) as N    
	FROM 
	@baseReferences
	)d
	
	
	declare 
	--@rc   int,
	--@mrc       int,
	@amount    decimal(18,2)= 0.0,
	@oldamount           decimal (18,2),
	@noofincreases       int =0 ,
	@noofdecrease		int =0             

	while ( @RowCount <=  @MaxRowCount)
	begin  
		set @amount = ISNULL( (select Amount from @BaseReferences
						   where  [@BaseReferences].RN = @RowCount
						   ),0)
			set @oldamount = isnull((select Amount from @BaseReferences
							  where  [@BaseReferences].RN = @RowCount - 1				      
							  ),0)			   
		   
			if @amount > @oldamount AND @RowCount>1
				set @noofincreases += 1
			else if @amount< @oldamount AND @RowCount >1
				set @noofdecrease +=1
		 
		
		 set @RowCount = @RowCount+ 1

		 set @noofincreases=0
		 set @noofdecrease=0
		 delete from @BaseReferences
	end
	
	if (@noofincreases > 0)
		-- update field in @Base
	
	if (@noofdecreases > 0)
		-- update field in @Base
	
  DELETE  @base
   WHERE  @ID = id
      AND  @Reference = reference  
   END -- if @@rowcount > 0
END -- while @@rowcount > 0

Open in new window

0
 
krapvagAuthor Commented:
I have got this working, and it's cut down the execution time by over an hour!  If I could give you more than 500 points I would
0
 
Scott PletcherSenior DBACommented:
You should (also) key the table variables properly to speed up processing later:


DECLARE @Base as table
(

        UniqueRef            int    identity(1,1) primary key,

        ID   int,

        Reference           varchar(15)

)


insert INTO @Base
SELECT [@tempSummary].ID,
 [@tempSummary].Ref
where [@tempSummary].CountOfRecords > 1      

DECLARE _cursor CURSOR
FOR SELECT  UniqueRef,ID FROM @Base
OPEN _cursor;
FETCH NEXT FROM _cursor
INTO  @ID,@Reference

WHILE @@FETCH_STATUS = 0
BEGIN

      declare @BaseReferences as table
       (       ID                   int identity(1,1) primary key,            
             StartDate     datetime,
             Amount        decimal(18,2),
             REF varchar(8),
             RN   int
       )

      insert into @BaseReferences
             select distinct RG.StartDate, RG.Amount,@Reference
             ,ROW_NUMBER() OVER (PARTITION BY left(RG.RReference, 8)
                   ORDER BY RG.StartDate) RN
             from RG
             where RG.Reference like @Reference + '%'
             order by RG.StartDate

      select @RowCount =1, @MaxRowCount=SUM(N)
      from
      (
      SELECT      
             ISNULL(count(1),0) as N    
      FROM
      @baseReferences
      )d
      
      
      declare
      --@rc   int,
      --@mrc       int,
      @amount    decimal(18,2)= 0.0,
      @oldamount           decimal (18,2),
      @noofincreases       int =0 ,
      @noofdecrease            int =0            

      while ( @RowCount <=  @MaxRowCount)
      begin  
            set @amount = ISNULL( (select Amount from @BaseReferences
                                       where  [@BaseReferences].RN = @RowCount
                                       ),0)
                  set @oldamount = isnull((select Amount from @BaseReferences
                                            where  [@BaseReferences].RN = @RowCount - 1                              
                                            ),0)                     
               
                  if @amount > @oldamount AND @RowCount>1
                        set @noofincreases += 1
                  else if @amount< @oldamount AND @RowCount >1
                        set @noofdecrease +=1
             
            
             set @RowCount = @RowCount+ 1

             set @noofincreases=0
             set @noofdecrease=0
             delete from @BaseReferences
      end
      
      if (@noofincreases > 0)
            -- update field in @Base
      
      if (@noofdecreases > 0)
            -- update field in @Base
      
FETCH NEXT FROM _cursor INTO @ID,@BaseReferencesReference;


END
CLOSE _cursor;
DEALLOCATE _cursor;
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
krapvagAuthor Commented:
thanks a lot scott, I will put that in place tomorrow, sorry I can't give you any points
0
 
Scott PletcherSenior DBACommented:
No problem.

Just let me know if it makes any difference.
0
 
krapvagAuthor Commented:
well it takes approx 14 minutes to run now instead of 16.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now