这个更大的子查询怎么会更快呢？

Question

在我的公司里，理光是打印机和复印机的供应商。剪纸用于登记制作的副本和哪个账户。与理光一起工作的另一个应用程序是在一个不同的数据库中注册副本，但两者在某种程度上是相互关联的。

我的组织想要一个中央的地方来检索总成本，所以我写了一些问题。大多数情况下都很好，但是下面的那个发生了一些奇怪的事情，我无法弄清楚为什么这是失败的。以下问题的原因是什么？

由于数据库非常复杂，所以很难制作db<>fiddle。我会在这里解释这个问题。

实质上有两个主要的表。理光维护的包含客户端标识符ClientId、price (复制成本)、打印时间戳和作业标识符的表。Papercut维护的表还具有时间戳、printed标志和对其job_comment中相应的JobId的引用。

现在考虑下面的查询，它给出了使用单独的理光应用程序打印时的总打印成本。

SELECT CliendId,
       SUM(ricoh.price) AS cost
FROM
  (SELECT a.SystemId as ClientId,
          a.ProcessInternalUid as JobId,
          a.price
   FROM   ricoh.printhistory AS a
   WHERE a.Exportdate >= '2020-10-01'
     AND a.Exportdate <= '2020-12-12') AS ricoh
INNER JOIN
  (SELECT substring(b.job_comment, 16, 36) AS JobId
   FROM  papercut.printer_usage_log AS b
   WHERE b.usage_date >= '2020-10-01'
     AND b.usage_date <= '2020-12-12'
     AND b.printed = 'Y'
   GROUP BY substring(b.job_comment, 16, 36)) AS papercut ON (papercut.JobId = ricoh.JobId)
GROUP BY ricoh.CliendId

如果运行此查询，就会得到即时结果，其中子查询ricoh和papercut分别有96.321行和9.354行。

但是，如果我将日期范围的下限更改为2020-11-01，则查询需要49分钟(!)。而子查询分别有46.223和4.547条记录。

这怎么可能呢？如何使用更大的子查询连接来获得更快的结果？我对此感到很困惑。时间增长的原因是什么？(然后我可以尝试进一步调试)

我做了一些(初始)调试，并注意到以下几点：

• 有个限定日期。如果我将下限设置为比2020-10-08更高的值，那么执行时间就会变大。我带着那个时间戳去找一些麻烦的记录，但没有找到。此外，选择任何较高的日期，就像2020-12-01一样，会导致高执行时间。
• 如果删除GROUP BY聚合(和和)，则执行是瞬时的。但是，如果我添加了一个ORDER BY ricoh.price，那么执行时间就会再次增加。价格有问题吗？这是一个浮点数。我尝试将其转换为整数，但问题仍然存在。

编辑：

根据用户J.D的要求添加了执行计划。我对这些计划并不太熟悉。我想主要的区别是Hash Match？

• 快速查询：执行计划
• ‘'Slow’查询：执行计划

此外，此查询正在SQL server 2012上运行。而且，由于数据库是由外部公司管理的，所以我不能添加索引。是否有一种方法可以重写查询以强制执行“快速”计划？

Answer 1

几乎可以保证您正在经历的是Execution Plan中的更改，从而导致发生不同的操作(例如，Index Scan而不是Index Seek)，因为对于一个日期范围返回的数据与另一个日期范围返回的数据有一个不同的cardinality。

Cardinality是从要查询的表返回的结果的唯一性。Server的引擎使用cardinality来确定在查找和服务数据时最好使用哪种类型的操作。然后生成一个Execution Plan，这是它需要进行的为数据服务的全部操作集。

cardinality是根据您在WHERE、JOIN和HAVING子句中使用的predicates确定的。

例如，假设您有一个Cars表，其中10条记录和Manufacturer列，其中8条Manufacturer设置为“本田”，2条设置为"BMW“。“宝马”是一个比“本田”更独特的价值表。因此，如果查询要执行搜索Index Seek，则D15是查找和服务数据的一种性能更好的操作。但是“本田”在Cars表中是一个更常见的值，如果您将查询更改为WHERE Manufacturer = 'Honda'，那么Index Scan操作将具有更高的性能(获取数据的Execution Plan将发生变化)。对于所有类型的列(日期、it、浮点数等)，都是这样工作的。

Server将statistics存储在这些值上，以帮助确定cardinality，但有时它会在为数据服务时对cardinality进行错误估计，从而导致不太理想的execution plans。没有具体地看到您的execution plans，这是我们所能猜测的最好的问题，但它很可能是一个cardinality estimate问题。

最重要的是，返回的数据量并不是唯一影响查询性能的因素，实际上，这并不是性能不佳的原因。在提供数据时，引擎引擎罩下会发生更多的事情。

根据您最新的问题，以下是与上述段落有关的一些附加信息。

以下是慢速查询的execution plan中最相关的部分：

请注意，这里的箭头更粗，这是因为在这一步返回的实际数据量比之前在这一步返回的数据量要多得多。但是，不要停留在数据量上，更大的问题是，如果您查看"Actual的行数“，它将返回4,048,577行，但在"Estimated行数”下，它认为只返回1行。这就是我提到的cardinality estimate issue正在发生的地方。(这两个数字可以相差一点点，但通常情况下，如果差一个或更大，cardinality estimate就会出现明显的问题。)

像这样的cardinality estimate问题导致性能问题的原因之一是，它们导致引擎请求错误数量的硬件资源来服务数据。也就是说，通过低估这个步骤将返回的行数，它可能严重地低估了处理此操作所需的内存和CPU。

当函数在cardinality estimate、WHERE和HAVING子句中被用作predicates时，就会发生可能导致JOIN问题的事情。在仔细查看查询之后，我发现您正在使用SUBSTRING()从papercut.printer_usage_log表中获取一个表示D37的值，然后在执行ON (papercut.JobId = ricoh.JobId)时将该值连接到子查询之外。这可能是cardinality estimate问题的根源。如果您有其他方法获得不涉及使用函数的JobId (或者至少可以尝试另一个函数)，那么您可能能够解决您的问题。

您可以尝试这个逻辑上等效的查询重写，看看它是否为您提供了更好的execution plan。唯一的缺点是它不再是单语句查询，而是利用TempDB。我不知道到底是什么在消耗您的查询(View、Stored Procedure、Report / Application?)因此，可能有一些限制阻止您这样做，但如果不这样做，那么这可能解决您的问题：

-- Materializing your logic for Ricoh and Papercut into their own TempTables
SELECT 
    a.SystemId as ClientId,
    a.ProcessInternalUid as JobId,
    a.price
INTO #Ricoh
FROM ricoh.printhistory AS a
WHERE a.Exportdate >= '2020-10-01'
    AND a.Exportdate <= '2020-12-12'

-- This materializes the SUBSTRING() function call's results to a TempTable so that when we join on JobId later on, we're not joining by a function call rather we're joining by the actual value itself
SELECT SUBSTRING(b.job_comment, 16, 36) AS JobId 
INTO #Papercut
FROM  papercut.printer_usage_log AS b
WHERE b.usage_date >= '2020-10-01'
    AND b.usage_date <= '2020-12-12'
    AND b.printed = 'Y'
GROUP BY SUBSTRING(b.job_comment, 16, 36)

-- Indexing the aforementioned temp tables
CREATE CLUSTERED INDEX IX_Ricoh_JobId ON #Ricoh (JobId)
CREATE CLUSTERED INDEX IX_Ricoh_JobId ON #Papercut (JobId)

-- Final select
SELECT 
    #Ricoh.CliendId,
    SUM(#Ricoh.price) AS cost
FROM #Ricoh
INNER JOIN #Papercut 
    ON (#Papercut.JobId = #Ricoh.JobId)
GROUP BY #Ricoh.CliendId

如果您能够使用这个多语句查询，那么让我知道它的工作原理，您也可以上传新的execution plan作为我们的参考。

我仍然在分析您的execution plans之间的差异，并将很快提出解决方案。

要查看Execution Plan，可以像这样在中启用它(然后运行查询)：