Hi, some clause to minimize the runtime in this query??
it is taking more than 5 minutes to read one table of 5 millions of records and show only 2.5 millons of then after the join and where´s included on it..
- SELECT
- Query_Base.Calculo_Tasa AS [PC Euros]
- ,Query_Base.profit_center_local_currency AS [PC Local Currency]
- ,Query_Base.Controlling_Area_Controlling_Area_Desc AS [Country]
- ,Query_Base.Fiscal_Year AS [FY]
- ,Query_Base.Period AS [Period]
- ,Query_Base.[Business _Area_Origen] AS [BA]
- ,Query_Base.[Business _Line_Origen] AS [BL]
- ,Query_Base.Tipo_de_costo AS [COA1 Origin]
- ,q1.COA_Level_2 AS COA2
- ,q1.COA_Level_3 AS COA3
- ,CASE
- WHEN Query_Base.Tipo_de_costo = 'NULL'
- THEN CASE
- WHEN q1.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner <> '0'
- THEN 'Financial income, net Below Profit'
- WHEN q1.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner = '0'
- THEN 'Financial income, net'
- ELSE q1.COA_Level_2
- END
- ELSE Query_Base.Tipo_de_costo
- END AS [Key Figures]
- ,Query_Base.Tipo_de_costo_Partner AS [COA1 Partner]
- ,Query_Base.Average AS [FX Average]
- ,Query_Base.SPOT AS [FX Spot]
- ,Query_Base.Cuentas_Maestro_Numero_cuenta AS [Account Number]
- ,Query_Base.Descripcion_cuenta AS [Account Desc.]
- ,Query_Base.Cuentas_Maestro_Controlling_Area AS [ARE]
- ,Query_Base.Cuentas_Maestro_Profit_Center AS [Profit Center]
- ,Query_Base.[Nombre_ Profit _Center_Origen] AS [PC Desc.]
- ,Query_Base.Partner_Profit_Ctr AS [Profit Center Partner]
- ,Query_Base.[Nombre_ Profit _Center_Partner] AS [PC Partner Desc.]
- ,Query_Base.Elimination_PrCtr AS [Profit Center Elim.]
- ,Query_Base.[Nombre_ Profit _Center_Elimination] AS [PC Elim. Desc.]
- ,Query_Base.Functional_Area AS [Functional Area]
- ,Query_Base.Functional_area_Desc AS [Functional Area Desc.]
- ,Query_Base.Partner_Func_Area AS [Functional Area Partner]
- ,Query_Base.Functional_area_Desc_Partner AS [Functional Area Desc. Partner]
- ,Query_Base.Funcionalidad_Origen AS [PC Functionality]
- ,Query_Base.Funcionalidad_Partner AS [PC Partner Functionality]
- ,Query_Base.[Spiridon _2019_Origen] AS [Depth Structure SAP]
- ,Query_Base.[Business _Subsegment_Origen] AS [Depth Structure ESPRIT]
- ,Query_Base.[Short _Text_Origen] AS [BL Short Text Origin]
- ,Query_Base.[Cross _Div _Biz_Origen] AS [Cross Div. Biz.]
- ,Query_Base.[Cross _Div _Biz_Partner] AS [Cross Div. Biz. Partner]
- ,Query_Base.[Transaction] AS [Transaction]
- ,Query_Base.Trans_Type_Desc AS [Transaction Type Desc.]
- ,Query_Base.Origin_Object_num AS [Origin Object]
- ,Query_Base.Origin_Object_description AS [Origin Object Desc.]
- ,Query_Base.Receiver_object_type AS [Receiver Object]
- ,Query_Base.Origin_Object_description_Receiver AS [Receiver Object Desc.]
- ,Query_Base.[Short _Text_Partner] AS [BL Short Text Partner]
- ,Query_Base.[Business _Area_Partner] AS [BA Partner]
- ,Query_Base.[Business _Line_Partner] AS [BL Partner]
- ,Query_Base.[Spiridon _2019_Partner] AS [Depth Structure SAP Partner]
- ,Query_Base.[Business _Subsegment_Partner] AS [Depth Structure ESPRIT Partner]
- ,Query_Base.[Short _Text_Elimination] AS [BL Short Text Elim.]
- ,Query_Base.[Cross _Div _Biz_Elimination] AS [Cross Div. Biz. Elim.]
- ,Query_Base.[Business _Area_Elimination] AS [BA Elim.]
- ,Query_Base.[Business _Line_Elimination] AS [BL Elim.]
- ,Query_Base.[Spiridon _2019_Elimination] AS [Depth Structure SAP Elim.]
- ,Query_Base.[Business _Subsegment_Elimination] AS [Depth Structure ESPRIT Elim.]
- ,Query_Base.Funcionalidad_Elimination AS [PC Elim. Functionality]
- ,Query_Base.Plant AS [Plant] /*,Query_Base.Cuentas_Maestro_Curr_key as [Currency Key]*/
- ,Query_Base.Curr1key1of1PrCtr1LC AS [Currency Key]
- ,Query_Base.Debit_Credit_Ind AS [C/D Indicator]
- ,Query_Base.Cuentas_MaestroTransType AS [Transaction Type]
- ,Query_Base.Trading_Partner AS [Trading Partner]
- ,Query_Base.Periodo AS [Periodo]
- FROM [1RQ_BPCSQL1].[dbo].[Query_Base]
- LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group2] Q1 ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= q1.inicio
- AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= Q1.fin
- WHERE [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta IN (
- 'R1110110'
- ,'R1111300'
- ,'R3310000' /*,'R9999900'*/
- )
- AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
- AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 1
- AND 12
- OR [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta LIKE ('S6%')
- AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
- AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 1
- AND 12
- UNION
- SELECT Query_Base.Calculo_Tasa AS [PC Euros]
- ,Query_Base.profit_center_local_currency AS [PC Local Currency]
- ,Query_Base.Controlling_Area_Controlling_Area_Desc AS [Country]
- ,Query_Base.Fiscal_Year AS [FY]
- ,Query_Base.Period AS [Period]
- ,Query_Base.[Business _Area_Origen] AS [BA]
- ,Query_Base.[Business _Line_Origen] AS [BL]
- ,Query_Base.Tipo_de_costo AS [COA1 Origin]
- ,CASE
- WHEN Q.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner <> '0'
- THEN 'Financial income, net Below Profit'
- WHEN Q.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner = '0'
- THEN 'Financial income, net'
- ELSE Q.COA_Level_2
- END AS COA2
- ,Q.COA_Level_3 AS COA3
- ,CASE
- WHEN Query_Base.Cuentas_Maestro_Numero_cuenta >= '50000000'
- AND Query_Base.Cuentas_Maestro_Numero_cuenta <= '59999999'
- THEN 'Own Business (Rev outside SHS)'
- ELSE CASE
- WHEN Query_Base.Tipo_de_costo = 'NULL'
- THEN CASE
- WHEN Q.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner <> '0'
- THEN 'Financial income, net Below Profit'
- WHEN Q.COA_Level_2 = 'Financial income, net Split'
- AND Query_Base.Trading_Partner = '0'
- THEN 'Financial income, net'
- ELSE Q.COA_Level_2
- END
- ELSE Query_Base.Tipo_de_costo
- END
- END AS [Key Figures]
- ,Query_Base.Tipo_de_costo_Partner AS [COA1 Partner]
- ,Query_Base.Average AS [FX Average]
- ,Query_Base.SPOT AS [FX Spot]
- ,Query_Base.Cuentas_Maestro_Numero_cuenta AS [Account Number]
- ,Query_Base.Descripcion_cuenta AS [Account Desc.]
- ,Query_Base.Cuentas_Maestro_Controlling_Area AS [ARE]
- ,Query_Base.Cuentas_Maestro_Profit_Center AS [Profit Center]
- ,Query_Base.[Nombre_ Profit _Center_Origen] AS [PC Desc.]
- ,Query_Base.Partner_Profit_Ctr AS [Profit Center Partner]
- ,Query_Base.[Nombre_ Profit _Center_Partner] AS [PC Partner Desc.]
- ,Query_Base.Elimination_PrCtr AS [Profit Center Elim.]
- ,Query_Base.[Nombre_ Profit _Center_Elimination] AS [PC Elim. Desc.]
- ,Query_Base.Functional_Area AS [Functional Area]
- ,Query_Base.Functional_area_Desc AS [Functional Area Desc.]
- ,Query_Base.Partner_Func_Area AS [Functional Area Partner]
- ,Query_Base.Functional_area_Desc_Partner AS [Functional Area Desc. Partner]
- ,Query_Base.Funcionalidad_Origen AS [PC Functionality]
- ,Query_Base.Funcionalidad_Partner AS [PC Partner Functionality]
- ,Query_Base.[Spiridon _2019_Origen] AS [Depth Structure SAP]
- ,Query_Base.[Business _Subsegment_Origen] AS [Depth Structure ESPRIT]
- ,Query_Base.[Short _Text_Origen] AS [BL Short Text Origin]
- ,Query_Base.[Cross _Div _Biz_Origen] AS [Cross Div. Biz.]
- ,Query_Base.[Cross _Div _Biz_Partner] AS [Cross Div. Biz. Partner]
- ,Query_Base.[Transaction] AS [Transaction]
- ,Query_Base.Trans_Type_Desc AS [Transaction Type Desc.]
- ,Query_Base.Origin_Object_num AS [Origin Object]
- ,Query_Base.Origin_Object_description AS [Origin Object Desc.]
- ,Query_Base.Receiver_object_type AS [Receiver Object]
- ,Query_Base.Origin_Object_description_Receiver AS [Receiver Object Desc.]
- ,Query_Base.[Short _Text_Partner] AS [BL Short Text Partner]
- ,Query_Base.[Business _Area_Partner] AS [BA Partner]
- ,Query_Base.[Business _Line_Partner] AS [BL Partner]
- ,Query_Base.[Spiridon _2019_Partner] AS [Depth Structure SAP Partner]
- ,Query_Base.[Business _Subsegment_Partner] AS [Depth Structure ESPRIT Partner]
- ,Query_Base.[Short _Text_Elimination] AS [BL Short Text Elim.]
- ,Query_Base.[Cross _Div _Biz_Elimination] AS [Cross Div. Biz. Elim.]
- ,Query_Base.[Business _Area_Elimination] AS [BA Elim.]
- ,Query_Base.[Business _Line_Elimination] AS [BL Elim.]
- ,Query_Base.[Spiridon _2019_Elimination] AS [Depth Structure SAP Elim.]
- ,Query_Base.[Business _Subsegment_Elimination] AS [Depth Structure ESPRIT Elim.]
- ,Query_Base.Funcionalidad_Elimination AS [PC Elim. Functionality]
- ,Query_Base.Plant AS [Plant] /*,Query_Base.Cuentas_Maestro_Curr_key as [Currency Key]*/
- ,Query_Base.Curr1key1of1PrCtr1LC AS [Currency Key]
- ,Query_Base.Debit_Credit_Ind AS [C/D Indicator]
- ,Query_Base.Cuentas_MaestroTransType AS [Transaction Type]
- ,Query_Base.Trading_Partner AS [Trading Partner]
- ,Query_Base.Periodo AS [Periodo]
- FROM [1RQ_BPCSQL1].[dbo].[Query_Base]
- LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group] Q ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= Q.inicio
- AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= Q.fin
- WHERE isnull(TRY_CAST([1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta AS INT), 0) BETWEEN 50000000
- AND 89999999
- AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
- AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 1
- AND 12
Mageshwaran RPosted Nov 5, 2019, 10:26 AM
Rajanikant HawaldarPosted Sep 18, 2019, 5:55 AM