Solved

Need to replace a SQL Cursor

Posted on 2013-01-22
6
337 Views
Last Modified: 2013-01-23
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
Comment
Question by:krapvag
  • 3
  • 2
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38805482
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
 
LVL 1

Author Closing Comment

by:krapvag
ID: 38805954
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38805972
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:krapvag
ID: 38807136
thanks a lot scott, I will put that in place tomorrow, sorry I can't give you any points
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38807714
No problem.

Just let me know if it makes any difference.
0
 
LVL 1

Author Comment

by:krapvag
ID: 38810373
well it takes approx 14 minutes to run now instead of 16.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now