我一直在对SQL存储过程进行故障排除,该过程一直挂起并且无法完成执行。我将问题简化为声明的两个变量。下面显示的 SQL 查询是存储过程的一个片段。以下是我执行的测试用例场景。
- 将 WHERE 子句中的 @startver 和 @endver 替换为 56 并且它起作用了。查询在不到一秒的时间内完成。
- 在 WHERE 子句中将@endver替换为@starver,它起作用了。查询在不到一秒的时间内完成。
- 在 WHERE 子句中将@starver替换为 @endver 并且它起作用了。查询在不到一秒的时间内完成。
当两个变量放在一起时,它不起作用。它不断显示
正在执行查询...
有谁知道为什么会这样?我该如何解决这个问题?我无法调试它,因为 SQL 实例在 AWS 中,我没有系统管理员帐户。
DECLARE @startver int = 56, @endver int = 56
SELECT
Z_Prodver.ProdverID ProdverID,
Prod.ProdName,
Z_Prodver.Releasedver,
Z_DNameLen.Z_DName Z_DName,
Z_DItemOffsets.Offset DItemOffset,
Z_DNameLen.Offset DNameIndex,
(Z_FormType.storagesize * M.ASize * DItem.ASize *
CASE DType.grouped
WHEN 0 THEN 1
ELSE 8
END
) AllocationSize,
H_NAME()
FROM
Z_Prodver,
Prod,
DItem,
DType,
M,
Z_DNameLen,
Z_DItemOffsets,
ProdPerM,
Z_ProdSupport,
Form,
Z_FormType
WHERE
DItem.ProdverID >= @startver
AND DItem.ProdverID <= @endver
AND DItem.DName = Z_DNameLen.DName
AND DType.DTypeName = DItem.DTypeName
AND DType.ProdverID = DItem.ProdverID
AND M.MName = DItem.MName
AND M.ProdverID = DItem.ProdverID
AND M.MName = ProdPerM.MName
AND M.ProdverID = ProdPerM.ProdverID
AND ProdPerM.ProdverID = Z_Prodver.ProdverID
AND Z_DItemOffsets.ProdverID = Z_Prodver.ProdverID
AND Prod.ProdName = ProdperM.ProdName
AND Z_DItemOffsets.DName = DItem.DName
AND Form.FormName = DItem.FormName
AND Z_FormType.FormTypeName = Form.FormTypeName
AND Form.ProdverID = DItem.ProdverID
AND Z_DItemOffsets.HostName = H_NAME()
AND Z_ProdSupport.ProdverID = M.ProdverID
AND Z_ProdSupport.ProdName= Prod.ProdName
AND Z_ProdSupport.PCSupport = 1
更新:使用正确的内部连接重写查询后,我仍然收到相同的错误。
DECLARE @startversion int = 56, @endversion int = 56
SELECT
PC_ProductVersion.ProductVersionID AS ProductVersionID,
Product.ProductName,
PC_ProductVersion.ReleasedVersion,
PC_DataNameLengths.PC_DataName AS PC_DataName,
PC_DataItemOffsets.Offset AS DataItemOffset,
PC_DataNameLengths.Offset AS ataNameIndex,
(PC_FormatType.storagesize * Module.ArraySize * DataItem.ArraySize *
CASE DataType.grouped
WHEN 0 THEN 1
ELSE 8
END
) AllocationSize,
HOST_NAME()
FROM DataItem
INNER JOIN PC_DataNameLengths
ON DataItem.DataName = PC_DataNameLengths.DataName
INNER JOIN DataType
ON DataType.DataTypeName = DataItem.DataTypeName
AND DataType.ProductVersionID = DataItem.ProductVersionID
INNER JOIN Module
ON Module.ModuleName = DataItem.ModuleName
AND Module.ProductVersionID = DataItem.ProductVersionID
INNER JOIN ProductPerModule
ON ProductPerModule.ProductVersionID = Module.ProductVersionID
AND ProductPerModule.ModuleName = Module.ModuleName
INNER JOIN PC_DataItemOffsets
ON PC_DataItemOffsets.DataName = DataItem.DataName
AND PC_DataItemOffsets.HostName = HOST_NAME()
INNER JOIN PC_ProductVersion
ON PC_ProductVersion.ProductVersionID = PC_DataItemOffsets.ProductVersionID
AND PC_ProductVersion.ProductVersionID = ProductPerModule.ProductVersionID
INNER JOIN Product
ON Product.ProductName = ProductperModule.ProductName
INNER JOIN Format
ON Format.FormatName = DataItem.FormatName
AND Format.ProductVersionID = DataItem.ProductVersionID
INNER JOIN PC_ProductSupport
ON PC_ProductSupport.ProductVersionID = Module.ProductVersionID
AND PC_ProductSupport.ProductName= Product.ProductName
AND PC_ProductSupport.PCSupport = 1
INNER JOIN PC_FormatType
ON PC_FormatType.FormatTypeName = Format.FormatTypeName
WHERE
DataItem.ProductVersionID >= @startversion
AND DataItem.ProductVersionID <= @endversion
DItem.ProdverID >= @startver AND DItem.ProdverID <= @endver
当@startver
和@endver
相同时,这意味着所有ProdverID
此查询的一个丑陋的解决方法是:
and 1 = case when @startver = @endver and DItem.ProdverID <> @startver
then 0
else 1
end
但是整个查询需要使用适当的连接进行重写,并且使用上述解决方法将从option (recompile)
或转换为使用动态 sql 中受益匪浅。
参考:
- 参数嗅探、嵌入和重新编译选项 - Paul White
- 动态搜索条件 - 埃尔兰索马斯科格
- 包罗万象查询 - 盖尔·肖
- 更新的"厨房水槽"示例 - 亚伦·伯坦德