WHERE 子句中的函数 - 处理性能不佳的问题

Question

我有一个大表，里面有超过 100000 条记录。

我需要在用于评估 totalSum 的 where 子句中添加另一个条件，并仅返回 totalSum 为 <>0 的记录。

这里使用了大量的连接和临时标签，我不打算全部发布。

这是我的函数：

CREATE FUNCTION returnTotalSum(@clientID VARCHAR(20),@type INT,@currency VARCHAR(20),@date VARCHAR())
RETURNS INT
AS
BEGIN
DECLARE @totalSum BIGINT;
SET @totalSum = ( SELECT SUM (CONVERT(DECIMAL(18,4),P.iznos*(1-P.dp)/2))
FROM pts as p
INNER JOIN tippart t on p.tip = @type
INNER JOIN its i on p.partija = @clientID                      
WHERE p.currency = @currency and pts.dknizenja < @date
GROUP BY p.partija )

RETURN @totalSum
END

我在这里使用它：(在 WHERE 子句中的最后一个 AND)

... 
880001,
NULL, 
NULL,
NULL, 
NULL,
CONVERT(INT,REPLACE('2017.12.31','.',''))                 
FROM ITS I WITH(NOLOCK)      
JOIN TIPPART R WITH(NOLOCK) ON I.TIP = R.TIP       
LEFT JOIN #UNPVT_TIPSTOPE_TS T (NOLOCK) ON I.KAMGRUPA = T.GRUPA AND I.TIP = T.TIP AND I.VALUTA = T.SIFVAL   
LEFT JOIN #UNPVT_TIPSTOPE_TS T1 (NOLOCK) ON I.KAMGRUPA = T1.GRUPA AND I.TIP = T1.TIP AND I.VALUTA = T1.SIFVAL AND T.GRUPA IS NULL         
LEFT JOIN #TMP_DODATNA_KS DS (NOLOCK) ON I.PARTIJA = DS.PARTIJA AND I.VALUTA = DS.SIFVAL AND I.KAMGRUPA = DS.GRUPA
LEFT JOIN #NML_RATE N (NOLOCK)  ON I.TIP = N.TIP AND N.SIFVAL = I.VALUTA AND N.GRUPA = I.KAMGRUPA
-- LEFT JOIN TIPSTOPE TS (NOLOCK) ON I.TIP = TS.TIP AND TS.GRUPA = I.KAMGRUPA AND TS.SIFVAL = I.VALUTA
LEFT JOIN #NML_RATE_PERIOD NML (NOLOCK) ON I.TIP = NML.TIP AND I.VALUTA = NML.SIFVAL AND NML.GRUPA = I.KAMGRUPA AND NML.SIFVAL = I.VALUTA
--WHERE NOT EXISTS (SELECT * FROM [dbo].[IC_INPT_AR_X_INT_RATE_SNPST] WHERE PARTIJA = I.PARTIJA AND VALUTA = I.VALUTA AND APP = 'ST')  
WHERE I.DOTVARANJE <= '2017.12.31'
AND (T.TIP IS NOT NULL 
OR T1.TIP IS NOT NULL 
OR DS.PARTIJA IS NOT NULL)
AND dbo.returnTotalSum(i.partija,r.tip,t.sifval,i.dotvaranje) <> 0

我认为这样做的问题在于它必须遍历每条记录，比较，评估条件。 考虑到表中没有索引(我无法添加索引，因为我没有权限)，它往往会永远运行。

我能做些什么来提高这个性能 功能，您对使用其他东西有什么建议吗 功能什么？

Answer 1

在您的实现中，调用函数作为过滤的一部分会导致巨大的性能消耗，可能是由于多个索引扫描。

作为一般指导，如果您在没有该功能的情况下很好地实现了这一点，您应该能够减少它。

如果没有数据结构和样本数据，将很难为您提供准确的解决方案。

尝试以下代码：

... 
880001,
NULL, 
NULL,
NULL, 
NULL,
CONVERT(INT,REPLACE('2017.12.31','.','')) 
FROM ITS I WITH(NOLOCK)      
JOIN TIPPART R WITH(NOLOCK) ON I.TIP = R.TIP       
LEFT JOIN #UNPVT_TIPSTOPE_TS T (NOLOCK) ON I.KAMGRUPA = T.GRUPA AND I.TIP = T.TIP AND I.VALUTA = T.SIFVAL   
LEFT JOIN #UNPVT_TIPSTOPE_TS T1 (NOLOCK) ON I.KAMGRUPA = T1.GRUPA AND I.TIP = T1.TIP AND I.VALUTA = T1.SIFVAL AND T.GRUPA IS NULL 
LEFT JOIN #TMP_DODATNA_KS DS (NOLOCK) ON I.PARTIJA = DS.PARTIJA AND I.VALUTA = DS.SIFVAL AND I.KAMGRUPA = DS.GRUPA
LEFT JOIN #NML_RATE N (NOLOCK)  ON I.TIP = N.TIP AND N.SIFVAL = I.VALUTA AND N.GRUPA = I.KAMGRUPA
-- LEFT JOIN TIPSTOPE TS (NOLOCK) ON I.TIP = TS.TIP AND TS.GRUPA = I.KAMGRUPA AND TS.SIFVAL = I.VALUTA
LEFT JOIN #NML_RATE_PERIOD NML (NOLOCK) ON I.TIP = NML.TIP AND I.VALUTA = NML.SIFVAL AND NML.GRUPA = I.KAMGRUPA AND NML.SIFVAL = I.VALUTA
--WHERE NOT EXISTS (SELECT * FROM [dbo].[IC_INPT_AR_X_INT_RATE_SNPST] WHERE PARTIJA = I.PARTIJA AND VALUTA = I.VALUTA AND APP = 'ST')  
LEFT OUTER JOIN -- Added this join with same logic from function rather than calling a function. 
(
SELECT SUM (CONVERT(DECIMAL(18,4),P.iznos*(1-P.dp)/2)) TotalSum
FROM pts as p
INNER JOIN tippart t on p.tip = r.tip
INNER JOIN its i on p.partija = i.partija     
WHERE p.currency = t.sifval and pts.dknizenja < i.dotvaranje
GROUP BY p.partija 
) SumTable
WHERE I.DOTVARANJE <= '2017.12.31'
AND (T.TIP IS NOT NULL 
OR T1.TIP IS NOT NULL 
OR DS.PARTIJA IS NOT NULL)
AND SumTable.TotalSum <> 0  -- This is similar to your old logic where you were comparing with function output. 

查询说明：

• 我已经SumTable中添加了具有现有查询逻辑的左外部连接。
• 由于附加的左外连接按p.partija分组，因此不会弄乱您的结果集。
• 旧函数的所有输入都已替换为相关值，因为我们在这里进行内联查询。
最后，改进了被
• 指示为性能消耗罪魁祸首的 where 部分，并且不会调用函数，而是使用SumTable.TotalSum并将其与 0 进行比较。

Answer 2

转换为内联表值函数的函数应该接近这个值。

CREATE FUNCTION returnTotalSum
(
@clientID VARCHAR(20)
,@type INT
,@currency VARCHAR(20)
,@date VARCHAR(10) --Don't store dates as strings...
)
RETURNS TABLE AS RETURN
SELECT TotalSum = SUM(CONVERT(DECIMAL(18,4), P.iznos * (1 - P.dp) / 2))
FROM pts as p
INNER JOIN tippart t on p.tip = @type
INNER JOIN its i on p.partija = @clientID                      
WHERE p.currency = @currency 
and pts.dknizenja < @date
GROUP BY p.partija