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

hanoihanoi gravatar image

hanoihanoi
0 4 5 6

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x984
x406
x58

asked: Aug 23, 2012 at 03:13 PM

Seen: 824 times

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