sql - 为什么交叉应用会使查询变慢？

Question

我有一个查询需要大约 11 秒才能运行单个日期。我想多天运行相同的查询。换句话说，我希望能够返回多天的快照。这是我的原始查询:

SELECT 
    COUNT(*) AS 'Number of Cars',
    d.ManufacturerName AS 'Make',
    d.Name AS 'Model', 
    c.name AS 'Car Class'
FROM CarRating a 
    INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID
    INNER JOIN CarClass c ON a.CarClassID = c.CarClassID
    INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID

WHERE 
    @myDate < a.ExpiredWhen AND @myDate  >= a.EffectiveWhen 
GROUP BY 
    d.Name, c.name,d.ManufacturerName

就像我提到的查询需要 11 秒。为了对多个日期运行此查询，我使用了一个日期表并将其交叉应用于上述查询:

SELECT [DATE], b.* FROM DimDate 
CROSS APPLY
    (SELECT 
        COUNT(*) AS 'Number of Cars',
        d.ManufacturerName AS 'Make',
        d.Name AS 'Model', 
        c.name AS 'Car Class'
    FROM CarRating a 
        INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID
        INNER JOIN CarClass c ON a.CarClassID = c.CarClassID
        INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID

    WHERE 
    dimDate.Date < a.ExpiredWhen AND dimDate.Date >= a.EffectiveWhen    
    GROUP BY 
    d.Name, c.name,d.ManufacturerName) b

 WHERE DimDate.Date between @StartDate and @EndDate

即使是一天，这个查询也需要 49 秒。为什么这么慢？有没有更好的方法来做到这一点？

Answer 1

您的查询速度较慢，因为它加入了维度表，大大增加了正在处理的数据量。您可以通过确保您有适当的索引来修复此查询:

OwnedCar(OwnedCarId)

CarClass(CarClassId)

BaseCare(BaseCareID)

CarRating(EffectiveWhen, ExpiredWhen)

如果这没有帮助，那么您将需要重新考虑查询。有另一种写法，但索引可以更简单地解决问题。