Solved

Need to replace a SQL Cursor

Posted on 2013-01-22
6
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 143

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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