x

How to remove cursor with other

Hi I have a proc(pasted as a query) which use cursor and is running very slow.I have to process 18000 records for one @Rp.Is there any wasy I can use a while loop instead. Can any one help Please?

Very Urgent...

Thanks, Hanoi

 declare 
 @RP         varchar(6)
 Set @Rp= '042012'
  
 Declare @Temp Table 
 ( 
 agenid          int, 
 ApplicationSequence int, 
 SubBeginDate        datetime, 
 SubEndDate          datetime, 
 AssistanceStart     datetime 
 ) 
 
 Declare 
 @applicationDate     datetime,    
 @assistanceStart     datetime, 
 @applicationSequence int, 
 @subBeginDate        datetime, 
 @subEndDate          datetime, 
 @oneLessSeq          int, 
 @agenid              int, 
 @PreSubEndDate       datetime, 
 @preSubBeginDate     datetime, 
 @compareBeginDate    datetime, 
 @subsidyBeginDate    datetime, 
 @PreAssistanceStart  datetime, 
 @compareEndDate      datetime 
 
 Insert into @Temp 
  ( 
     agenid, 
     ApplicationSequence, 
     SubBeginDate, 
     SubEndDate 
    ) 
  
 Select distinct 
         A.ApplicantgenId, 
         A.ApplicationSequence, 
         SubsidyBeginDate, 
         SubsidyEndDate 
        
 from  APP A 
 Inner Join Fam f on f.Uid = A.AGenid 
 where f.Rptperiod= @RP 
 and f.Uid = A.AGenID                       
 and A.ApplicationSequence = f.ApplicationSequence 
  
  
  --Select * from @Temp 
    
 DECLARE X CURSOR FOR 
 SELECT agenid, applicationsequence, SubBeginDate,SubEndDate 
 FROM @Temp 
 order by agenid 
  
 OPEN X 
  
 FETCH NEXT FROM X 
 INTO @agenid, @applicationsequence, @SubBeginDate,@SubEndDate 
  
 WHILE @@FETCH_STATUS = 0 
 BEGIN   
  
          set @assistanceStart = @SubBeginDate 
  
          set @oneLessSeq = @applicationSequence 
          
           if  (@applicationSequence = 1) 
                Begin 
                  Set   @assistanceStart = @SubBeginDate 
                End 
               else 
                 Begin 
               
                   select @compareBeginDate = @subBeginDate 
 
                      while (@oneLessSeq >1 ) 
                          Begin 
                         
                             set @oneLessSeq         = @oneLessSeq - 1 
                             set @preSubEndDate      = null 
                             set @preSubBeginDate    = null 
                             set @PreAssistanceStart = null 
                              
                              select @preSubBeginDate = SubsidyBeginDate,@preSubEndDate  = SubsidyEndDate,@PreAssistanceStart= SubsidyBeginDate  from  App A ,Fam f where A.AGenID = f.Uid  and A.applicationsequence = @oneLessSeq  and f.RptPeriod = @RP 
 and A.AGenID = @agenid 
  
                  if(@PreSubEndDate is not null and  datediff(day,@presubEndDate,@compareBeginDate)<= 90)---no gap 
                     Begin 
                                               
                     set @compareBeginDate = @presubBeginDate 
                     set @compareEndDate   = @presubEndDate 
                     set @AssistanceStart =  @PreAssistanceStart                     
                    End        
                   
                   else if(@PreSubEndDate is not null and datediff(day,@preSubEndDate ,@compareBeginDate)>90)--is there is a gap 
                       begin 
                             
                           Set @oneLessSeq = 1 
                          break; 
                        end 
                    else 
                         set @oneLessSeq = @oneLessSeq 
                        End 
  
                   End 
        Update @Temp 
        Set    AssistanceStart = @AssistanceStart   
          where agenid = @agenid 
       and applicationsequence = @applicationSequence 
  
 FETCH NEXT FROM X 
                 INTO @agenid, @applicationsequence, @SubBeginDate,@SubEndDate 
  
 END 
  
 CLOSE applicants_cursor 
 DEALLOCATE X 
  
 Select AGenid, 
        ApplicationSequence, 
        Convert(varchar,SubBeginDate) as SubsidyBeginDate, 
        Convert(varchar,SubEndDate) as   SubsidyEndDate, 
        Convert(varchar,AssistanceStart) as AssistanceStart 
 from @Temp 
more ▼

asked Aug 23, 2012 at 03:13 PM in Default

avatar image

hanoihanoi
10 5 5 8

Hi. I'm trying to follow what your stored procedure does, but it's a little late, too much code to immediately absorb and understand, plus some tasks are unnecessarily repeated.

Your question is how you can replace this query with a WHILE-loop, but I'd like to help out replacing with a single och a couple of queries and no looping. But to do that, I need to understand what you are trying to achieve.

Please describe in words what you are trying to do, and share your table structure, and I'll probably be able to help out, using a SET-based (which is what SQL Server is good at) rather than a RBAR approach (which is what SQL Server is less good at).

Aug 23, 2012 at 08:38 PM Magnus Ahlkvist

The code seems to be trying to identify the minimum SubsidyBeginDate for each AgenID and ApplicationSequence in a very round about and difficult way. That can certainly be done in a much faster and easier way than has been shown here.

@hanoihanoi - please provide us with the table structures, test data and expected output and we can get something put together quite quickly.

Aug 24, 2012 at 08:17 AM WilliamD

What I am doing is each Agenid can have many sequences. This proc will look at the sequences and go thru each sequence or that particular Agenid and if the diffence of subsidybegindate and subsidyenddate >90 it will take the corresponding Subsidybegindate. Eg: for agenid 100002 the answer = 02/01/2009

 AGEnID AppSeq SubsidyBeginDate SubsidyEndDate
  100002 1 12/10/2006 03/10/2007 100002 2 11/04/2007 05/03/2008 
  100002 3 05/04/2008 11/01/2008 100002 4 02/01/2009 05/30/2009 
  100002 5 05/31/2009 11/28/2009 100002 6 11/29/2009 05/29/2010 
  100002 7 05/30/2010 11/27/2010 100002 8 11/28/2010 05/28/2011 
  100002 9 05/29/2011 11/26/2011 100002 10 11/27/2011 05/26/2012 
  100002 11 05/27/2012 11/24/2012
 

Let me know if it is understandabale. Thanks a bunch guys for replying...

Hanoi

Aug 24, 2012 at 01:31 PM hanoihanoi
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069
x457
x68

asked: Aug 23, 2012 at 03:13 PM

Seen: 1101 times

Last Updated: Aug 24, 2012 at 04:10 PM

Copyright 2016 Redgate Software. Privacy Policy