February 5, 2010 at 4:02 am
I have a stored proc that uses a fast forward cursor to process data(Warehouse_UpdateAllTables). I need it to run faster than it does as the data that it processes is growing by the day. Please assist. I have attached what i thought is relevant. If you need anything or there is something missing, please let me know.
Thanks
February 5, 2010 at 5:44 am
tendayit thanks for posting everything we might need to analyze it; it's so large, it'd take a lot to look at it all;
your functions 2200+lines ,and it's all doing RBAR (Row By Agonizing Row) processing, and not doing anything set based.
it's also calling the same function (dbo.GetDetailCode) a LOT of times, to populate different @variables, whatever that code is doing would be replaced with joins t the table to get the value instead.
the function itself is not complex, but the project and function is so large, you might want to hire a consultant to address this; i'm not sure how much help you'll get for free, other than advice like "getrrid of the cursor" I do not see anything that actually requires a cursor.
this is basically what the function is doing:
-- inserting new records Warehouse_Document and updating existing to the same table.
-- doing the same for :
Warehouse_FieldAgent
Warehouse_FinancialSummary
Warehouse_LegalAction
Warehouse_MatterStatus
Warehouse_SMSAction
Warehouse_Tracing
from the body where you are gathering values, there's a handful of different WHERE statements, but they are repeated so many times, it's clear that everything could be replaced with a set based operation; i'd simply break this up into a master proc that called 7 child procs, where each child proc updates one of the Warehouse_tables as a set based operation.
even the function GetDetailCode could get fixed as well, by using the FOR XML command to return the comma delimited list it returns, instead of using the cursor.
looks like lots of low hanging fruit to fix, but it would take some time to go through it all.
Lowell
February 5, 2010 at 7:00 am
I have to agree with Lowell on this one. I could easily spend my entire weekend working on this one, but not willing to do that for free. There is one thing missing, however, to really work this project is sample data to use in redeveloping the procedure. But even without that could still be done since you provided the code for the tables.
February 5, 2010 at 9:28 pm
Don't even try to salvage the code. Sit down and write a new set of requirements for the code without thinking about how to code it. Then, sit down with someone who knows how to write set based code and bang through it. The new set based code will likely run hundreds of times faster and will be much shorter to boot.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2010 at 1:01 am
tendayit (2/5/2010)
I have a stored proc that uses a fast forward cursor to process data(Warehouse_UpdateAllTables). I need it to run faster than it does as the data that it processes is growing by the day. Please assist. I have attached what i thought is relevant. If you need anything or there is something missing, please let me know.Thanks
Dude, seriously?
As others have mentioned, this is 2200 lines of SQL code, and all of it bad. I would be happy to fix this for you, it's the kind of thing that I do all the time. However, I have a special word for it when it passes over a certain size, and that word is "work", and as a general rule I get paid for it.
My companies contact info is in my signature if you want to go that route, and I am sure that there are others here who would be available for contract also..
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 2:32 am
Thanks for all your responses. Its code that i inherited and did not write myself and i was not too happy with its speed and fuctionality. I was just looking for a faster and better way to improve things. I will try and see if i can change it first. If not successful i will give you a shout.
Thanks
February 8, 2010 at 9:00 am
tendayit (2/8/2010)
... Its code that i inherited and did not write myself and i was not too happy with its speed and fuctionality. I was just looking for a faster and better way to improve things. ...
Nothing wrong with that, we've all been there. And there is nothing wrong in bringing this problem here, or in asking us for help. The point that we are trying to make, however, is that there is a difference between "Help" and "Work", and that this difference is primarily one of size.
That is, if you made it smaller, it would be fine for us to try and help you with it.
Now I have taken a second look at it and it appears to me that it should be relatively easy for you to do that: to cut it down by about 80%, to a size that would be reasonable for us to respond to.
How? Well, the vast majority of this code is taken up in long lists of column names and variable names, and (here's the key), for "Help" you do not need them all, you just the "Key" columns from each table plus one additional "data" column per table to stand in for all of the other columns. So just remove all of the columns that are either not used in this proc or are logically redundant (i.e., redundant for the purposes of receiving help on the logic of this proc). Also, since most of the variables are there to hold copies of the column values, you should be able to eliminate most of them as well.
After that, make some test data, test what you have to make sure that it still works and then repost it here to this thread. Then we should be able to help you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 9:05 am
I should add also, that if multiple tables are treated in a logically identical way in the proc, then should also be able to eliminate the logically redundant tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 3:58 pm
OK, I had nothing better to do today, so I went through and pared your proc down by 1) reducing the number of output tables from 6 to 3, 2) eliminating about 1/3 of the columns, 3) removing one of the four input tables ("matters")) as it was no longer needed and 4) deleting all remaining unused variables. I also reformatted it some to a form that I find easier to read (this actually added lines as there were a lot of "squashed" INSERT commands).
With all this the line count is not down to just over 500, which I think is close to what folks here would be willing to help with.
Note that the following issues still remain as an obstacle to us working on it:
* There is no sample data, so it will not actually run
* One of the tables is missing (detail sub-codes, I think), so one of the functions will not compile
* This same missing table will ultimately prevent the proc from running too.
Here it is:
ALTER PROCEDURE [dbo].[Warehouse_UpdateAllTables]
AS BEGIN
DECLARE
@AccountId varchar(20),
@capital money,
@CapitalOutstanding money,
@TotalPaid money,
@DateLastPaid datetime,
@AmountLastPaid money,
@DatelastRD datetime,
@AmountLastRD money,
@A0Date datetime,
@LastVoidTransactionDate datetime,
@LastSettlementDate datetime,
@Installment money,
@newDueDate datetime,
@DateFirstPaid datetime,
@NoPreviousDefaults int,
@NoPreviousVoids int,
@AgeingBasedOnPayments int,
@AgeingBasedOnDefaulter int,
@TotalCredit money,
@CollectComm money,
@RDComm money,
@TotalDebit money,
@DateLastCredit Datetime,
@DateLastDebit datetime,
@AmountLastDebit money,
@TempAccount varchar(5),
@ActivationSMS int,
@ArrConfirmationSMS int,
@ReminderSMS int,
@DefaultSMS int,
@PayconfirmationSMS int,
@RDSMS int,
@R50SMS int,
@LowCapitalSMS int,
@DateLastSMS datetime,
@LastSMSCode varchar(4),
@LastSMSJobNo varchar(20),
@NoITCMatch int,
@NoExperianMatch int,
@DatelastITCMatch datetime,
@DateLastITCSuccessful datetime,
@DateLastITCUnsuccessful datetime,
@ActivationSMSCode varchar(255),
@ArrConfirmationSMSCode varchar(255),
@ReminderSMSCode varchar(255),
@DefaultSMSCode varchar(255),
@PayConfirmationSMSCode varchar(255),
@RDSMSCode varchar(255),
@R50SMSCode varchar(255),
@LowCapitalSMSCode varchar(255),
----change made 21 november 2007
@MiscellaneousSMSCode varchar(255),
----end change
@AllSMSCodes varchar(255),
@ITCMatchCode varchar(255),
@ExperianMatchCode varchar(255),
@ITCMatchSuccessfulCode varchar(255),
@ITCUnsuccessfulCode varchar(255)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SELECT @ActivationSMSCode = dbo.GetDetailCode_NoCursor( 'Activation SMS' )
SELECT @ArrConfirmationSMSCode = dbo.GetDetailCode_NoCursor( 'Arr Confirmation SMS')
SELECT @ReminderSMSCode = dbo.GetDetailCode_NoCursor( 'Reminder SMS')
SELECT @DefaultSMSCode = dbo.GetDetailCode_NoCursor( 'Default SMS')
SELECT @PayConfirmationSMSCode = dbo.GetDetailCode_NoCursor( 'Pay Confirmation SMS')
SELECT @RDSMSCode = dbo.GetDetailCode_NoCursor( 'RD SMS')
SELECT @R50SMSCode = dbo.GetDetailCode_NoCursor( 'R50SMS')
SELECT @LowCapitalSMSCode = dbo.GetDetailCode_NoCursor( 'Low Capital SMS')
--change made 21 November 2007
SELECT @MiscellaneousSMSCode = dbo.GetDetailCode_NoCursor( 'Miscellaneous')
--end change
--change made 21 November 2007
SELECT @AllSMSCodes = CAST(ISNULL(@ActivationSMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@ArrConfirmationSMSCode, ' ')AS varchar) + ',' + CAST(ISNULL(@ReminderSMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@DefaultSMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@PayConfirmationSMS, ' ') AS varchar) + ','
SELECT @AllSMSCodes = @AllSMSCodes + CAST(ISNULL(@RDSMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@R50SMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@LowCapitalSMSCode, ' ') AS varchar) + ',' + CAST(ISNULL(@MiscellaneousSMSCode, ' ') AS varchar)
--end change
SELECT @ITCMatchCode = dbo.GetDetailCode_NoCursor( 'ITC Match' )
SELECT @ExperianMatchCode = dbo.GetDetailCode_NoCursor( 'Experian Match' )
SELECT @ITCMatchSuccessfulCode = dbo.GetDetailCode_NoCursor( 'ITC Match Successful' )
SELECT @ITCUnsuccessfulCode = dbo.GetDetailCode_NoCursor( 'ITC Unsuccessful' )
DECLARE C CURSOR FAST_FORWARD FOR
SELECT
AD.AccountId,
AD.Capital,
AR.Installment,
A.TempAccount
FROM
DebtorMaster AD (NOLOCK)
JOIN Arrangement AR (NOLOCK) ON AR.AccountId = AD.AccountId
JOIN Account A (NOLOCK) ON A.AccountId = AR.AccountId
--JOIN Matter M (NOLOCK) ON M.AccountId = A.AccountId
WHERE
A.AccountType = '&' AND
AD.AccountId IN (
SELECT AccountID
FROM AccountUpdated (NOLOCK)
WHERE Updated = 0
AND TransactionTable = 1
)
OPEN C
FETCH NEXT FROM C INTO
@AccountId
, @capital
, @Installment
, @TempAccount
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TotalPaid = NULL
SELECT @DateLastPaid = NULL
SELECT @AmountLastPaid = NULL
SELECT @DatelastRD = NULL
SELECT @AmountLastRD = NULL
SELECT @LastVoidTransactionDate = NULL
SELECT @LastSettlementDate = NULL
SELECT @newDueDate = NULL
SELECT @DateFirstPaid = NULL
SELECT @NoPreviousDefaults = NULL
SELECT @NoPreviousVoids = NULL
SELECT @CollectComm = NULL
SELECT @RDComm = NULL
SELECT @AgeingBasedOnPayments = NULL
SELECT @AgeingBasedOnDefaulter = NULL
SELECT @ActivationSMS = NULL
SELECT @ArrConfirmationSMS = NULL
SELECT @ReminderSMS = NULL
SELECT @DefaultSMS = NULL
SELECT @PayconfirmationSMS = NULL
SELECT @RDSMS = NULL
SELECT @R50SMS = NULL
SELECT @LowCapitalSMS = NULL
SELECT @DateLastSMS = NULL
SELECT @LastSMSCode = NULL
SELECT @LastSMSJobNo = NULL
SELECT @NoITCMatch = 0
SELECT @NoExperianMatch = 0
SELECT @DatelastITCMatch = NULL
SELECT @DateLastITCSuccessful = NULL
SELECT @DateLastITCUnsuccessful = NULL
SELECT * INTO #Transaction
FROM dbo.[Transaction]
WHERE AccountId = @AccountId
-- sum up all payments (credits) on this account
SELECT @TotalCredit = ISNULL(SUM(Amount),0.00)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND (( Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) )
--MarkK 04 Apr 2007, Tendayit 17 January 2007, MarkK 01 Feb 2008
SELECT @CollectComm = ISNULL(SUM(CASE WHEN (Amount*.10) > 300 THEN 300 ELSE (Amount*.10)END),0.00)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND (( Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) ) AND
TransactionDate <= '31 OCT 2007'
SELECT @CollectComm = (@CollectComm + ISNULL(SUM(CASE WHEN (Amount*.10) > 315 THEN 315 ELSE (Amount*.10)END),0.00))
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND (( Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) ) AND
TransactionDate > '31 OCT 2007'
--Sum up all Rd Commission. Commission changed from 300 to 315
SELECT @RDComm = ISNULL(SUM(CASE WHEN (Amount*.10) > 300 THEN 300 ELSE (Amount*.10)END),0.00)
FROM
#Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND ((Business = 0 AND debit = 1)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 1)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 1) )
AND TransactionDate <= '31 OCT 2007'
SELECT @RDComm = (@RDComm + ISNULL(SUM(CASE WHEN (Amount*.10) > 315 THEN 315 ELSE (Amount*.10)END),0.00))
FROM
#Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND (( Business = 0 AND debit = 1)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 1)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 1)
--ended change
)
AND TransactionDate > '31 OCT 2007'
SELECT @CollectComm = (@CollectComm +(@CollectComm*.14))
SELECT @RDComm = (@RDComm +(@RDComm*.14))
select @CollectComm = (@CollectComm - @RDComm)
-- sum up all payments (credits) on this account
SELECT @DateLastCredit = Max(TransactionDate)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND ((Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) )
-- sum up all non payments (debits) on this account
SELECT @TotalDebit = ISNULL(SUM(Amount),0.00)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND ((Business = 0 AND debit = 1)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 1)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 1) )
-- sum up all non payments (debits) on this account
SELECT @DateLastDebit = Max(TransactionDate)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND ((Business = 0 AND debit = 1)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 1)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 1) )
-- sum up all payments (credits) on this account
SELECT @DateFirstPaid = MIN(TransactionDate)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND ((Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) )
-- TotalPiad = totalCredit - TotalDebit
SELECT @TotalPaid = @TotalCredit - @TotalDebit
-- capital outstanding = Capital - TotalPaid
SELECT @CapitalOutstanding = @capital - @TotalPaid
-- Last Payment date
SELECT @DateLastPaid = @DateLastCredit
-- debits on the last payment date if any
SELECT @AmountLastDebit = ISNULL(SUM(Amount),0.00)
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND
DATEDIFF( day, TransactionDate, @DateLastCredit) = 0 AND ((Business = 0 AND debit = 1)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 1)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 1) )
-- amount last paid
SELECT @AmountLastPaid = SUM(Amount) - @AmountLastDebit
FROM #Transaction (NOLOCK)
WHERE AccountId = @AccountId
AND
DATEDIFF( day, TransactionDate, @DateLastCredit) = 0 AND ((Business = 0 AND debit = 0)
OR (TransactionTypeId = 'J' AND MemoType = 'M' AND FeeEarner = 'R' AND Debit = 0)
OR (TransactionTypeId = 'R' AND MemoType = 'M' AND Debit = 0) )
-- date of last credit payment transaction
--MarkK 06 Mar 2007
SELECT @DateLastRD = MAX(TransactionDate),
@AmountLastRD = MAX(Amount)
FROM
#Transaction (NOLOCK)
WHERE
(DTCODE = 'GV' AND TransactionTypeID = 'R') AND
TransactionDate > @DateLastPaid
-- last void transaction date
SELECT @LastVoidTransactionDate = MAX(TransactionDate)
FROM #Transaction T
JOIN DetailCode DT ON DT.DetailCode = T.DTCode
JOIN detailCodeSubCategory ds on ds.DetailCodeSubCategoryId = DT.DetailCodeSubCategoryId
WHERE
DS.[Description] = 'Arrangement Void'
-- Last Settlement Arrangement Date after the last void transaction if there was one otherwise the last transactiondate for a active subfee earner
SELECT @LastSettlementDate = MIN(TransactionDate)
FROM #Transaction T
JOIN DetailCode DT ON DT.DetailCode = T.DTCode
JOIN detailCodeSubCategory ds on ds.DetailCodeSubCategoryId = DT.DetailCodeSubCategoryId
WHERE
DS.[Description] IN ('Arranging Settlement Payment') AND TransactionDate > ISNULL(@LastVoidTransactionDate, '1 JAN 1950') AND Reference IN (
SELECT SFECode FROM SFE (NOLOCK) WHERE (SFE.Status NOT IN ('SQLNOTE', 'SQLFEES', 'LV1', 'LG1', 'GF1', 'AJ1', '005', '', 'ANO') AND
SFE.Status IS NOT NULL AND SFE.Status NOT LIKE 'Job%'))
SELECT @Installment = 0
--change Tendayit 4 november 2008
SELECT @A0Date = MAX(Transactiondate) FROM #Transaction WHERE DtCode = 'A0'
-- Last Arrangement Updated By
SELECT @NoPreviousVoids = COUNT(0)
FROM #Transaction T
JOIN DetailCode DT ON DT.DetailCode = T.DTCode
JOIN detailCodeSubCategory ds on ds.DetailCodeSubCategoryId = DT.DetailCodeSubCategoryId
WHERE
DS.[Description] IN ('Arrangement Void') AND TransactionDate < @LastVoidTransactionDate
SELECT @AgeingBasedOnPayments = ABS(DATEDIFF( day, GETDATE(), @DateLastPaid))
IF @TotalPaid <= 0
SELECT @DateLastPaid = NULL
-- No of Activation SMS Sent
SELECT @ActivationSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ActivationSMSCode+ ',') > 0
-- No of Arrangement Confirmation SMS Sent
SELECT @ArrConfirmationSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ArrConfirmationSMSCode+ ',') > 0
-- No of Reminder SMS Sent
SELECT @ReminderSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ReminderSMSCode+ ',') > 0
-- No of Defaulter SMS Sent
SELECT @DefaultSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @DefaultSMSCode+ ',') > 0
-- No Of Pay Confirmation SMS Sent
SELECT @PayConfirmationSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @PayConfirmationSMSCode+ ',') > 0
-- No of RD SMS Sent
SELECT @RDSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @RDSMSCode+ ',') > 0
-- No of R50SMS Sent
SELECT @R50SMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @R50SMSCode+ ',') > 0
-- No Of Low Capital SMS Sent
SELECT @LowCapitalSMS = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @LowCapitalSMSCode+ ',') > 0
SELECT TOP 1
@DateLastSMS = TransactionDate,
@LastSMSCode = DetailCodeId,
@LastSMSJobNo = Reference
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @AllSMSCodes+ ',') > 0
ORDER BY
T.TransactionDate DESC
-- Last SMS Job NO
SELECT TOP 1 @LastSMSJobNo = T.Reference
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @AllSMSCodes+ ',') > 0
ORDER BY T.TransactionDate DESC
SELECT @NoITCMatch = COUNT(0),
@DateLastITCMatch = MAX(TransactionDate)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ITCMatchCode+ ',') > 0
SELECT @NoExperianMatch = COUNT(0)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ExperianMatchCode+ ',') > 0
SELECT @DateLastITCSuccessful = MAX(TransactionDate)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ITCMatchSuccessfulCode+ ',') > 0
SELECT @DateLastITCUnSuccessful = MAX(TransactionDate)
FROM #Transaction T
WHERE CHARINDEX( ',' + T.DTCode + ',', ',' + @ITCUnsuccessfulCode+ ',') > 0
IF (SELECT COUNT(0) FROM Warehouse_FinancialSummary WHERE AccountId = @AccountId) = 0
INSERT INTO Warehouse_FinancialSummary(
AccountId
, Capital
, CapitalOutstanding
, TotalPaid
, DateLastPaid
, AmountLastPaid
, DateLastRD
, AmountLastRD
, LastVoidTransactionDate
, LastSettlementDate
, Installment
, NewDueDate
, DateFirstPaid
, NoPreviousDefaults
, NoPreviousVoids
, AgeingBasedOnPayments
, AgeingBasedOnDefaulter
, CollectComm)
VALUES(
@AccountId
, @capital
, @CapitalOutstanding
, @TotalPaid
, @DateLastPaid
, @AmountLastPaid
, @DateLastRD
, @AmountLastRD
, @LastVoidTransactionDate
, @LastSettlementDate
, @Installment
, @NewDueDate
, @DateFirstPaid
, @NoPreviousDefaults
, @NoPreviousVoids
, @AgeingBasedOnPayments
, @AgeingBasedOnDefaulter
, @CollectComm)
ELSE
UPDATE
Warehouse_FinancialSummary
SET
Capital = @capital,
CapitalOutstanding = @CapitalOutstanding,
TotalPaid = @TotalPaid,
DateLastPaid = @DateLastPaid,
AmountLastPaid = @AmountLastPaid,
DateLastRD = @DateLastRD,
AmountLastRD = @AmountLastRD,
LastVoidTransactionDate = @LastVoidTransactionDate,
LastSettlementDate = @LastSettlementDate,
Installment = @Installment,
DateFirstPaid = @DateFirstPaid,
NoPreviousDefaults = @NoPreviousDefaults,
NoPreviousVoids = @NoPreviousVoids,
AgeingBasedOnPayments = @AgeingBasedOnPayments,
AgeingBasedOnDefaulter = @AgeingBasedOnDefaulter,
CollectComm = @CollectComm,
SysUserUpdated = USER_NAME()
WHERE
AccountId = @AccountId
--Warehouse_SMSAction
IF (SELECT COUNT(0) FROM Warehouse_SMSAction (NOLOCK) WHERE AccountId = @AccountId) = 0
INSERT INTO
Warehouse_SMSAction(
AccountId
, ActivationSMS
, ArrConfirmationSMS
, ReminderSMS
, DefaultSMS
, PayconfirmationSMS
, RDSMS
, R50SMS
, LowCapitalSMS
, DateLastSMS,
LastSMSCode
, LastSMSJobNo )
VALUES( @AccountId
, @ActivationSMS
, @ArrConfirmationSMS
, @ReminderSMS
, @DefaultSMS
, @PayconfirmationSMS
, @RDSMS
, @R50SMS
, @LowCapitalSMS
, @DateLastSMS,
@LastSMSCode
, @LastSMSJobNo )
ELSE
UPDATE
Warehouse_SMSAction
SET
ActivationSMS = @ActivationSMS,
ArrConfirmationSMS = @ArrConfirmationSMS,
ReminderSMS = @ReminderSMS,
DefaultSMS = @DefaultSMS,
PayconfirmationSMS = @PayconfirmationSMS,
RDSMS = @RDSMS,
R50SMS = @R50SMS,
LowCapitalSMS = @LowCapitalSMS,
DateLastSMS = @DateLastSMS,
LastSMSCode = @LastSMSCode,
LastSMSJobNo = @LastSMSJobNo,
SysUserUpdated = USER_NAME()
WHERE
AccountId = @AccountId
IF (SELECT COUNT(0) FROM Warehouse_Tracing WHERE AccountId = @AccountId) = 0
INSERT INTO
Warehouse_Tracing(
AccountId
, NoITCMatch
, NoExperianMatch
, DatelastITCMatch
, DateLastITCSuccessful
, DateLastITCUnsuccessful
)
VALUES( @AccountId
, @NoITCMatch
, @NoExperianMatch
, @DatelastITCMatch
, @DateLastITCSuccessful
, @DateLastITCUnsuccessful
)
ELSE
UPDATE
Warehouse_Tracing
SET
NoITCMatch = @NoITCMatch,
NoExperianMatch = @NoExperianMatch,
DatelastITCMatch = @DatelastITCMatch,
DateLastITCSuccessful = @DateLastITCSuccessful,
DateLastITCUnsuccessful = @DateLastITCUnsuccessful,
SysUserUpdated = USER_NAME()
WHERE
AccountID = @AccountId
DROP TABLE #Transaction
FETCH NEXT FROM C INTO
@AccountId
, @capital
, @Installment
, @TempAccount
END
-- WE DO NOT HAVE THIS PROC
--EXEC WarehouseLogAddNew @TableName = 'Warehouse_UpdateAllTables', @RecordsToUpdate = 0, @RecordsProcessed = @RecordCount, @Successful = 1
CLOSE C
DEALLOCATE C
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 4:03 pm
I also cleaned up one of the two functions as it had a lot of issues (an internal cursor, and referencing that missing table). Its not valid without that table of course, but it should be good enough for performance testing.
ALTER FUNCTION [dbo].[GetDetailCode_NoCursor]( @Category varchar(255) ) RETURNS varchar(255)
AS /* Removed the Noxious Cursor. Still an RBAR Function though */
BEGIN
DECLARE @DetailCode AS VARCHAR(255)
SET @DetailCode = ''
;WITH DTCodes AS (
SELECT RTRIM(REPLACE(ISNULL(DetailCode, ''), char(13) + char(10), '')) AS DTCode
, ROW_NUMBER() OVER(ORDER BY DetailCode) AS RowNo
FROM DetailCode DT
-- NO detailCodeSubCategory TABLE IN TEST KIT
--JOIN detailCodeSubCategory ds on ds.DetailCodeSubCategoryId = DT.DetailCodeSubCategoryId
--WHERE DS.[Description] = @Category
WHERE dt.[Description] = @Category
)
SELECT @DetailCode = CASE WHEN RowNo = 1 THEN DTCode ELSE ',' + DTCode END
FROM DTCodes
RETURN @DetailCode
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 4:08 pm
I fixed up the other function as it had some problems too (two parameters it wasn't using, really inefficient statement structure, etc).
ALTER FUNCTION [dbo].[DebtorProfileGet_New] (@TotalPaid float, @TotalDue float) RETURNS int AS
BEGIN
DECLARE @PercentPaid float,@PercentPayment float,@Profile int
SELECT @PercentPaid = COALESCE((@TotalPaid / @TotalDue) * 100,0)
-- NOT USED?
--SELECT @PercentPayment = COALESCE((@NoPayments / @DuePayments) * 100, 0)
IF @PercentPaid > 80
SELECT @Profile = 0
ELSE IF @PercentPaid BETWEEN 60 AND 79
SELECT @Profile = 1
ELSE IF @PercentPaid BETWEEN 40 AND 59
SELECT @Profile = 2
ELSE IF @PercentPaid BETWEEN 20 AND 39
SELECT @Profile = 3
ELSE IF @PercentPaid BETWEEN 10 AND 19
SELECT @Profile = 4
ELSE
SELECT @Profile = 5
RETURN @Profile
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 4:21 pm
(Oops, bad post. Please ignore)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 10, 2010 at 2:45 am
Hi,
Thanks for the help. I now know what to do in future. I have attached the missing tables and some sample data. I took a 100 accounts for the sampling. The data is in excel format. Hope this is ok.
February 10, 2010 at 6:42 am
tendayit (2/10/2010)
Hi,Thanks for the help. I now know what to do in future. I have attached the missing tables and some sample data. I took a 100 accounts for the sampling. The data is in excel format. Hope this is ok.
Actually, it is not OK. What am I supposed to do with 12,000+ rows of Excel data in a dozen different files? Excel is nigh useless for us. As explained in almost every one of the half-dozen explanatory links in the signatures of the first three replies to your original post, we need this data in the form of INSERT statements.
Here's one of those links explaining that: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Here's another link with a stored procedure that automates creating those INSERT Scripts: http://www.sqlservercentral.com/articles/scripting/64884/
Also, make these all one big script separated by "GO"s, and not 12 different scripts. Remember, you're asking a lot of us, so try to make it as easy as possible for us.
Also, if you've got more than 1000 rows in one of the tables of "sample" data, then you might be better off making a BCP natural export file (if you do that don't forget to provide the explicit import command that goes along with it).
Well, that's it. That's as easy as I can make it for you, to make it easy for us to help you. Let us know if you have any questions about this process or need any more assistance with it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2010 at 9:17 am
Sorry for getting back to you later than expected. There were a lot of work issues that i had to get round. I have tried to come up with the data as you requested. I hope i got it right. If not, please advise. I had a problem generating the transaction bcp export and import command for you. Maybe i was getting the syntax wrong. I ended up attaching a format file, the transaction file and the code that i used to generate it.
The tables reside in 2 databases, UniverseRelational and UniverseWarehouse. I tried to make the scripts create the tables in the appropriate database.
Thanks
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply