OFFSET和FETCH会对查询造成巨大的性能影响-包括当OFFSET =0时

Question

我有一个相当复杂的SQL查询，它从大量连接中返回大约20列，用于在UI中填充结果网格。它还使用几个CTE对结果进行预过滤。我在下面的查询中包含了一个近似值(我注释掉了修复性能的行)

随着数据库中数据量的增加，查询性能变得非常糟糕，主表'Contract‘中只有2500行。

通过实验，我发现通过删除顺序，在结束时偏移fetch，性能从大约30秒下降到只有1秒！

order by 1 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

这对我来说毫无意义。最后一行应该非常便宜，即使偏移量为零也是免费的，那么为什么它要增加29秒的查询时间呢？

为了维护SQL的相同函数，我对其进行了调整，首先选择into # temp，然后在临时表上执行上面的order-offset-fetch，然后删除临时表。这大约在2-3秒内完成。

我的“优化”感觉很不对劲，当然还有更合理的方法来达到同样的速度？

我还没有对更大的数据集进行广泛的测试，它基本上是一个快速修复，可以暂时恢复性能。我怀疑它是否会随着数据大小的增长而提高效率。

除了主键上的聚集索引之外，表上没有索引。查询执行计划似乎没有显示任何主要的瓶颈，但我不是解释它的专家。

WITH tableOfAllContractIdsThatMatchRequiredStatus(contractId) 
AS (
    SELECT DISTINCT c.id
    FROM contract c 
    INNER JOIN site s ON s.ContractId = c.id
    INNER JOIN SiteSupply ss ON ss.SiteId = s.id AND ss.status != 'Draft'
    WHERE 
        ISNULL(s.Deleted, '0') = 0 
        AND ss.status in ('saved')
)
,tableOfAllStatusesForAContract(contractId, status) 
AS (
    SELECT DISTINCT c.id, ss.status
    FROM contract c 
    INNER JOIN site s ON s.ContractId = c.id
    INNER JOIN SiteSupply ss ON ss.SiteId = s.id AND ss.status != 'Draft'
    WHERE ss.SupplyType IN ('Electricity') AND ISNULL(s.Deleted, '0') = 0 
)

SELECT 
     [Contract].[Id]
    ,[Contract].[IsMultiSite]
    ,statuses.StatusesAsCsv
    ... lots more columns
    ,[WaterSupply].[Status] AS ws

--INTO #temp

FROM 
(
    SELECT 
        tableOfAllStatusesForAContract.contractId, 
        string_agg(status, ', ') AS StatusesAsCsv  
    FROM 
        tableOfAllStatusesForAContract
    GROUP BY 
        tableOfAllStatusesForAContract.contractId
) statuses

JOIN contract ON Contract.id = statuses.contractId
JOIN tableOfAllContractIdsThatMatchRequiredStatus ON tableOfAllContractIdsThatMatchRequiredStatus.contractId = Contract.id
JOIN Site ON contract.Id = site.contractId and site.isprimarySite = 1 AND ISNULL(Site.Deleted,0) = 0
... several more joins
JOIN [User] ON [Contract].ownerUserId = [User].Id

WHERE isnull(Deleted, 0) = 0 
AND
 (
 [Contract].[Id] = '12659' 
 OR [Site].[Id] = '12659'
 ... often more search term type predicates here
  )

--select * from #temp
order by 1
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
--drop table #temp

Answer 1

我还没有得到一个答案，所以我将尝试用我对SQL工作原理的糟糕理解和Jeroen在上面的评论中的一些指示自己来解释它。这可能是不正确的，但从我所发现的情况来看，它可能是正确的，而且我确实知道如何解决我当前的问题，以便它可以帮助其他人。

我将用一个类比来解释它，因为我认为这可能正在发生：

假设你是一家餐馆的厨师，你必须准备大量的饭菜(rows in results)。你知道会有很多，因为你在房子前面已经告诉你这一点(TOP 10 or FETCH 10)。

你花时间列出了所需的大量配料(table joins)和所需的设备，当第一个订单到来时，你要确保你将真正高效。将第一个订单所需的更多切碎，放入小碗中，准备在随后的订单中使用。第一个订单需要相当长的时间(30 secs)，因为您正在提前计划，并希望后续的菜肴尽可能快地推出。

然而，当你坐在厨房等待下一个命令的时候..那就别来了。就是这样，只有一个订单。好吧，那是浪费时间！如果你只是试着拿出一道菜，你可以做得更快(1sec)，但你提前计划了一些从来不需要的东西。

第二天晚上，你抛弃了之前的策略，一次只做每个盘子。然而这一次，有100多个客户。你不能以足够快的速度交付它们，一次只做一个。如果你像前一天晚上那样提前计划，那么交付所有订单的时间会快得多。(我没有测试这个假设，但我预计可能会发生这种情况)。

对于我的查询，我不知道结果是1还是100虽然我可以根据用户输入的搜索条件预先做一些分析，但我可能必须调整我的UI以提供更多的信息，以便我可以更好地预测这一点，这意味着我可以为SQL选择适当的策略来预先使用。事实上，我对少量的结果进行了优化，目前运行良好-但我需要做一些更广泛的测试，以了解随着数据集的增长，性能是如何受到影响的。

"If you want a answer to something, post something that's wrong on the internet and someone will be sure to correct you"