使用选项(重新编译)与连接到表的开销
使用: 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被有效地从执行中删除。
然而，重新编译是有成本的，我希望跟踪这个开销，这样我就可以做出明智的决定，以这种方式格式化我们的查询。一般来说，我看到重新编译总是更快，但有很多帖子说，这意味着执行计划可能远远不是最优的。
任何关于测量这些管理费用的指导，或者在更广泛地实施之前我应该调查的任何问题，我们都将不胜感激。
非常感谢。
回答 2
Stack Overflow用户
发布于 2019-04-29 03:32:07
您可以执行您所要求的操作，而无需重新编译每次都会强制其生成新计划的选项。您希望尽您所能不将分支逻辑放在where子句中，因为where子句只会使引擎崩溃，试图找出如何为您的两种场景创建一个合适的计划，而这是它无法做到的，并且只会造成性能问题。您要做的是创建一个分支逻辑，以便有两个不同的查询，每个查询都可以有自己的执行计划。您可以使用以下内容来执行此操作。
Declare @customerid int
Set @customerid = (select customerid from dbo.table2)
If @customerid is null
BEGIN
Select datadesired from table1
END
ELSE
BEGIN
Select datadesired from table1
INNER JOIN table2 ON PKey = FKey
WHERE customerid = @customerID
END如果您想要提取所有数据或特定的一组数据，这将非常有效。如果您像您建议的那样变得更复杂，那么动态SQL可能是更好的选择。您是否仍然可以使用此方法为每个场景创建查询？当然，如果你想的话。但是我几乎可以保证，如果你尝试使用分支where子句逻辑来创建两个完全不同的查询，然后给它提供任何大量的数据，你将会遇到问题，但是如果两个不同的查询都存在于不同的存储过程中，并且你找出了在web层调用哪个过程(也是可能的)，那么两个不同的查询同样有效。
Stack Overflow用户
发布于 2019-04-29 04:53:21
要深入了解编译时间，请查看：
https://ericblinn.com/quantifying-compile-time
基本上，在查询之前使用SET STATISTICS TIME ON来获取关于编译和执行所用时间的控制台消息。
顺便说一句，担心编译时间可能不是最有效率的做法。相当低级的幕后工作和不可预知的东西。
如果您有两个截然不同的查询模式，那么创建两个(或更多)不同的存储过程可能会更好，这些存储过程由一个条目根据条件控制，每个条目都有自己的模式(包括或删除可为空的参数)，并让优化器平静地工作。
强制重新解析和重建计划，同时受到连续事务的影响，可能不是最明智的选择。
此外，请查看以下博客：
它对选项(OPTIMIZE FOR (@string = ''))有一些见解，这可能是有用的。
但是，如前所述，我从中得出的结论不是使用重新编译，而是设计数据访问过程，以避免在可能的情况下它的必要性。
https://stackoverflow.com/questions/53741064
复制相似问题