主页 / dba / 问题 / 183486
Accepted
Nate
Asked: 2017-08-15 14:19:31 +0800 CST

视图上的 SELECT COUNT(*) 比同一视图上的 SELECT * 慢几个数量级

  • 772

风景

CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS 

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM 
    products as p 
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

为简单起见，假设productMetadatahas 列ProductId, MetaId, Value有大约 87m 行，并且表中有大约 400k 行products。

针对此视图的一般查询非常有效：

SELECT * FROM vProductList WHERE CalculatedPrice > 500

查询结果在 2-4 秒内（通过 vpn 和远程，所以我很擅长）。

将上述更改为计数同样快：

SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

与原始选择大约在同一时间运行，我再次同意。大约有 10k 种产品符合此标准。

我遇到了两个不同的情况，事情变得非常奇怪并且永远持续下去。

第一的

对视图中基表中的一列进行查询：

SELECT * FROM vProductList WHERE Name = 'Hammer'

这个查询需要一点时间来运行（20-30 秒）并返回大约 30k 的结果；但是，对所述查询稍作更改：

SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer'

需要 13 分钟才能返回一个说明 ~30k 的计数。

第二

执行WHERE IN子查询

SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

此查询返回约 300k 行，需要两分钟才能返回（我相信大部分时间只是花在将数据下载到 SSMS 中）；但是，将其更改为SELECT COUNT(*)需要 20 分钟的查询。

SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

为什么它SELECT *比 快SELECT COUNT？

我将 SSMS 提供的总执行时间用于此处列出的所有时间。

执行计划

计划SELECT 1 FROM v WHERE IN (...)

计划SELECT COUNT(0) FROM v WHERE IN (...)

注意：我尝试使用 PasteThePlan，但它一直告诉我的计划是无效的 xml。

performance view

1 个回答

  1. Best Answer

    从提供的执行计划中，对于COUNT优化器围绕最终连接选择本地/全局聚合策略的情况：

    最终加入

    不幸的是，优化器高估了本地聚合的有效性。它估计有 136 行驱动嵌套循环连接，而在运行时遇到 366,115 行。

    估计

    对于本地 SQL Server 实例而言，366,115 次索引查找可能不是什么大问题，但计划中包含的等待统计信息显示了当前 Azure SQL 数据库配置的 I/O（可能还有内存）限制：

    等待统计

    该计划SELECT 1显示了一个专门的哈希和合并连接策略，在这种情况下，它会在内存和/或 I/O 能力非常有限的情况下产生更好的结果。

    您可能会看到带有OPTION (HASH JOIN, MERGE JOIN)提示的第一个查询的性能更好，但根本问题是由大量连接驱动的较差的基数/数据分布估计。

    不要被针对每个计划运算符显示的成本百分比所误导 - 这些数字目前来自优化器对成本的估计（使用抽象模型）。这些数字不反映运行时条件或成本。

    估计行数和实际行数之间的较大偏差通常会导致问题。对于导致优化器选择在特定硬件设置上不能很好扩展的策略的低估尤其如此。

    • 5

相关问题