• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Removing Cursor

I'm trying to remove this cursor and not sure how to go about it.  I had another cursor that was running for 45 minutes that after removed it only ran for about 1 minute.  I have found this one below that is not efficient and would like help with recreating it, just not sure where to start.  I'm working on SQL Server 2000
declare @NewDebtorID int
declare @DebtorID int
declare @OrgID int
declare @ServiceID int
declare @AccountNo varchar(20)
declare @InvoiceNo varchar(20)
declare @BalanceDue money
declare @ServiceAmount money
declare @ServiceDate datetime
declare @ServiceCount int
declare @StatusID int
declare @SeriesID int
declare @LastLetterID int
declare @StatusDate datetime
declare @EventDesc varchar(255)
 
declare c1 cursor for
--select service records that need to be moved:
select DS.ServiceID, D.DebtorID, D.AccountNo, DS.InvoiceNo, D.OrgID, DS.BalanceDue, DS.ServiceAmount, DS.ServiceDate, D.StatusID, D.StatusDate, D.SeriesID, D.LastLetterID
from DebtorInvoice DI
inner join DebtorService DS on DS.ServiceID = DI.ServiceID
inner join Debtor D on D.DebtorID = DS.DebtorID 
inner join Organization O on D.OrgID = O.OrgID
inner join Account A on O.AccountID = A.AccountID
inner join AccountSeries Ase on A.AccountID = ASe.AccountID
inner join AccountSeriesLetter ASL on ASe.AccountSeriesID = ASL.AccountSeriesID and SeriesLetterID = 221 --Invoice Letter
where D.StatusID = 19 --Invoice Status
--07/21/2008 - Nathan Riley - Added CreateDate from the DebtorService table to more accuratly have an InvoiceSentDate.
and dateadd(d,ASL.DelayDays,DS.CreateDate) < getdate()
--and dateadd(d,ASL.DelayDays,OriginalInvoiceDate) < getdate()
and BalanceDue > 0
order by D.DebtorID
 
open c1
 
fetch next from c1 into @ServiceID, @DebtorID, @AccountNo, @InvoiceNo, @OrgID, @BalanceDue, @ServiceAmount, @ServiceDate, @StatusID, @StatusDate, @SeriesID, @LastLetterID
 
while @@Fetch_Status = 0
begin
	
	select @NewDebtorID = isnull(DebtorID,0)
	from Debtor 
	where AccountNo = @AccountNo
	and OrgID = @OrgID
	and StatusID in (1,2,6,9) --New, Suspended, Bad Address, Letter Series
 
	if @@rowcount = 0 
	begin
		exec @NewDebtorID = p_copy_debtor_process @DebtorID
	end
	
	--move the service
	update DebtorService set DebtorID = @NewDebtorID
	where ServiceID = @ServiceID
 
	--move the service level events (Payment Received, Invoice Sent)
	update DebtorEvent set DebtorID = @NewDebtorID
	where DebtorID = @DebtorID 
	and EventDesc like '%' + @InvoiceNo + '%'
	and TypeID in (23,8)
 
	--delete the service level events (Debt Recorded, Debt Updated)
	delete DebtorEvent 
	where DebtorID = @DebtorID 
	and EventDesc like '%' + @InvoiceNo + '%'
	and TypeID in (9,13)
 
	--update the new debtor summary record
	update DebtorSummary set 	OriginalAmount = OriginalAmount + @ServiceAmount,
					AmountDue = AmountDue + @BalanceDue
	where DebtorID = @NewDebtorID
	and DebtorSummaryTypeID = 1
 
	--Update the old debtor summary record
	update DebtorSummary set 	OriginalAmount = OriginalAmount - @ServiceAmount,
					AmountDue = AmountDue - @BalanceDue
	where DebtorID = @DebtorID
	and DebtorSummaryTypeID = 1
	
	set @EventDesc = 'Invoice Number: ' + isnull(@InvoiceNo,' ') + '; Date of Service: ' + isnull(convert(varchar,@ServiceDate,101),' ') + '; Balance Due: ' +  isnull(cast(@BalanceDue as varchar),'0.00')
	-- Log debt added event
	INSERT	DebtorEvent (DebtorID, TypeID, StatusID, StatusDate, EventUserID, EventDate, SeriesID, LastLetterID, EventDesc)
	VALUES	(@NewDebtorID, 24, @StatusID, @StatusDate, 1, GETDATE(), @SeriesID, @LastLetterID, @EventDesc)
 
	--remove the old debtor if there are no more service records
	select @ServiceCount = count(*) 
	from DebtorService 
	where DebtorID = @DebtorID
	if @ServiceCount = 0 
	begin
		exec P_UTIL_DELETE_DEBTORALL @DebtorID
	end
 
	fetch next from c1 into @ServiceID, @DebtorID, @AccountNo, @InvoiceNo, @OrgID, @BalanceDue, @ServiceAmount, @ServiceDate, @StatusID, @StatusDate, @SeriesID, @LastLetterID
end
 
close c1
deallocate c1

Open in new window

0
Nathan Riley
Asked:
Nathan Riley
  • 5
  • 3
1 Solution
 
Nathan RileyFounder/CTOAuthor Commented:
Maybe insert into a temporary table?
0
 
reb73Commented:
You'll need to post the code for the following two procedures invoked within the body of the cursor to determine if there is a non-cursor solution -

p_copy_debtor_process
P_UTIL_DELETE_DEBTORALL

Can you also confirm if your SQL Server version is SQL2000? (appears so from the tags!)
0
 
Nathan RileyFounder/CTOAuthor Commented:
p_copy_debtor_process
(
	@OldDebtorID int
)
as 
set nocount on
declare @NewDebtorID int
declare @NewAddressID int
declare @OldAddressID int
declare @return int
begin tran
select @OldAddressID = AddressID from DebtorAddress where DebtorID = @OldDebtorID
exec @NewDebtorID = P_Copy_Debtor @OldDebtorID
if @NewDebtorID < 0 
begin
	rollback tran
	goto error
end
exec @NewAddressID = p_Copy_Address @OldAddressID
if @NewAddressID < 0 
begin
	rollback tran
	goto error
end
exec @return = p_Copy_DebtorAddress @OldDebtorID, @NewDebtorID, @OldAddressID, @NewAddressID
if @return < 0 
begin
	rollback tran
	goto error
end
exec @return = p_Copy_DebtorDetail @OldDebtorID, @NewDebtorID
if @return < 0 
begin
	rollback tran
	goto error
end
exec @return = p_Copy_DebtorMedical @OldDebtorID, @NewDebtorID
if @return < 0 
begin
	rollback tran
	goto error
end
exec @return = p_Copy_DebtorSummary @OldDebtorID, @NewDebtorID, 1 --Do zero the balances
if @return < 0 
begin
	rollback tran
	goto error
end
insert into DebtorEvent
select 	@NewDebtorID,
	TypeID,
	StatusID,
	StatusDate,
	SeriesID,
	LastLetterID,
	EventDate,
	EventUserID,
	EventDesc
from DebtorEvent
where DebtorID = @OldDebtorID
and TypeID in (6,12,5,20,17,4,16,10,22)
if @@error <> 0 
begin
	rollback tran
	goto error
end
exec P_UPD_DEBTOR2 @DebtorID = 	@NewDebtorID, @StatusID	= 1, @UserID = 1, @CancelReasonID = null, @CancelReasonComment = null, @SuspendReasonComment = null
if @@error <> 0 
begin
	rollback tran
	goto error
end
commit tran
return @NewDebtorID
error:
	return -1
set nocount off
GO

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nathan RileyFounder/CTOAuthor Commented:
Oh yes, SQL Server 2000

P_UTIL_DELETE_DEBTORALL
(
	@DebtorID int
)
AS
SET NOCOUNT ON
--delete DebtorEvent
delete DebtorEvent where DebtorID = @DebtorID
--find Addresses
DECLARE @Address table
(
	AddressID	int	NOT NULL 
)
insert into @Address select AddressID from DebtorAddress where DebtorID = @DebtorID
--delete DebtorAddress
delete DebtorAddress where DebtorID = @DebtorID
--delete Address
delete from Address where AddressID in (select AddressId from @Address)
--delete DebtorMedical
delete DebtorMedical where DebtorID = @DebtorID
--delete DebtorSummary
delete DebtorSummary where DebtorID = @DebtorID
--delete DebtorDetail
delete DebtorDetail where DebtorID = @DebtorID
--delete DebtorNote
delete DebtorNote where DebtorID = @DebtorID
--delete DebtorPayment
delete DebtorPayment where ServiceID in (select ServiceID from DebtorService where DebtorID = @DebtorID)
 
--delete DebtorInvoiceHistory
delete DebtorInvoiceHistory where DebtorInvoiceID in 
	(select DebtorInvoiceID from DebtorInvoice where ServiceID in 
	(select ServiceID from DebtorService where DebtorID = @DebtorID))
 
--delete DebtorInvoiceDetail
delete DebtorInvoiceDetail where DebtorInvoiceID in 
	(select DebtorInvoiceID from DebtorInvoice where ServiceID in 
	(select ServiceID from DebtorService where DebtorID = @DebtorID))
 
--delete DebtorInvoice
delete DebtorInvoice where ServiceID in 
	(select ServiceID from DebtorService where DebtorID = @DebtorID)
 
--delete DebtorService
delete DebtorService where DebtorID = @DebtorID
--delete LetterHistory
delete LetterHistory where DebtorID = @DebtorID
--delete CollCommunication
delete CollCommunication where DebtorID = @DebtorID
--delete CollectionPayment
delete CollectionPayment where DebtorID = @DebtorID
--delete SentToPhoneCollect
delete SentToPhoneCollect where DebtorID = @DebtorID
--delete SentToAttorney
delete SentToAttorney where DebtorID = @DebtorID
--delete SentTo2ndCollect
delete SentTo2ndCollect where DebtorID = @DebtorID
--delete DebtorArchived
delete DebtorArchived where DebtorID = @DebtorID
--delete DebtorAddressUpdate
delete DebtorAddressUpdate where DebtorID = @DebtorID
--delete Debtor
delete Debtor where DebtorID = @DebtorID
SET NOCOUNT OFF
RETURN
GO

Open in new window

0
 
reb73Commented:
The procedure p_copy_debtor_process is invoking a host of other procedures, which are probably simple insert statements, but will, nevertheless,  make it a time-consuming process to review and optimize..
0
 
Nathan RileyFounder/CTOAuthor Commented:
So I should start with working on it first then work my way back to the first one b/c that is where a lot of time is spent?
0
 
reb73Commented:
The reason the code is being called in a cursor, is the the work seems to be unitized by a single debtor id.

If you can review and optimize the code in the different procedures called by the procedure p_copy_debtor_process, then and see if it can be handled using set-based queries , then the trigger can be substituted with code using temporary tables and made to run much, much quicker..

0
 
Nathan RileyFounder/CTOAuthor Commented:
Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now