SQL函数非常慢

Question

我有一个SQL函数，用于在另一个视图中返回单个值，这个函数有时在较大的数据库上需要超过30秒，我认为它可能会一遍又一遍地运行?

老实说，我只是在这一点上失去了理智，需要帮助。有人知道优化这个的最佳方法吗?

T-SQL函数是这样的:

IF OBJECT_ID('Base.fn_AssetPriority') IS NOT NULL
DROP FUNCTION Base.fn_AssetPriority
GO
CREATE FUNCTION Base.fn_AssetPriority 
(@LID BIGINT)
RETURNS NVARCHAR(20)
WITH EXECUTE AS CALLER
AS 
BEGIN
DECLARE @Priority NVARCHAR(20)
DECLARE @RGID     BIGINT
DECLARE @CP TABLE 
(
ConsequenceAssessmentID BIGINT,
[Sign] VARCHAR(2),
Score DECIMAL(18,2),
AssetPriority CHAR(1),
ConsNo INT
)
SET @Priority = 'Not Allocated'
INSERT INTO @CP
SELECT  
ConsequenceAssessmentID, [Sign], Score, AssetPriority, ConsNo
FROM    
Base.ConsequencePriority
ORDER BY
ConsNo DESC

SELECT @RGID = MAX(ID)
FROM ACA.ReviewGroup
WHILE EXISTS (SELECT * FROM @CP) 
BEGIN
DECLARE @CAID BIGINT
DECLARE @ConsNo INT
DECLARE @Sign VARCHAR(2)
DECLARE @Score DECIMAL(18,2)
DECLARE @AP CHAR(1)
SELECT TOP 1
@CAID   = ConsequenceAssessmentID,
@ConsNo = ConsNo,
@Sign   = [Sign],
@Score  = Score,
@AP     = AssetPriority
FROM    
@CP
ORDER BY
ConsNo DESC
IF @Sign = '=' 
BEGIN
IF EXISTS (SELECT * FROM ACA.ConsequenceAssessment 
WHERE LID = @LID AND RGID = @RGID 
AND BaseCAID = @CAID AND Score = @Score) 
BEGIN
SET @Priority = @AP
BREAK
END
END
ELSE BEGIN
IF EXISTS (SELECT * FROM ACA.ConsequenceAssessment 
WHERE LID = @LID AND RGID = @RGID 
AND BaseCAID = @CAID AND Score >= @Score) 
BEGIN
SET @Priority = @AP
BREAK
END
END
DELETE FROM @CP 
WHERE ConsequenceAssessmentID = @CAID 
AND ConsNo = @ConsNo
END
RETURN @Priority
END

有另一个视图将其作为字段调用:

Base.fn_AssetPriority(BaseAS.ID) AS AssetPriority,

我到底该如何优化它?或者让它运行得快一点?

Answer 1

存储函数的执行计划可能过时了。

试着这样做并重新运行它。

EXEC sp_recompile N'Base.fn_AssetPriority';  

如果它变得更快，您可能希望每隔一段时间就运行重新编译。可以每天用一个job重新编译。

你可能不想把WITH RECOMPILE放在函数的定义中，因为你经常使用它，而重新编译的原因是改变了它查询的表中的统计信息。