更快地计算每月库存在巨大桌子上的速度

Question

我有一张桌子上有成千上万的行。每个与列出的商品以及将物品放在市场上以及出售时（如果已出售的话）有关的

每个物品 （如果已出售）。给定的一个月有其他标准（其他标准有足够的排列，预先计算这些数字并不是理想的。）我正在尝试检索120个月的数据，并在网页上平均数据，因此理想情况下，整个查询都会如此快速 可能的。我有一个可行的查询...但是只需要5秒钟的时间才能持续12个月的数据，我需要它的速度至少比这快10倍，因此我可以在不错的时间内显示10年的平均值。

这是我现在正在使用的示例：

SELECT Avg(inv)
FROM   (
    SELECT
           CASE
                  WHEN Count(*) =0 THEN NULL
                  ELSE Count(*)
           END AS inv
    FROM   listings
    WHERE  originalentrytimestamp <= @DateOfMonthEnd
    AND    @DateOfMonthEnd < @currentMonthStart
    AND    (
                  offmarketdate > @DateOfMonthEnd
           OR     offmarketdate IS NULL)
    AND    city='New York'
    AND    listprice >= 0
    AND    listprice <= 999999999
    AND    category1='RESI'
    AND    category2 IN('D','A','S','R','O')
    UNION ALL
    SELECT
           CASE
                  WHEN Count(*) =0 THEN NULL
                  ELSE Count(*)
           END AS inv
    FROM   listings
    WHERE  originalentrytimestamp <= Dateadd(month, 1,@DateOfMonthEnd)
    AND    Dateadd(month, 1,@DateOfMonthEnd) < @currentMonthStart
    AND    (
                  offmarketdate > Dateadd(month, 1,@DateOfMonthEnd)
           OR     offmarketdate IS NULL)
    AND    city='New York'
    AND    listprice >= 0
    AND    listprice <= 999999999
    AND    category1='RESI'
    AND    category2 IN('D','A','S','R','O')
    UNION ALL
    SELECT
           CASE
                  WHEN Count(*) =0 THEN NULL
                  ELSE Count(*)
           END AS inv
    FROM   listings
    WHERE  originalentrytimestamp <= Dateadd(month, 2,@DateOfMonthEnd)
    AND    Dateadd(month, 2,@DateOfMonthEnd) < @currentMonthStart
    AND    (
                  offmarketdate > Dateadd(month, 2,@DateOfMonthEnd)
           OR     offmarketdate IS NULL)
    AND    city='New York'
    AND    listprice >= 0
    AND    listprice <= 999999999
    AND    category1='RESI'
    AND    category2 IN('D','A','S','R','O')
     )

我只在此代码中包括3个月，但是如果我想运行120个月，这很快就会变得笨拙。它也很慢，但是我不确定如何将其重新使用以使用窗口函数或子句的组作为单个列表，如果花了一段时间的出售，则可能会在一月和2月计算单个列表。

案例声明是允许代码也用于计算年度最早的平均值，其中代码可能包含一个没有数据的未来一个月，并且应将其排除在平均水平之外（但是，如果没有必要可以以接受开始和结束月的格式重新编码整个内容，而不是对120个查询进行硬编码。）

从评论讨论：

• 执行计划： https://brentozar.com/pastetheplan/?id=rjtynali5=rjtynali5
• = rjtynali5 href =“ https://pastebin.com/uxrtsdax” rel =“ nofollow noreferrer”> https://pastebin.com/uxrtsdax

此表是每天制作的实时数据的副本，而不是在中午写成，可以免费添加或删除任何索引，以加快加速查询（但是，城市价格，名单等条款的示例只是示例，我们到目前为止有索引，因为我们可以在那里搜索不同的条款。）

Answer 1

尝试a 覆盖索引 on （City，category1，category2，ointernEntryTimestamp）包括 offmarketdate 和 ListPrice 列。像这样的DDL可能会创建您需要的索引。

CREATE NONCLUSTERED INDEX [CityCategoriesTimestamp] ON [dbo].[listings]
(
    [city] ASC,
    [category1] ASC,
    [category2] ASC,
    [originalentrytimestamp] ASC
)
INCLUDE (
    [offmarketdate] ASC,
    [listprice] ASC
);

这是因为可以从第一个合名的顺序进行扫描行到最后一个。这比扫描整个桌子要快。

并且，考虑重新加工查询以使用 eomonth（）。摆脱大联合所有级联，而不是（OriginalTerryTimestamp）进行组。我建议重写，但我不确定我足够了解您的业务逻辑。

Answer 2

我最终通过对这些可能的标准进行了这些库存水平的预先计算来解决这个问题。这并不理想，因为它限制了我可以在此搜索中应用的其他标准，并且前电池列表正在占用大量空间，但它将适用于我与之合作的特定业务案例。