sql - 查询优化 - 增加估计数据大小

Question

我的一个查询(它用于创建报告，因此只包含:where's、join's 和 group by's)在约 20 分钟后返回超时。预计执行计划向我展示了:

这个哈希匹配可能意味着我的查询的 group by 子句。但令我感兴趣的是估计数据大小约为 3TB，比我的数据库大 10 倍。经过调查，我发现查询执行的一部分会极大地增加数据传输:

但是由于估计执行计划，此嵌套循环会对查询成本产生任何影响。

此查询由 Reporting Services 生成并捕获(我删除了一些未使用的部分):

SET DATEFIRST 7
SELECT
    SUM([f_orders].[LORDEREDQTY_PU]) [QtyPU],
    SUM((((((((([f_orders].[DTRANSFERPRICE] + [f_orders].[DICSBASICORDERFEE]) + [f_orders].[DICSFREIGHTCHARGE]) + [f_orders].[DICSINSURANCEFEE]) + [f_orders].[DICSPACKINGFEE]) + [f_orders].[DICSFREETXTFEE]) + [f_orders].[DICSLOGISTICSFEE]) + [f_orders].[DICSPACKAGEPACKINGFEE]) * [A_ExchangeRates].[USDRate])) [TPinclChargesinUSDBudgetexchangerate],
    [f_orders].[LORDEREDQTY] [TotalQty],
    [dbo_D_ORDERADDRESS].[Countrycode] [Countrycode],
    [D_PRODUCT_NEW].[Framesize] [Framesize],
    [f_orders].[SZCUSSHORTNAME] [Customershortname],
    [f_orders].[SZCUSNO] [Customerno],
    [D_PRODUCT_NEW].[Generationcode] [Generationcode],
    [D_PU].[PU] [PU],
    [D_BALIDINFO].[SRU] [SRU],
    [D_WAREHOUSE].[Warehouse] [Warehouse],
    [f_orders].[DATE_OrderBookedatSU] [OrderbookedatSU],
    [f_orders].[DTCONFIRMEDDEL] [Confirmeddeliverydate],
    [D_PRODUCT_NEW].[MotorsProductType] [MotorsProductType],
    [f_orders].[SZORDERNO] [Orderno],
    [f_orders].[IORDERLINENO] [Orderlineno],
    [D_FRAMESIZE].[Framesize1] [Framesize1],
    [D_PRODGROUP].[Productgroup] [Productgroup],
    [D_PRODTYPE].[Producttype] [Producttype],
    [f_orders].[SZPRODNO] [Productno],
    [D_TYPEOFORDHAND].[Typeoforderhandling] [Typeoforderhandling],
    CASE WHEN COALESCE(LEN([f_orders].[SZVARIANTCODE]), 0) = 0 THEN CAST([f_orders].[IVARIANTCODENO] AS VARCHAR(255)) ELSE [f_orders].[SZVARIANTCODE] END [Variantcodeno],
    [f_orders].[IVARIANTIDX] [Variantindex],
    [f_orders].[DATE_OrderBookedatSU] [OrderbookedatSU1],
    [f_orders].[DATE_OrderBookedatSU] [OrderbookedatSU2]
FROM
[dbo].[f_orders] [f_orders]
LEFT OUTER JOIN (
    SELECT
        [dbo_D_ORDERADDRESS].[SZCOUNTRYCODE] [Countrycode],
        [dbo_D_ORDERADDRESS].[ADDRESS_ORGID] [ADDRESS_ORGID]
    FROM
        (
            SELECT        CAST(1 AS numeric(38)) AS ID, CAST(CVCOUNTRY AS int) AS cvcountry, ADDRESS_ORGID, ORDERLINE_ORGID, ORDER_ORGID, CVADDRESSTYPE, IDWSOURCEID, 
                LORDERID, IORDERLINENO, SZNAME1, SZNAME2, SZNAME3, SZROAD1, SZROAD2, SZROAD3, SZPOSTCODE, SZTOWN, SZPHONE, SZFAX, SZCONTACTPERSON, 
                SZEMAIL, SZVATID, SZINVCUSNO, SZCOUNTRYCODE
            FROM            D_ORDERADDRESS_NEW
        ) [dbo_D_ORDERADDRESS]
) [dbo_D_ORDERADDRESS] ON ([f_orders].[DELADDRESS_ORGID] = [dbo_D_ORDERADDRESS].[ADDRESS_ORGID] OR [f_orders].[DELADDRESS_ORGID] IS NULL AND [dbo_D_ORDERADDRESS].[ADDRESS_ORGID] IS NULL)
LEFT OUTER JOIN (
    SELECT
        [D_FRAMESIZE].[Framesize] [Framesize],
        [D_PRODUCT_NEW].[SZGENCODE] [Generationcode],
        [D_PRODTYPE].[MotorsProductType] [MotorsProductType],
        [D_PRODTYPE].[MotorsProdType] [MotorsProdType],
        [D_PRODUCT_NEW].[DWBOBJECTOID] [DWBOBJECTOID]
    FROM
        [dbo].[D_PRODUCT_NEW] [D_PRODUCT_NEW]
        LEFT OUTER JOIN (
            SELECT
                [D_FRAMESIZE].[DESCRIPTIONEN] [Framesize],
                [D_FRAMESIZE].[VALUE] [VALUE]
            FROM
                [dbo].[D_FRAMESIZE] [D_FRAMESIZE]
        ) [D_FRAMESIZE] ON [D_PRODUCT_NEW].[CVFRAMESIZE] = [D_FRAMESIZE].[VALUE]
        LEFT OUTER JOIN (
            SELECT
                [D_PRODTYPE].[SZMDPRODTYPE] [MotorsProductType],
                [D_PRODTYPE].[SZMDPRODTYPE] [MotorsProdType],
                [D_PRODTYPE].[VALUE] [VALUE]
            FROM
                [dbo].[D_PRODTYPE] [D_PRODTYPE]
        ) [D_PRODTYPE] ON [D_PRODUCT_NEW].[CVPRODTYPE] = [D_PRODTYPE].[VALUE]            
) [D_PRODUCT_NEW] ON ([f_orders].[DWPRDOID] = [D_PRODUCT_NEW].[DWBOBJECTOID] OR [f_orders].[DWPRDOID] IS NULL AND [D_PRODUCT_NEW].[DWBOBJECTOID] IS NULL)  
LEFT OUTER JOIN (
    SELECT
        [D_BALIDINFO].[SZSOPREGION] [SRUSOPRegion],
        [D_BALIDINFO].[SZCOUNTRY] [SRUCountry],
        [D_BALIDINFO].[SZALTERNATEID] [SRU],
        [D_BALIDINFO].[SZALTERNATEID] [SZALTERNATEID],
        [D_BALIDINFO].[SZSOPREGION] [SOPRegion],
        [D_BALIDINFO].[DWBOBJECTOID] [DWBOBJECTOID]
    FROM
        [dbo].[D_BALIDINFO] [D_BALIDINFO]
) [D_BALIDINFO] ON [f_orders].[DWSALESRESPONSIBLEUNITOID] = [D_BALIDINFO].[DWBOBJECTOID]
LEFT OUTER JOIN (
    SELECT
        [D_PU].[DESCRIPTIONEN] [PU],
        [D_PU].[DESCRIPTIONEN] [PU2],
        [D_PU].[VALUE] [VALUE]
    FROM
        [dbo].[D_PU] [D_PU]
) [D_PU] ON [f_orders].[CVPU] = [D_PU].[VALUE]
LEFT OUTER JOIN (
    SELECT
        [D_WAREHOUSE].[DESCRIPTIONEN] [Warehouse],
        [D_WAREHOUSE].[DESCRIPTIONEN] [Warehouse2],
        [D_WAREHOUSE].[VALUE] [VALUE]
    FROM
        [dbo].[D_WAREHOUSE] [D_WAREHOUSE]
) [D_WAREHOUSE] ON [f_orders].[CVWAREHOUSEID] = [D_WAREHOUSE].[VALUE]
LEFT OUTER JOIN (
    SELECT
        [D_FRAMESIZE].[DESCRIPTIONEN] [Framesize1],
        [D_FRAMESIZE].[DESCRIPTIONEN] [FrameSize],
        [D_FRAMESIZE].[VALUE] [VALUE]
    FROM
        [dbo].[D_FRAMESIZE] [D_FRAMESIZE]
) [D_FRAMESIZE] ON [f_orders].[CVFRAMESIZE] = [D_FRAMESIZE].[VALUE]

LEFT OUTER JOIN (
    SELECT
        [D_PRODGROUP].[DESCRIPTIONEN] [Productgroup],
        [D_PRODGROUP].[DESCRIPTIONEN] [ProductGroup2],
        [D_PRODGROUP].[VALUE] [VALUE]
    FROM
        [dbo].[D_PRODGROUP] [D_PRODGROUP]
) [D_PRODGROUP] ON [f_orders].[CVPRODGROUP] = [D_PRODGROUP].[VALUE]
LEFT OUTER JOIN (
    SELECT
        [D_PRODTYPE].[DESCRIPTIONEN] [Producttype],
        [D_PRODTYPE].[DESCRIPTIONEN] [ProductType2],
        [D_PRODTYPE].[VALUE] [VALUE]
    FROM
        [dbo].[D_PRODTYPE] [D_PRODTYPE]
) [D_PRODTYPE] ON [f_orders].[CVPRODTYPE] = [D_PRODTYPE].[VALUE]
INNER JOIN (
    SELECT
        [S_SECURITY].[Login_Name] [LoginName],
        [S_SECURITY].[Security_ID] [Security_ID]
    FROM
        [dbo].[S_SECURITY] [S_SECURITY]
) [S_SECURITY] ON [f_orders].[SECURITY_ID2] = [S_SECURITY].[Security_ID]
LEFT OUTER JOIN (
    SELECT
        [D_ORDERTYPE].[DESCRIPTIONEN] [OrderType],
        [D_ORDERTYPE].[VALUE] [VALUE]
    FROM
        [dbo].[D_ORDERTYPE] [D_ORDERTYPE]
) [D_ORDERTYPE] ON [f_orders].[CVORDERTYPE] = [D_ORDERTYPE].[VALUE]
LEFT OUTER JOIN (
    SELECT
        [D_LINEORDERTYPE].[DESCRIPTIONEN] [LineOrderType],
        [D_LINEORDERTYPE].[VALUE] [VALUE]
    FROM
        [dbo].[D_LINEORDERTYPE] [D_LINEORDERTYPE]
) [D_LINEORDERTYPE] ON [f_orders].[CVLINEORDERTYPE] = [D_LINEORDERTYPE].[VALUE]
LEFT OUTER JOIN (
    SELECT
        [A_ExchangeRates].[USDRate] [USDRate],
        [A_ExchangeRates].[CVCurrency] [CVCurrency],
        [A_ExchangeRates].[RateDate] [RateDate]
    FROM
        [dbo].[A_ExchangeRates] [A_ExchangeRates]
) [A_ExchangeRates] ON [f_orders].[CVCURRENCY] = [A_ExchangeRates].[CVCurrency] AND [f_orders].[DATE_OrderBookedatSU] = [A_ExchangeRates].[RateDate]
LEFT OUTER JOIN (
    SELECT
        [D_TYPEOFORDHAND].[DESCRIPTIONEN] [Typeoforderhandling],
        [D_TYPEOFORDHAND].[VALUE] [VALUE]
    FROM
        [dbo].[D_TYPEOFORDHAND] [D_TYPEOFORDHAND]
) [D_TYPEOFORDHAND] ON [f_orders].[CVTYPEOFORDHAND] = [D_TYPEOFORDHAND].[VALUE]
    GROUP BY
[f_orders].[LORDEREDQTY], [dbo_D_ORDERADDRESS].[Countrycode], [D_PRODUCT_NEW].[Framesize],  
[f_orders].[SZCUSSHORTNAME], [f_orders].[SZCUSNO], [D_PRODUCT_NEW].[Generationcode],  
[D_BALIDINFO].[SRUSOPRegion], [D_BALIDINFO].[SRUCountry], [D_PU].[PU], [D_BALIDINFO].[SRU], [D_WAREHOUSE].[Warehouse], [f_orders].[DATE_OrderBookedatSU], [f_orders].[DTCONFIRMEDDEL], 
[D_PRODUCT_NEW].[MotorsProductType], [f_orders].[SZORDERNO], [f_orders].[IORDERLINENO], [D_FRAMESIZE].[Framesize1], 
    [D_PRODGROUP].[Productgroup], [D_PRODTYPE].[Producttype], [f_orders].[SZPRODNO],
    CASE WHEN COALESCE(LEN([f_orders].[SZVARIANTCODE]), 0) = 0 THEN CAST([f_orders].[IVARIANTCODENO] AS VARCHAR(255)) ELSE [f_orders].[SZVARIANTCODE] END,
    [f_orders].[IVARIANTIDX], [f_orders].[DATE_OrderBookedatSU], [f_orders].[DATE_OrderBookedatSU], D_TYPEOFORDHAND.Typeoforderhandling

1.通过嵌套循环增加估计数据大小是正常的，对性能没有任何影响吗？

2.我使用了大约 20 个分组列，它似乎对查询成本影响最大。可以替换这个哈希匹配吗？

Answer 1

好吧，您的查询看起来相当复杂，并且很可能使优化器感到困惑，也许可以查看统计数据，看看是否可以通过使用完整扫描来帮助优化器。考虑使用临时表来暂存嵌套连接，以便您只暂存所需的列。还可以考虑在主表 f_orders 上为此添加索引以减少表扫描。