question

hanoihanoi avatar image
hanoihanoi asked

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
t-sqlstored-procedurescursor
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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).
2 Likes 2 ·
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.
0 Likes 0 ·
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
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.