sql - 标量函数很慢，但单独的 SQL 很快
问题描述
我有一个在 4 秒内执行的查询。执行它时，我硬编码了我要查询的项目的 ID：
DECLARE @Id UNIQUEIDENTIFIER = 'A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF'
然后我将完全相同的 SQL 转换为标量函数：
CREATE FUNCTION dbo.tmp ( @Id UNIQUEIDENTIFIER )
RETURNS INT
AS
BEGIN
--DECLARE @Id UNIQUEIDENTIFIER = 'A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF'
-- Declare variable to work with.
DECLARE @Layer0 VARCHAR(300),
@Layer1 VARCHAR(300),
@Layer2 VARCHAR(300),
@Layer3 VARCHAR(300),
@Layer4 VARCHAR(300),
@Layer5 VARCHAR(300),
@Layer6 VARCHAR(300),
@Layer7 VARCHAR(300),
@Layer8 VARCHAR(300),
@Layer9 VARCHAR(300),
@FunctionalLayer0 VARCHAR(300),
@FunctionalLayer1 VARCHAR(300),
@FunctionalLayer2 VARCHAR(300),
@FunctionalLayer3 VARCHAR(300),
@FunctionalLayer4 VARCHAR(300),
@FunctionalLayer5 VARCHAR(300),
@FunctionalLayer6 VARCHAR(300),
@FunctionalLayer7 VARCHAR(300),
@FunctionalLayer8 VARCHAR(300),
@FunctionalLayer9 VARCHAR(300),
@LayerIndex INT,
@IsLocationRow BIT,
@IsFunctionRow BIT,
@IsPhysical BIT,
@Result INT
-- Populate the variables to make querying easier to work with below.
SELECT
@Layer0 = LocationLayer0, @Layer1 = LocationLayer1, @Layer2 = LocationLayer2, @Layer3 = LocationLayer3,
@Layer4 = LocationLayer4, @Layer5 = LocationLayer5, @Layer6 = LocationLayer6, @Layer7 = LocationLayer7,
@Layer8 = LocationLayer8, @Layer9 = LocationLayer9,
@FunctionalLayer0 = FunctionalLayer0, @FunctionalLayer1 = FunctionalLayer1, @FunctionalLayer2 = FunctionalLayer2,
@FunctionalLayer3 = FunctionalLayer3, @FunctionalLayer4 = FunctionalLayer4, @FunctionalLayer5 = FunctionalLayer5,
@FunctionalLayer6 = FunctionalLayer6, @FunctionalLayer7 = FunctionalLayer7, @FunctionalLayer8 = FunctionalLayer8,
@FunctionalLayer9 = FunctionalLayer9, @LayerIndex = LayerIndex, @IsPhysical = IsPhysical,
@IsLocationRow = CASE WHEN @Layer0 IS NOT NULL THEN 1 ELSE 0 END,
@IsFunctionRow = CASE WHEN @FunctionalLayer0 IS NOT NULL THEN 1 ELSE 0 END
FROM dbo.Asset
WHERE Id = @Id
-- Get the count.
;with deletedItems AS
(
SELECT *
FROM dbo.Asset
WHERE IsActive = 0 AND IsPhysical=0
)
SELECT @Result = (SELECT SUM(IIF(d.Id IS NULL, 1, 0))
FROM dbo.Asset a
LEFT JOIN deletedItems d
ON (d.LayerIndex = 0 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,''))
OR (d.LayerIndex = 1 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,''))
OR (d.LayerIndex = 2 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,''))
OR (d.LayerIndex = 3 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'')
OR (d.LayerIndex = 4 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,''))
OR (d.LayerIndex = 5 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,''))
OR (d.LayerIndex = 6 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,''))
OR (d.LayerIndex = 7 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,''))
OR (d.LayerIndex = 8 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(d.LocationLayer8,''))
OR (d.LayerIndex = 9 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(d.LocationLayer8,'') AND ISNULL(a.LocationLayer9,'') = ISNULL(d.LocationLayer9,''))
)
WHERE
a.IsPhysical = 1
AND a.IsActive = 1
AND (@Id IS NULL OR (
a.Id != @Id AND
(
(@LayerIndex = 0 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,''))
OR (@LayerIndex = 1 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,''))
OR (@LayerIndex = 2 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,''))
OR (@LayerIndex = 3 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'')
OR (@LayerIndex = 4 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,''))
OR (@LayerIndex = 5 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,''))
OR (@LayerIndex = 6 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,''))
OR (@LayerIndex = 7 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,''))
OR (@LayerIndex = 8 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(@Layer8,''))
OR (@LayerIndex = 9 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(@Layer8,'') AND ISNULL(a.LocationLayer9,'') = ISNULL(@Layer9,''))
)
))
)
)
RETURN ISNULL(@Result, 0)
end
GO
但是当我那时：
SELECT DBO.tmp('A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF')
我的结果（相同的值）在 12 秒内返回。
什么会导致相同的代码在将其更改为标量函数时使其执行时间增加三倍？
编辑：我似乎已经修复了它。我怀疑参数嗅探问题。通过添加：
DECLARE @UseMe UNIQUEIDENTIFIER
SET @UseMe = @Id
到函数的顶部，然后在 WHERE 子句中使用 @UseMe 作为 ID ...我得到了作为 SQL 运行时获得的性能。
解决方案
如果可能，请尝试使用 inline-UDF 重写您的代码：
CREATE FUNCTION dbo.tmp ( @Id UNIQUEIDENTIFIER )
RETURNS TABLE
AS
RETURN (SELECT ISNULL(..., 0));
推荐阅读
- excel - 在excel中打开xml表
- android - 由于权限被拒绝错误，使用 ndk 构建的 android 项目失败
- python - 使用 Python 查询 DynamoDB
- ios - 如何创建任何具有毫米测量值的 UIObject？
- azure-devops - 如何识别 Azure DevOps Services 的最新稳定 REST API 版本？
- hibernate - 访问 DialectResolutionInfo 和 Hibernate
- javascript - 在 Node.js 问题中使用 Telegraf 的 Telegram 机器人
- excel - 使用 VBA 的 Z 因子迭代
- javascript - webpack 无法启动 react 项目
- flutter - Flutter：将 CustomPainter 与 ScrollController 绑定