SQL函数性能 [英] 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

所以基本上，我想检查作为参数传递的 ID 是否存在于 TABLE1 上.如果返回一行，则将价格乘以 1.0368，否则价格将保持不变.

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.

我的问题是性能.我在返回 17000 行的视图上使用它.现在我的视图正在运行，第 12000 行 45 分钟.

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?

编辑

我这样称呼我的观点:

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

连同 select 语句.

along with the select statement.

推荐答案

如果你使用函数的唯一地方是在视图中，那么为什么不直接将逻辑封装在视图中:

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>;

外部应用只是与您的函数执行相同的检查以查看记录是否存在，然后在 select 语句中，当未找到匹配项时，价格乘以您的常数，否则乘以 1.

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.

您可以为此创建一个内联表值函数.与标量 UDF 不同，它不会执行 RBAR，但查询计划扩展到外部查询:

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
);

那么你会称它为:

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

这篇关于SQL函数性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！