查询优化

Question

我有一张表，上面有所有的销售记录(按文件/项目分类)。我需要一个报告与一些指标按项目，但只对VB文档(文档类型)。不过，我需要一些指示符，例如每天最大数量/项目，这迫使我进行另一个查询，但这是按日分组的。问题是我得到了一个非常慢的查询。总共4700000次查询花费了4小时以上。

可以优化这个查询吗？可能没有用最好的方法..。

SELECT std.ItemID AS [REF.], SUM(std.Quantity) AS [TOTAL QTY]
, MAX(t1.Qty) AS [MAX QTY BY DAY]
, COUNT(DISTINCT(std.CreateDate)) AS [SALES DAYS]
, DATEDIFF(DAY,MIN(std.CreateDate),MAX(std.CreateDate))+1 AS [CALENDAR DAYS]
FROM SaleTransactionDetails std
INNER JOIN (
    SELECT CreateDate, ItemID, SUM(Quantity) AS [Qty]
    FROM SaleTransactionDetails
    WHERE TransDocument = 'VB' AND CreateDate > '2012-12-15'
    GROUP BY CreateDate, ItemID) t1 
    ON std.ItemID = t1.ItemID
WHERE std.TransDocument = 'VB' AND std.CreateDate > '2012-12-15'
GROUP BY std.ItemID

我会感谢你的帮助。谢谢大家！

Answer 1

您不需要引用表两次，您可以从一次扫描中获得所需的一切：

SELECT  [REF.] = std.ItemID,
        [Total Quantity] = SUM(std.Quantity),
        [Max Qty By Day] = MAX(std.Quantity),
        [Sales Days] = COUNT(std.CreateDate),
        [Calendar Days] = DATEDIFF(DAY, MIN(std.CreateDate), MAX(std.CreateDate)) + 1
FROM    (   SELECT  std.CreateDate,
                    std.ItemID,
                    Quantity = SUM(std.Quantity)
            FROM    SaleTransactionDetails std
            WHERE   std.TransDocument = 'VB' 
            AND     std.CreateDate > '2012-12-15'
            GROUP BY std.CreateDate, std.ItemID
        ) std
GROUP BY std.ItemID;

然而，在您的绩效中，更重要的因素将是如何对表进行索引。找到所需索引的最佳方法是在启用“”选项的情况下运行查询，然后SSMS将建议建立一个索引来改进查询。我猜下面的过滤指数将提高性能

CREATE NONCLUSTERED INDEX IX_SaleTransactionDetails_ItemID_CreateDate
    ON SaleTransactionDetails (ItemID, CreateDate)
    INCLUDE (Quantity)
    WHERE TransDocument = 'VB';