大型查询下的SQL Server性能表现

Question

大家好，我有几个关于报告的查询需要翻译，每个查询都从35个以上的表中提取数据。每个表几乎有10万条记录。所有查询都使用Union ALL，例如：

;With CTE
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table3 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table4 WHERE Some_Condition
.
.
. And so on 
)
SELECT col1, col2, col3 FROM CTE
ORDER BY col3 DESC

目前我只在开发服务器上测试过这个查询，我发现获取结果需要一定的时间。这35个以上的表之间没有任何关联，这是我能想到的获取所有所需数据的唯一方式。

1. 有更好的方法来执行这种查询吗？

2. 如果这是执行此类查询的唯一方法，如何通过进行任何可能的更改来提高此查询的性能？

我的观点
在此报告中，我不介意使用一些脏读取。我正在考虑使用查询提示 with nolock 或将事务隔离级别设置为READ UNCOMMITED。

这些方法是否有帮助？

编辑
每个表都有5-10个二进制列和与每个二进制列对应的日期列，每个SELECT语句的条件都类似于：

WHERE BitColumn = 1 AND DateColumn IS NULL 

同行建议

筛选索引

CREATE NONCLUSTERED INDEX IX_Table_Column
ON TableName(BitColumn)
WHERE BitColum = 1

包含列的过滤索引

CREATE NONCLUSTERED INDEX fIX_IX_Table_Column
ON TableName(BitColumn)
INCLUDE (DateColumn)
WHERE DateColumn IS NULL

这是最好的方法吗？还是有其他建议？

- M.Ali

---

你确定这些表没有关联吗？很难想象如何将完全不相关的数据进行合并。 - Andrew

你看过查询的执行计划了吗？35个以上的表是否都有适当的索引可用？ - Ian Nelson

听起来对我来说是次优设计，但如果你确实需要在35个表中检查条件，我不知道还有什么其他方法。这个条件总是相同的吗？也许您可以创建一个与谓词匹配的过滤索引。否则，* 耸肩 *。[另外，请不要在一个问题上@提醒别人去看另一个问题。] 这会破坏该网站大多数用户的整个工作流程。 - Aaron Bertrand

是的，这个查询/报告将经常执行，因为每个员工都必须跟踪所有客户案例的情况。 - M.Ali

我不确定还有什么其他人可以告诉你。你可以查看解释，看看是否有任何单独的查询需要很长时间。我认为SQL Server会一次运行所有这些查询。也许你可以使用SSIS并行运行它们或其中的一部分？ - Andrew

Answer 1

有很多方法可以使查询变快。如果你需要执行这些UNIONs，那么你可以通过以下方式加速查询：

1. 缓存结果，例如，
   • 你能否从整个语句中创建一个索引视图？或者有很多不同的WHERE条件，因此会有很多索引视图？但要知道这将减慢对这些表的修改（INSERT等）。
   • 你能否以不同的方式进行缓存？也许在中间层中？
   • 它可以提前重新计算吗？
2. 创建覆盖索引。前导列是来自WHERE的列，然后是查询中的所有其他列作为包含的列。
   • 请注意，覆盖索引也可以被过滤，但如果查询中的WHERE具有变量/参数，并且它们可能具有未被过滤索引覆盖的值（即行没有被覆盖），则不使用过滤索引。
3. ORDER BY将导致排序：
   • 如果可以缓存，则无需排序（已缓存排序）
   • 否则，排序是CPU绑定的（如果不在内存中，则是I/O绑定的）。为了加快排序速度，你是否使用快速排序规则？最慢和最快排序规则之间的性能差异甚至可以达到3倍。例如，SQL_EBCDIC280_CP1_CS_AS、SQL_Latin1_General_CP1251_CS_AS、SQL_Latin1_General_CP1_CI_AS是最快的排序规则之一。但是，如果我不知道你需要的排序特性，很难做出建议。
4. 网络：
   • 用于执行SELECT的连接的“network packet size”应该尽可能大-如果结果集（行数）很大，则为32,767字节。这可以在客户端端设置，例如，如果使用.NET和SqlConnection连接字符串。这将最小化从SQL Server发送数据时的CPU开销，并且将改善客户端和服务器两端的性能。如果网络是瓶颈，则这可以将性能提高甚至达到几十个百分点。
   • 如果客户端在SQL Server上，请使用共享内存端点；否则使用TCP/IP以获得最佳性能。
5. 常规事项：
   • 如你所说，使用未提交读取级别将提高性能。

...

也许除了重写查询等行为外，你无法进行更改，但以防万一，在不足的情况下添加更多内存，或者使用SQL Server 2014中的内存特性：-），...肯定会有所帮助。

有太多可以调整的东西了，但如果问题不是非常具体，很难指出关键点。

希望这有所帮助。

- E.K.

Answer 2

查询提示或隔离级别只有在出现阻塞时才能帮助您。如果您不介意脏读并且在执行期间存在锁定，则可能是一个好主意。

关键问题是需要使用的 Where 子句中有多少数据适合（WHERE BitColumn = 1 AND DateColumn IS NULL）。如果由此筛选出的子集与总行数相比较小，则在两个列，即 BitColum 和 DateColumn 上使用索引，并将这些列包括在 select 子句中，以避免查询计划中的“Page Lookup”操作。

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(BitColumn, DateColumn)
INCLUDE (col1, col2, col3)

当然，覆盖过滤索引所需的空间取决于涉及字段的数据类型和满足 WHERE BitColumn = 1 AND DateColumn IS NULL 的行数。

之后，我建议使用视图而不是公共表达式：

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
.
.
.
)

通过这样做，你的查询计划应该类似于35个小型索引扫描，但如果大部分数据都满足索引中的where子句，则性能将类似于扫描35个源表，这种解决方案不值得。 但是你说“每个表都有5-10个位列和相应的日期列…”，那么我认为针对每个位列制作一个索引可能不是一个好主意。如果需要使用不同的BitColums和不同的DateColums进行过滤，请在表中使用计算列。

ALTER TABLE Table1 ADD ComputedFilterFlag AS
CAST(
    CASE WHEN BitColum1 = 1 AND DateColumn1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN BitColum2 = 1 AND DateColumn2 IS NULL THEN 2 ELSE 0 END +
    CASE WHEN BitColum3 = 1 AND DateColumn3 IS NULL THEN 4 ELSE 0 END
AS tinyint)

我建议您在条件X（BitColumnX = 1且DateColumnX不为空）中使用值2^(X-1)。这将允许您使用任何该条件的组合进行过滤。 使用值3，您可以定位满足以下所有行：Bit1，Date1和Bit2，Date2条件。任何条件组合都有其相应的ComputedFilterFlag值，因为ComputedFilterFlag充当条件的位图。 如果您少于8个不同的过滤器，则应使用tinyint来节省索引空间并减少所需的IO操作。

然后在ComputedFilterFlag列上使用索引：

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(ComputedFilterFlag)
INCLUDE (col1, col2, col3)

并创建视图：

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
.
.
.
)

通过这样做，您的索引涵盖了所有条件，查询计划应该看起来像35个小的索引查找。

但这是一个棘手的解决方案，也许在您的表模式中进行重构可以产生更简单和更快的结果。

- Santiago Regojo

Answer 3

由于您没有提供任何统计数据或执行时间的样本，因此很难猜测哪些部分缓慢以及是否真的缓慢。结果集中有多少数据？可能只是检索了100K行，需要一些时间。如果结果集中有10000行，并且需要5分钟，那么肯定需要检查一下。因此，如果您有示例查询、结果中的行数以及不同where条件下的执行时间，请发布它们。这将帮助我们比较结果。

顺便说一句，请勿使用CTE，只需使用常规的内部和外部查询选择即可。确保已正确配置Temp DB。LDF和MDF的默认配置不会增加10％。通过尝试和错误，您将了解到对于各种范围查询应该增加多少日志和Temp DB大小，并根据此设置Temp DB的初始和增量大小的MDF和LDF。对于覆盖筛选器索引，包含列应为col1、col2和co3，而不是列Date，除非Date也在选择列表中。

这35个原始表中的数据更新频率如何？如果最多每天更新一次，或者它们几乎同时更新，那么索引视图可能是一个可行的解决方案。但如果原始表更新超过一天一次，或者它们随时都有更新且没有同步，就不要考虑索引视图。

如果磁盘空间不是问题，作为最后手段，可以在每个35个表上使用触发器进行测试性能。创建新表以保存从此选择查询中期望的最终结果。在每个35个表上创建插入/更新/删除触发器，在触发器内部检查条件，如果是，则仅复制相同的插入/更新/删除到新表中。是的，你需要在新表中有一列来标识来自哪个表的数据。因为日期是可空列，所以你不能充分利用该列上的索引，因为“大多数情况下，你要查找的是WHERE Date is NULL”。

在新表中，你只需要查询Date为NULL的数据，那么甚至不需要创建该列，只需创建BIT列和其他col1、col2、col3等列即可。如果你提供实际查询的示例并解释实际表格，其他细节可以稍后处理。

- Anup Shah

Answer 4

如果您使用联合查询在多个表上进行操作，那么您将无法获得实时结果，但我可以告诉您如何从类似的情况中获得一些速度提升。希望这能对您有所帮助。

您可以通过一点编码和创意来同时运行它们。

您可以创建一个全局临时表，而不是常规表达式，并且不要在全局临时表上放置任何键，因为这样只会减慢操作速度。然后开始所有单独的查询，这些查询将插入到全局临时表中。我已经手动执行过这种方法大约100次，它比使用联合查询更快，因为您可以在每个CPU核心上运行查询。棘手的部分是确定单独查询何时完成，您必须自己处理这一部分，因此我手动执行这些操作。

- danny117