为每个提取的记录性能在其他表上计算聚合

Question

我正在使用许多联接进行查询。对于一个“联合”表，有一对多的关联，我需要在这个表上聚合(和)。

每次执行查询时，我都会提取大约500到1000条记录。它大约需要100到200 has (它有许多连接)。但是，在添加聚合后，它将执行增加到大约5-6秒！

我尝试了2种解决方案(问题在于最后两篇专栏文章：、SubchargesTo、和都因为一个很大的因素而性能恶化：

第一：

SELECT
            RR.Id,
            Customer.Name AS Customer,
            PrincipalsCustomer.Name AS PrincipalsCustomer,
            EffectiveCarrier.Name AS EffectiveCarrier,
            CAST(CASE RR.isImport WHEN 1 THEN RR.unloadingDateStart ELSE TR.loadingDateStart END AS DATE) AS LoadingUnloadingDate,
            RR.containerNo AS ContainerNumber,
            CASE RR.isImport WHEN 1 THEN PLACEUNLOADING.Name ELSE PlaceLoading.Name END AS LoadingUnloadingPlace,
            Pol.Name AS POL,
            Pod.Name AS POD,
            Commodity.Name AS Commodity,
            RR.Km AS Km,
            RR.pricePerKm AS SalesPricePerKM,
            RR.salesPrice AS SalesPrice,
            RR.purchasePrice AS PurchasePrice
            (SELECT SUM(salesAmount*salesCost) FROM TruckingTobaccoSurcharges WHERE TruckingTobaccoSurcharges.REPORT = RR.Id) +
            (SELECT SUM(incomeAmount*toSBCIncome) FROM TruckingTobaccoSurcharges2 WHERE TruckingTobaccoSurcharges2.REPORT = RR.Id) as SurchargesTo,
            (SELECT SUM(costAmount*costCost) FROM TruckingTobaccoSurcharges WHERE TruckingTobaccoSurcharges.REPORT = RR.Id) +
            (SELECT SUM(costAmount*fromCustomerCost) FROM TruckingTobaccoSurcharges2 WHERE TruckingTobaccoSurcharges2.REPORT = RR.Id) as SurchargesFrom
        FROM Report RR
            JOIN TruckingReport TR ON TR.REPORT = RR.ID
            LEFT JOIN Customer ON RR.CUSTOMER = Customer.ID
            LEFT JOIN PrincipalsCustomer ON RR.PRINCIPALSCUSTOMER = PrincipalsCustomer.ID
            LEFT JOIN EffectiveCarrier ON RR.EFFECTIVECARRIER = EffectiveCarrier.ID
            LEFT JOIN PlaceLoading ON TR.PLACELOADING = PlaceLoading.ID
            LEFT JOIN PlaceUnloading ON RR.PLACEUNLOADING = PlaceUnloading.ID
            LEFT JOIN Pol ON TR.POL = Pol.Id
            LEFT JOIN Pod ON TR.POD = Pod.Id
            LEFT JOIN Commodity ON RR.COMMODITY = Commodity.Id

第二项：

SELECT
            RR.Id,
            Customer.Name AS Customer,
            PrincipalsCustomer.Name AS PrincipalsCustomer,
            EffectiveCarrier.Name AS EffectiveCarrier,
            CAST(CASE RR.isImport WHEN 1 THEN RR.unloadingDateStart ELSE TR.loadingDateStart END AS DATE) AS LoadingUnloadingDate,
            RR.containerNo AS ContainerNumber,
            CASE RR.isImport WHEN 1 THEN PLACEUNLOADING.Name ELSE PlaceLoading.Name END AS LoadingUnloadingPlace,
            Pol.Name AS POL,
            Pod.Name AS POD,
            Commodity.Name AS Commodity,
            RR.Km AS Km,
            RR.pricePerKm AS SalesPricePerKM,
            RR.salesPrice AS SalesPrice,
            RR.purchasePrice AS PurchasePrice,
            SUBCH1.sales + SUBCH2.sales AS SurchargesTo,
            SUBCH1.costs + SUBCH2.costs AS SurchargesFrom
        FROM Report RR
            JOIN TruckingReport TR ON TR.REPORT = RR.ID
            LEFT JOIN Customer ON RR.CUSTOMER = Customer.ID
            LEFT JOIN PrincipalsCustomer ON RR.PRINCIPALSCUSTOMER = PrincipalsCustomer.ID
            LEFT JOIN EffectiveCarrier ON RR.EFFECTIVECARRIER = EffectiveCarrier.ID
            LEFT JOIN PlaceLoading ON TR.PLACELOADING = PlaceLoading.ID
            LEFT JOIN PlaceUnloading ON RR.PLACEUNLOADING = PlaceUnloading.ID
            LEFT JOIN Pol ON TR.POL = Pol.Id
            LEFT JOIN Pod ON TR.POD = Pod.Id
            LEFT JOIN Commodity ON RR.COMMODITY = Commodity.Id
            LEFT JOIN ( SELECT REPORT, SUM(salesAmount*salesCost) AS sales, SUM(costAmount*costCost) AS costs
                        FROM TruckingTobaccoSurcharges SR1 GROUP BY SR1.REPORT
                        )AS SUBCH1 ON SUBCH1.REPORT = RR.ID
            LEFT JOIN ( SELECT REPORT, SUM(incomeAmount*toSBCIncome) AS sales, SUM(costAmount*fromCustomerCost) AS costs
                        FROM TruckingTobaccoSurcharges2 SR2 GROUP BY SR2.REPORT
                       )AS SUBCH2 ON SUBCH2.REPORT = RR.ID

是否有更快的方法来达到预期的结果？还是这么多的加入不能让它变得更快？

感谢任何帮助=]

编辑：

在TruckingTobaccoSurcharges表的报表FK上添加索引，正如Nikolać建议的那样(使用解决方案1)。但还没有尝试解决方案2。不过，我想知道我的查询是否会更好，因为正如其他人所说的，我不是加入，而是要求.

Answer 1

你必须意识到你没有加入！！

是的，你正在加入，因为所有地方都有剩余的加入，但事实上不，这不是你的问题。

您正在子查询以检索您的和，这是坏的、坏的和坏的。子查询总是比加入慢。

这是一个子查询，而不是联接：

LEFT JOIN 
( 
    SELECT REPORT, 
           SUM(salesAmount*salesCost) AS sales, 
           SUM(costAmount*costCost) AS costs
    FROM TruckingTobaccoSurcharges SR1 GROUP BY SR1.REPORT
)

因为您是在子查询，所以不使用报表上的索引，因此速度慢。

因此，对于您的问题，下面是应该起作用的：(请注意，真正的加入！)

SELECT
    SUM(s.salesAmount * s.salesCost) + SUM(s2.salesAmount * s2.salesCost) AS SurchargesTo,
    SUM(s.costAmount * s.costCost) + SUM(s2.costAmount * s2.fromCustomerCost) AS SurchargesFrom
FROM Report RR
    LEFT JOIN TruckingTobaccoSurcharges s 
        ON s.REPORT = RR.ID
    LEFT JOIN TruckingTobaccoSurcharges2 ss
        ON s.REPORT = RR.ID
GROUP BY s.REPORT