One of my queries (he used to create a report so that it simply contained: where's, join and group by), returned a timeout after ~ 20 minutes. The proposed execution plan showed me:
This Hash Match probably means a group by clause of my request. But what was interesting to me was the approximate data size of ~ 3 TB, which is 10 times the size of my database. After the investigation, I found out the query execution part that significantly increases this data transfer:
but these nested loops have any effect on the cost of the request due to the estimated execution plan.
This request is generated by Reporting Services and caught (I delete the unused part):
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 , , -, . -?