Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to replace a SQL Cursor

Posted on 2013-01-22
6
Medium Priority
?
354 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 2000 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 70

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 70

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

670 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