Need help in improving performance of a stored proc which invokes multiple scalar functions on the resultset
-
I have stored proc which fetches a lot of data and the performance is really bad.
The problem is that this SP extensively uses Scalar functions (which does a complex calculation using while loop).
The scalar function is called on the resultset, which means the functions are invoked for each row of the result set.I have moved almost all logic of scalar functions to SP except for the while loop calculation.
Any help on what can be done to improve the performance of SP?
-
If you could provide the code of your stored procedure, maybe one of us will be able to assist.
It is very difficult to give you an answer without seeing where the problem is. - atulnathr - Wednesday, October 25, 2017 3:34 AM
Maybe post the WHILE loop function and ask whether it can be changed into a set-based operation?
- Paulo de Jesus - Wednesday, October 25, 2017 5:35 AM
Also include the code for the functions. We might be able to convert the scalar function to a single-row ITVF, which will perform better.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, October 25, 2017 9:03 AM
This, for sure.
-
I'm really sorry that I did not post any code for reference. Below is the scalar function which has the loop to do calculation to find over due days based on the payments done till now.
CREATE Function dbo.fn_Looping_Function(@aging_dt datetime, @aging_days_min int,
@payment_start_date datetime,@number_of_payments int,@payment_amount decimal(9,2),@final_payment_amount decimal(9,2),@total_applied_amount decimal(9,2)
,@contract_total decimal(9,2),@months_between_payments int) RETURNS int AsBEGIN
DECLARE @forgive_pct decimal(6,3)
DECLARE @overdue_amount decimal(9,2)
DECLARE @payment_date datetime
DECLARE @payment_nbr int
DECLARE @first_missed_payment_date datetime
DECLARE @aging_date datetime
DECLARE @previous_total_due decimal(9,2)
DECLARE @payment_due_amount decimal(9,2)
DECLARE @applied_amount decimal(9,2)
DECLARE @days_overdue int/* Get Forgive Percent */SET @forgive_pct = 1.00/* Get Aging Date In Date Format */
SET @aging_date = convert(datetime, @aging_dt)------------------------------------------------------------------------------------------------
-- LOOP THROUGH PAYMENT SCHEDULE TO DETERMINE AGED OVERDUE AMOUNT
------------------------------------------------------------------------------------------------
SET @overdue_amount = 0.00
SET @payment_date = @payment_start_date
SET @payment_nbr = 1
SET @first_missed_payment_date = NULLWHILE (@payment_date <= @aging_date) and (@payment_nbr <= @number_of_payments)
BEGIN/* find the total amount previously due */
SET @previous_total_due = (@payment_nbr -1) * @payment_amount/* find the amount due for this installment payment */
IF @payment_nbr < @number_of_payments
SET @payment_due_amount = @payment_amount
ELSE
SET @payment_due_amount = @final_payment_amount
/* find the amount applied to this installment payment */
IF @total_applied_amount >= @previous_total_due + @payment_due_amount
SET @applied_amount = @payment_due_amount
ELSE
BEGIN
IF @total_applied_amount <= @previous_total_due
SET @applied_amount = 0.00
ELSE
SET @applied_amount = @total_applied_amount - @previous_total_due
END/* add to the total overdue if curent payment has not been fully applied */
IF @applied_amount < (@payment_due_amount * @forgive_pct)
SET @overdue_amount = @overdue_amount + (@payment_due_amount - @applied_amount)if @number_of_payments = 1 and isnull(@applied_amount,0.00) = 0.00 and isnull(@payment_due_amount,0.00) = 0.00 and @contract_total - @total_applied_amount > 0.00
set @overdue_amount = @contract_total - @total_applied_amountIF @overdue_amount > 0 and @first_missed_payment_date is NULL
SET @first_missed_payment_date = @payment_date/* get next payment date */
SET @payment_date = DATEADD(mm, @months_between_payments * @payment_nbr, @payment_start_date)
SET @payment_nbr = @payment_nbr + 1END /* End WHILE Loop */IF DATEDIFF(d , IsNull(@first_missed_payment_date, @aging_date), @aging_date) - @aging_days_min < 0
SET @days_overdue = 0
ELSE
SET @days_overdue = DATEDIFF(d , IsNull(@first_missed_payment_date, @aging_date), @aging_date) - @aging_days_minRETURN @days_overdueEND - atulnathr - Thursday, October 26, 2017 4:30 AM
Unfortunately, that function is a great big nothing-burger... As it doesn't touch any tables, there's not much optimization to do except to replace the loop with straight computation, but given WHAT this loop is doing, I have to wonder if using the actual table data might be more efficient. You'd need to provide a LOT more information about where the parameters for this function come from to get a "good" solution. Also, we'd need the exact definition of the various terms used to describe things in the function....
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
Agree with Steve. By the way, nice function. I think that's the first time I've seen a SQL Server function that's a function that includes logic without touching tables.
-
I think that I can convert this into an inline table-valued function. That would at least allow parallelism and won't be called one row at a time.
I just need to get some sample data and expected results based on that data. I'll be simplifying the function, but it should still consider everything. -
The aging date could be done in the SELECT, so I think the rest of the function can also. When I have done this kind of thing, I have used a temp table or table variable to hold intermediate results to make the code easy to read and maintain. Here is a sample:
INSERT INTO #Age
(
vst_int_id,
Aging,
Age_Sum,
fin_cls_cd,
Current_Insurance,
Financial_Class
)
SELECT DISTINCT
#Ref.vst_int_id,
DATEDIFF
(
DAY,
#Ref.billing_date,
CURRENT_TIMESTAMP
) AS Aging,
(
SELECT
#Ref.ttl_charges_billed_at -
(#Ref.insurance_pmt_at +
#Ref.patient_payment_at)
FROM
#Ref
) AS Aging_Sum,
TPB105.fin_cls_cd,
plan_ds AS Current_Insurance,
cod_dtl_ds AS Financial_Class - jim.snyder - Thursday, October 26, 2017 8:48 AM
The problem with using temp tables here is that it would still prevent you from making the function an ITVF, and changing this to an ITVF is the whole point.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thank you all for looking into this. Below is a sample invocation of the function.
select ISNULL(dbo.fn_Looping_Function('10-10-2017', 0,'2015-08-24 00:00:00.000',12,1085.52,1085.54,100,13026.26,1),0)This is expected to give me an output of 778. This output will be updated to a temp table column in my parent SP.
The parameters of the function stands for the below
@aging_dt => User input
@aging_days_min => user input
@payment_start_date => Start date of the payments
@number_of_payments => Number of payments to be done to fulfill the total
@payment_amount => Value of each payment.
@final_payment_amount => Value of the last payment to be done to fulfill the total
@total_applied_amount => How much amount has been payed till now.
@contract_total => This is amount to be fulfilled
@months_between_payments => How many months between each payment. -
This gives the result you expect with the parameters shown. However, I suggest that you do a more extensive testing as I might have missed something.
CREATE Function dbo.ifn_Looping_Function(
@aging_date datetime,
@aging_days_min int,
@payment_start_date datetime,
@number_of_payments int,
@payment_amount decimal(9,2),
@final_payment_amount decimal(9,2),
@total_applied_amount decimal(9,2),
@contract_total decimal(9,2),
@months_between_payments int
) RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(@number_of_payments) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
),
ctePayments AS(
SELECT payment_date = DATEADD(mm, @months_between_payments * (n-1), @payment_start_date),
payment_due_amount = CASE WHEN @number_of_payments = 1 THEN @contract_total
WHEN @number_of_payments < n THEN n * @payment_amount
ELSE (n-1) * @payment_amount + @final_payment_amount
END
FROM cteTally
WHERE DATEADD(mm, @months_between_payments * n, @payment_start_date) <= @aging_date
)
SELECT AgingDays = CASE WHEN DATEDIFF(d , IsNull(MIN(payment_date), @aging_date), @aging_date) - @aging_days_min > 0
THEN DATEDIFF(d , IsNull(MIN(payment_date), @aging_date), @aging_date) - @aging_days_min
ELSE 0 END
FROM ctePayments
WHERE payment_due_amount > @total_applied_amount;
Of course, you need to call the function in a different way.
SELECT st.*, x.AgingDays
FROM SomeTables st
CROSS APPLY dbo.ifn_Looping_Function( aging_date, aging_days_min, payment_start_date, number_of_payments,
payment_amount, final_payment_amount, total_applied_amount,
contract_total, months_between_payments ) x -
Please do automated testing. Have a test rig like tsqlt that can run a bunch of these against your query and ensure the edge cases are correct.
-
Thanks a lot for the function. It's awesome. I'm still testing it out and trying to fix some problems.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply