Solved

Need to replace a SQL Cursor

Posted on 2013-01-22
6
349 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

628 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