Gerald Gonzales Gerald Gonzales - 7 months ago 21
SQL Question

SQL function performance

I have this function which is used on my view.

FUNCTION [dbo].[CalculateAmount] (
@ID INT,
@PRICE DECIMAL(24,4))

declare @computedValue decimal(24,4)

set @computedValue = case when
(select TABLE1.ID
FROM dbo.[TABLE1] TABLE1
JOIN dbo.[TABLE2] TABLE2 ON TABLE2.ID = TABLE1.ID
WHERE TABLE1.ID = @ID // some conditions here //
Is null
then @PRICE
else
@PRICE * CAST('1.0368' as decimal(24,4))
end


So basically, I wanted to check if the ID passed as parameter is existing on TABLE1. If it returned a row, multiply the Price to 1.0368, if not then the Price will remain as is.

My problem here is the performance. I am using this on view which returns 17000 rows. Now my view is currently running, 45 minutes on 12000th row.

Any ideas or suggestion to improve the performance of my view?

EDIT

I am calling this on my view like this:

[dbo].[CalculateAmount](ID, PRICE) AS PRICE


along with the select statement.

Answer

If the only place you use your function is in the view, then why not just encapsulate the logic in the view:

ALTER VIEW dbo.YourView
AS
    SELECT  <columns>,
            CalculatedPrice = CASE WHEN t1.ID IS NULL THEN <tables>.Price 
                                    ELSE 1.0368 * <tables>.Price 
                                END
    FROM    <tables>
            OUTER APPLY
            (   SELECT  TOP 1 t1.ID
                FROM    dbo.Table1 AS t1
                        INNER JOIN dbo.Table2 AS t2
                            ON t2.ID = t1.ID
                WHERE   t1.ID = <tables>.ID
                --      More Conditions
            ) AS t1
    WHERE   <predicates>;

The outer apply simply does the same check as your function to see if a record exists, then in the select statement, when a match is not found the price is multiplied by your constant, otherwise it is multiplied by 1.

You could create an inline table valued function for this. Unlike a scalar UDF this is not executed RBAR, but the query plan is expanded out into the outer query:

CREATE FUNCTION dbo.CalculateAmount (@ID INT, @Price DECIMAL(24, 4)
RETURNS TABLE
AS
RETURN
(   SELECT  Price = CASE WHEN COUNT(*) = 0 THEN @Price
                        ELSE @Price * 1.0368
                    END
    FROM    dbo.Table1 AS t1
            INNER JOIN dbo.Table2 AS t2
                ON t2.ID = t1.ID
    WHERE   t1.ID = @ID
);

Then you would call it as:

SELECT  <columns>,
        CalculatedPrice = ca.Price
FROM    <tables>
        OUTER APPLY dbo.CalculateAmount(ID, Price) AS ca
WHERE   <predicates>;