sql - 使用 OPTION (RECOMPILE) 与加入表的成本
问题描述
使用:SQL Server 2016+
我一直在寻找是否有任何方法可以评估 SQL Server 重新编译查询的执行计划需要多长时间。我们有几个存储过程,我们根据表 2 中的可空参数从表 1 中进行选择。例如,如果客户不为空,则返回他们的销售额,否则返回所有销售额。
样本数据:
DROP TABLE IF EXISTS dbo.TestTable1;
DROP TABLE IF EXISTS dbo.TestTable2;
CREATE TABLE dbo.TestTable1 (ID INT NOT NULL PRIMARY KEY CLUSTERED , TextValue NVARCHAR(255) NULL);
CREATE TABLE dbo.TestTable2 (ID INT NOT NULL PRIMARY KEY CLUSTERED , TextValue NVARCHAR(255) NULL);
INSERT INTO TestTable1 (ID, TextValue)
VALUES (1, N'Table 1 - Text 1'),
(2, N'Table 1 - Text 2'),
(3, N'Table 1 - Text 3'),
(4, N'Table 1 - Text 4'),
(5, N'Table 1 - Text 5'),
(6, N'Table 1 - Text 6'),
(7, N'Table 1 - Text 7'),
(8, N'Table 1 - Text 8'),
(9, N'Table 1 - Text 9'),
(10, N'Table 1 - Text 10');
INSERT INTO TestTable2 (ID, TextValue)
VALUES (1, N'Table 2 - Text 1'),
(2, N'Table 2 - Text 2'),
(3, N'Table 2 - Text 3'),
(4, N'Table 2 - Text 4'),
(5, N'Table 2 - Text 5'),
(6, N'Table 2 - Text 6'),
(7, N'Table 2 - Text 7'),
(8, N'Table 2 - Text 8'),
(9, N'Table 2 - Text 9'),
(10, N'Table 2 - Text 10');
这大大简化了,因为我们将有多个可能的条件,链接到多个表。我们目前正在考虑重新编译查询,以便仅在需要时才连接到辅助表。
DECLARE @LookupValue NVARCHAR(50);
SET @LookupValue = NULL;
SELECT *
FROM dbo.TestTable1 T1
WHERE @LookupValue IS NULL
OR EXISTS ( SELECT TOP (1) 1 A FROM dbo.TestTable2 T2 WHERE T1.ID = T2.ID AND T2.TextValue = @LookupValue)
OPTION (RECOMPILE)
SET @LookupValue = N'Table 2 - Text 1';
SELECT *
FROM dbo.TestTable1 T1
WHERE @LookupValue IS NULL
OR EXISTS ( SELECT TOP (1) 1 A FROM dbo.TestTable2 T2 WHERE T1.ID = T2.ID AND T2.TextValue = @LookupValue)
OPTION (RECOMPILE);
从下面的查询计划中可以看出,重新编译表 2 被有效地从执行中删除。
然而,重新编译是有代价的,我正在寻找这种开销,以便我可以做出明智的决定,以这种方式格式化我们的哪些查询。一般来说,我看到重新编译总是更快,但有很多帖子指出这意味着执行计划可能远不如最优。
任何关于衡量这些开销的指导或我在更广泛地实施之前应该调查的任何问题都将不胜感激。
非常感谢。
解决方案
要了解编译时间,请查看:
https://ericblinn.com/quantifying-compile-time
基本上,在查询之前使用 SET STATISTICS TIME ON 来获取有关编译和执行所用时间的控制台消息。
作为旁注,不是您的问题,担心编译时间可能不是最有效的行动方案。恕我直言,相当低级的引擎盖下的东西和不可预测的东西。
如果您有两个非常不同的查询模式,也许最好创建两个(或更多)不同的存储过程,由一个基于条件的条目门控,每个都有自己的模式(包含或删除可空参数)并让优化器安静地工作。
在受到持续事务影响的情况下强制重新分析和重建计划可能不是最明智的选择。
另外,请查看以下博客:
它对可能有用的 OPTION(OPTIMIZE FOR (@string = '')) 有一些见解。
但是,如前所述,我从中得出的结论不是使用重新编译,而是设计数据访问过程,尽可能避免重新编译的必要性。
推荐阅读
- kotlin - Kotlin - create an object based on sum
- javascript - 将ajax变量值分配给输入字段
- python - Converting list of JSON pages to one object
- python - to_numpy() 与 .values 之间的区别
- python - How to downcast numpy.ndarray
- python - Efficient quaternions to Euler transformation
- forms - 如何将辅助值应用于输入元素
- excel - 带有数组的 AutoFilter
- python - 尝试在 Python 中使用一些即时生成的代码时出现问题
- java - How can i get a list of Media class and it returns two entities?