Eliminate cursor with while loop inside it
-
Good morning,
I am trying to migrate transactions (charges/payments) from a old application to a new one.
The new application works as follows:
A table named transaction that holds
-the charges for a contract.
- the amount of a payment applied to a chargeA table named payment that holds the full payment amount.
Now since we need to preserve historical data I can't just go and apply the payments to the charges in a fifo order. If someone for example didn't pay a late fee back in 2010 I can't all of a sudden have my query pay this late fee.
Therefore I created my own table named 'PreviouslyPaid' that tells me what amount on what charge is paid, but the payments in the old application are not strongly linked to a specific charge (Terrible, which is one of the reasons we're switching applications).
I am working on a query that will pay off the charges that I tell it to pay off, however if a payment does not cover a charge than the charge will be paid by 2 different payments.
Here is a example of some charges
Here are some payments, note that they each are for a specific contract.
This is how much I want to pay for each transaction, note that I don't have a paymentid here (that information is unknown)This is the final result.
See how charge 1 is paid by payment 1 and 2 (since payment 1 was only $40 and it did not cover the $100)
Then when I go and pay $20 towards charge 2 it uses payment 2 since there is still money left
When I go and pay charge 3 it uses payment 4 because that is the first one that is available for that contract.Below is the query that I wrote to get this result:
CREATE TABLE #Transaction
(TransactionID INT IDENTITY(1,1),
BatchID INT,
TransactionNo INT,
OwnerID INT,
ContractID INT,
TransactionType VARCHAR(3), -- CHG = Charge, PMT = Payment
TransactionDate DATE,
ApplyID INT,
PaymentID INT,
Amount DECIMAL(16,2)
)CREATE TABLE #Payment
(PaymentID INT IDENTITY(1,1),
PaymentAmount DECIMAL(16,2),
ContractID INT
)CREATE TABLE #PreviouslyPaid
(
TransactionID INT,
AmountPaid DECIMAL(16,2),
ContractID INT
)INSERT INTO #Transaction
(BatchID,
TransactionNo,
OwnerID,
ContractID,
TransactionType,
TransactionDate,
ApplyID,
PaymentID,
Amount)
VALUES
(1,1,344557,854123,'CHG','2017-01-01',1,NULL,100), -- add charge of $100 for contract 854123
(1,2,344557,854123,'CHG','2017-01-02',2,NULL,200), -- add charge of $200 for contract 854123
(1,3,443243,534227,'CHG','2017-01-01',3,NULL,700), -- add charge of $700 for contract 534227
(1,4,443243,534227,'CHG','2017-01-02',4,NULL,300), -- add charge of $300 for contract 534227
(1,1,344557,854123,'CHG','2017-01-04',5,NULL,10) -- add charge of $10 for contract 854123INSERT INTO #Payment
(PaymentAmount, ContractID)
VALUES
(-40,854123),
(-90,854123),
(-30,854123), -- total payments for contract 854123 = $160
(-750,534227),
(-200,534227),
(-50,534227) -- total payments for contract 534227 = 900--Determine that there are 2 charges already paid, at this point we don't know which payments apply to those but we do know
-- that these are the charges that we have to satisfy.
INSERT INTO #PreviouslyPaid
(TransactionID,AmountPaid,ContractID)
VALUES
(1,100,854123), -- charge of $100 fully paid
(2,20,854123), -- charge of $200 not fully paid, only $20 paid
(3,700,534227) -- charge of $700 fully paid-- this is the cursor I would like to get rid of
DECLARE @CursorTransactionID INT
DECLARE @CursorAmountToPay DECIMAL(16,2)
DECLARE @CursorContractID INT
DECLARE @CursorRemainingBalance DECIMAL(16,2)DECLARE ApplyAlreadyPaidChargesCursor CURSOR FOR
SELECT
TransactionID,
AmountPaid,
ContractID
FROM #PreviouslyPaid
OPEN ApplyAlreadyPaidChargesCursorFETCH NEXT FROM ApplyAlreadyPaidChargesCursor INTO
@CursorTransactionID,
@CursorAmountToPay,
@CursorContractID
WHILE @@FETCH_STATUS = 0 BEGIN
--Cusor logicSET @CursorRemainingBalance = @CursorAmountToPay
WHILE
-- The charge has not yet been fully paid
(SELECT ISNULL(SUM(ABS(Amount)),0) FROM #Transaction WHERE ApplyID = @CursorTransactionID
AND TransactionType = 'PMT') < ABS(@CursorAmountToPay)
AND
-- and the amount of money we have availalbe for this contract is lager than the amount we already spend for this contract.
(SELECT SUM(ABS(PaymentAmount)) FROM #Payment WHERE ContractID = @CursorContractID)
>
(SELECT ISNULL(SUM(ABS(Amount)),0) FROM #Transaction
WHERE ContractID = @CursorContractID
AND TransactionType = 'PMT')
BEGIN-- Insert payment transaction
INSERT INTO #Transaction
(BatchID,
TransactionNo,
OwnerID,
ContractID,
TransactionType,
TransactionDate,
ApplyID,
PaymentID,
Amount)
SELECT
1,
(SELECT ISNULL(MAX(TransactionNo),0)+1 FROM #Transaction), -- transaction no increases by 1 for every row.
(SELECT TOP 1 OwnerID FROM #Transaction WHERE ContractID = @CursorContractID),
@CursorContractID,
'PMT', -- payment
GETDATE(),
@CursorTransactionID, -- Apply this payment to the charge with id @CursorTransactionID
PaymentToTakeFrom.PaymentID, -- We are taking money from this payment
CASE
WHEN ABS(PaymentToTakeFrom.RemainingBalance) > ABS(@CursorRemainingBalance) THEN ABS(@CursorRemainingBalance) * -1
ELSE PaymentToTakeFrom.RemainingBalance * -1
END AS PayAmount
FROM -- Find out which payment still has money left that we can distribute
(
SELECT TOP 1 * FROM
(SELECT
TPayment.PaymentID,
(SELECT ABS(TPayment.PaymentAmount)
-
(SELECT ISNULL(SUM(ABS(Amount)),0) FROM
#Transaction WHERE PaymentID = TPayment.PaymentID)) AS RemainingBalance
FROM #Payment TPayment
WHERE TPayment.ContractID = @CursorContractID
) AvailablePayment
WHERE AvailablePayment.RemainingBalance > 0
) AS PaymentToTakeFrom-- Calculate remaining balance for this charge
SET @CursorRemainingBalance = ABS(@CursorAmountToPay) -
(SELECT ISNULL(SUM(ABS(Amount)),0) FROM #Transaction WHERE ApplyID = @CursorTransactionID AND TransactionType = 'PMT')
ENDFETCH NEXT FROM ApplyAlreadyPaidChargesCursor INTO
@CursorTransactionID,
@CursorAmountToPay,
@CursorContractID
END
CLOSE ApplyAlreadyPaidChargesCursor
DEALLOCATE ApplyAlreadyPaidChargesCursorSELECT * FROM #Transaction
SELECT * FROM #Payment
SELECT * FROM #PreviouslyPaidDROP TABLE #Transaction
DROP TABLE #Payment
DROP TABLE #PreviouslyPaidI would like to get rid of the cursor in there since it is not efficient when dealing with large datasets but I can't figure out how to calculate a running total of money remaining when I have multiple payments, but I can't sum them up because the amount for a 'PMT' in the transaction table can't exceed the PaymentAmount in Payment table for the record it applies to.
Any help is appreciated. 🙂 -
I'm not entirely sure there's a set-based way to accomplish the UPDATE portion of your task, as there's always going to be a need to know exactly how much of each incoming payment has been "allocated" to a given charge. Cursors are NOT always bad. Sometimes, however, they're the only way to go, especially when UPDATE is involved, and the results of a previous record update can affect the methodology for updating a subsequent record, which is what this appears to be. Are there any performance problems with the cursor as is? You may be able to assist the queries in it via indexing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
One of the issues I am having is that while I was testing I wrote my stored procedure for 1 contract specific.
So taking all the charges/payments from the old system, insert them into the new system and using a output clause to get the new keys in the new system.When testing it actually performed really well, it took either 1 or 2 seconds for a contract to migrate, that included applying what was left over, handling adjustments and chargebacks / NSF checks etc.
The problem with that is that when I started executing that in a loop the first couple of hundred contracts go in like a breeze, but after that it slows down. The SQL server process starts to consume over 90% CPU.This took 16 hours to complete, I would expect it to be done in 2. Since every time I execute the LoadManual stored procedure for any given contract id it never takes more than a few seconds. Is there any reason this process slows down after a few 100 iterations ?
CREATE TABLE #ContractsToMigrate
(TSWContractID INT)CREATE TABLE #ErrorMessage
(EMessage VARCHAR(MAX))INSERT INTO #ContractsToMigrate(TSWContractID)
SELECT DISTINCT
ARLineItem.[ContractID]
FROM [TswData].[dbo].[t_ARLineItem] ARLineItem
INNER JOIN [TswData].[dbo].[t_Contract] TSWContract
ON TSWContract.ContractID = ARLineItem.ContractID
INNER JOIN [SPIConversionSales].[dbo].[ts_contract]
ON ts_contract.[cont_legacy_key] = TSWContract.ContractNumber
INNER JOIN [SPIConversionPMS].[dbo].[SalesContract]
ON SalesContract.[Sales_Cont_ID] = ts_contract.Cont_ID
WHERE SalesContract.ResortID = 'IFK '
AND TSWContract.SiteID = 16SELECT * FROM #ContractsToMigrate
DECLARE @ContractID INT
DECLARE cur CURSOR LOCAL FOR
SELECT TSWContractID FROM #ContractsToMigrateOPEN cur
FETCH NEXT FROM cur INTO @ContractIDwhile @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
EXEC SPIMigration.dbo.LoadManual @ContractID
PRINT 'NOW DOING CONTRACT'
PRINT @ContractID
END TRY
BEGIN CATCH
INSERT INTO #ErrorMessage(EMessage)
SELECT 'contract ' + CAST(@ContractID AS VARCHAR) + ' cannot be processed due to error: ' + error_message() + '; at line ' + cast(error_line() as varchar)
END CATCHFETCH NEXT FROM cur INTO @ContractID
ENDclose cur
deallocate curSELECT * FROM #ErrorMessage
DROP TABLE #ContractsToMigrate
DROP TABLE #ErrorMessage -
I believe that the following article addresses this topic, or it should at least get you started in the right direction. Solving FIFO Queues Using Windowed Functions
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Without your code for the stored procedure, it's going to be hard to see what's going on. However, if you use temp tables, you probably ought to give them PRIMARY KEYs, and even indexes if they're going to hold large quantities of data, and it will possibly make the query plans better. Even temp tables with just one field can benefit from that. Please post your sproc code and then we'll see what's up.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
See attached, this would be to do everything in 1 big bulk. (eliminating the code I had in the previous post)
I had to zip the file it didn't allow me to upload a .sql -
That's a TON of temp tables, and not one of them has any primary key or any indexing. At least get a useful primary key (use field or fields that are getting joined on) for every one of those, and then post back the new query and it's execution plan. Some record count information would also be helpful, as that will help with figuring out indexing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
It sure is, the tables most accessed I guess are the 'chargesinserted' , 'paymentsinserted' and 'ar', I will create a composite primary key for the first two. And a PK for 'ar'.
. Do you have any suggestions on indexing ? - drooijackers - Monday, August 28, 2017 12:19 PM
Indexing is generally the most useful when the additional cost for the insert to update the index is not large enough to offset the benefit from improved read performance for queries against that table. Typically, what you look to do with an index is make it "covering", which means that the fields in the index or those included in it, include all the fields from that table that are being selected in the queries against that table. However, that's a rather simple view of indexing, as it's not always obvious as to what the best choices are.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I'll let you know what the execution time is after indexing. We're looking at roughly 2,500,000 transactions. (payments/charges combined)
-
I have a execution plan do you think that will help ? It shows every cursor loop as a individual query with a cost of 0% though.
- drooijackers - Tuesday, September 12, 2017 8:44 AM
Go ahead and post it as a .SQLPlan file, and I'll let you know.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Below is the query that I wrote to get this result:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply