none
Query Output

    Domanda

  • Hello,

    I have one query which i need to fine tune. There are 3 CTE's that are coded as below.

    I have a doubt on 3rd CTE and i think it is resulting a cartesian product. when i execute the 3rd CTE i end up resulting in 1 lac rows.. but when i rewrite the 3rd CTE by making use of Temp tables i am resulting in 20000 records. am not sure if my query is correct or the one with CTE. 

    can anyone please share your thoughts.. Query with temp tables is at the last..

    ;WITH
         CTE_PERIOD_LIMIT (SRC_SYSTEM
                              ,CIE
                              ,FISCAL_YEAR
                              ,MIN_PERIOD
                              ,MAX_PERIOD
                              )
         AS (SELECT [DI_TABA].SOURCE_SYSTEM
                   ,[DI_TABA].CIE
                   ,[DI_TABA].FISCAL_YEAR 
                   ,MIN(CAST([DI_TABA].PERIOD AS INT)) AS MIN_PERIOD
                   ,MAX(CAST([DI_TABA].PERIOD AS INT)) AS MAX_PERIOD
              FROM TABA AS [DI_TABA]
             WHERE ISNUMERIC([DI_TABA].PERIOD) = 1
             GROUP BY [DI_TABA].SOURCE_SYSTEM
                     ,[DI_TABA].CIE
                     ,[DI_TABA].FISCAL_YEAR 
            )
        ,CTE_T_BAL_PERIOD_LIMIT (SRC_SYSTEM
                                        ,CIE
                                        ,ACCOUNT
                                        ,DEPARTMENT
                                        ,PRODUCT_LINE
                                        ,TRADING_PARTNER
                                        ,FISCAL_YEAR
                                        ,TRANS_CURRENCY
                                        ,LOCAL_CURRENCY
                                        ,MIN_PERIOD
                                        ,MAX_PERIOD
                                        )
         AS (SELECT [DI_TABA].SOURCE_SYSTEM
                   ,[DI_TABA].CIE
                   ,[DI_TABA].ACCOUNT
                   ,[DI_TABA].DEPARTMENT
                   ,[DI_TABA].PRODUCT_LINE
                   ,[DI_TABA].TRADING_PARTNER
                   ,[DI_TABA].FISCAL_YEAR 
                   ,[DI_TABA].TRANS_CURRENCY
                   ,MAX([DI_TABA].LOCAL_CURRENCY) AS LOCAL_CURRENCY
                   ,MIN(CAST(CTE_PERIOD_LIMIT.MIN_PERIOD AS INT)) AS MIN_PERIOD
                   ,MAX(CAST(CTE_PERIOD_LIMIT.MAX_PERIOD AS INT)) AS MAX_PERIOD
              FROM [TABA] AS [DI_TABA]
              LEFT JOIN CTE_PERIOD_LIMIT
                ON CTE_PERIOD_LIMIT.SOURCE_SYSTEM = [DI_TABA].SOURCE_SYSTEM
               AND CTE_PERIOD_LIMIT.CIE = [DI_TABA].CIE
               AND CTE_PERIOD_LIMIT.FISCAL_YEAR = [DI_TABA].FISCAL_YEAR
             WHERE ISNUMERIC([DI_TABA].PERIOD) = 1
             GROUP BY [DI_TABA].SOURCE_SYSTEM
                     ,[DI_TABA].CIE
                     ,[DI_TABA].ACCOUNT
                     ,[DI_TABA].DEPARTMENT
                     ,[DI_TABA].PRODUCT_LINE
                     ,[DI_TABA].TRADING_PARTNER
                     ,[DI_TABA].FISCAL_YEAR 
                     ,[DI_TABA].TRANS_CURRENCY
            )
        ,CTE_A_PER_BY_YEAR (SOURCE_SYSTEM
                                ,CIE
                                ,ACCOUNT
                                ,DEPARTMENT
                                ,PRODUCT_LINE
                                ,TRADING_PARTNER
                                ,FISCAL_YEAR
                                ,PERIOD
                                ,MIN_PERIOD
                                ,MAX_PERIOD
                                ,TRANS_CURRENCY
                                ,LOCAL_CURRENCY
                                )
         AS (SELECT CTE_T_BAL_PERIOD_LIMIT.SOURCE_SYSTEM
                   ,CTE_T_BAL_PERIOD_LIMIT.CIE
                   ,CTE_T_BAL_PERIOD_LIMIT.ACCOUNT
                   ,CTE_T_BAL_PERIOD_LIMIT.DEPARTMENT
                   ,CTE_T_BAL_PERIOD_LIMIT.PRODUCT_LINE
                   ,CTE_T_BAL_PERIOD_LIMIT.TRADING_PARTNER
                   ,CTE_T_BAL_PERIOD_LIMIT.FISCAL_YEAR
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD AS PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MAX_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.TRANS_CURRENCY
                   ,CTE_T_BAL_PERIOD_LIMIT.LOCAL_CURRENCY
               FROM CTE_T_BAL_PERIOD_LIMIT
             UNION ALL
             SELECT CTE_A_PER_BY_YEAR.SOURCE_SYSTEM
                   ,CTE_A_PER_BY_YEAR.CIE
                   ,CTE_A_PER_BY_YEAR.ACCOUNT
                   ,CTE_A_PER_BY_YEAR.DEPARTMENT
                   ,CTE_A_PER_BY_YEAR.PRODUCT_LINE
                   ,CTE_A_PER_BY_YEAR.TRADING_PARTNER
                   ,CTE_A_PER_BY_YEAR.FISCAL_YEAR
                   ,CTE_A_PER_BY_YEAR.PERIOD + 1 AS PERIOD
                   ,CTE_A_PER_BY_YEAR.MIN_PERIOD
                   ,CTE_A_PER_BY_YEAR.MAX_PERIOD
                   ,CTE_A_PER_BY_YEAR.TRANS_CURRENCY
                   ,CTE_A_PER_BY_YEAR.LOCAL_CURRENCY
               FROM CTE_A_PER_BY_YEAR
              WHERE CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD
            )

    -- revisited query - breaking in to 2 diff queries

    SELECT CTE_T_BAL_PERIOD_LIMIT.SOURCE_SYSTEM
                   ,CTE_T_BAL_PERIOD_LIMIT.CIE
                   ,CTE_T_BAL_PERIOD_LIMIT.ACCOUNT
                   ,CTE_T_BAL_PERIOD_LIMIT.DEPARTMENT
                   ,CTE_T_BAL_PERIOD_LIMIT.PRODUCT_LINE
                   ,CTE_T_BAL_PERIOD_LIMIT.TRADING_PARTNER
                   ,CTE_T_BAL_PERIOD_LIMIT.FISCAL_YEAR
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD AS PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MAX_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.TRANS_CURRENCY
                   ,CTE_T_BAL_PERIOD_LIMIT.LOCAL_CURRENCY into #TempA
               FROM CTE_T_BAL_PERIOD_LIMIT

               SELECT CTE_A_PER_BY_YEAR.SOURCE_SYSTEM
                   ,CTE_A_PER_BY_YEAR.CIE
                   ,CTE_A_PER_BY_YEAR.ACCOUNT
                   ,CTE_A_PER_BY_YEAR.DEPARTMENT
                   ,CTE_A_PER_BY_YEAR.PRODUCT_LINE
                   ,CTE_A_PER_BY_YEAR.TRADING_PARTNER
                   ,CTE_A_PER_BY_YEAR.FISCAL_YEAR
                   ,CTE_A_PER_BY_YEAR.PERIOD + 1 AS PERIOD
                   ,CTE_A_PER_BY_YEAR.MIN_PERIOD
                   ,CTE_A_PER_BY_YEAR.MAX_PERIOD
                   ,CTE_A_PER_BY_YEAR.TRANS_CURRENCY
                   ,CTE_A_PER_BY_YEAR.LOCAL_CURRENCY
               FROM #TempA  as CTE_A_PER_BY_YEAR
              WHERE CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD

    After splitting the CTE's to use temp tables the output rows are reduced drastically. Is my assumption correct?



    Sri


    mercoledì 21 marzo 2018 16:31

Risposte

  • IS my above approach correct? ( like re writing the CTE query to populate temp tables and doing a Union All Join)

    Sorry didnt understand how a recursive logic can be replaced by just a UNION ALL. Where is the iteration in this case? I dont think the output will be same in this case compared to recursive logic

    If you want to replace recursive logic using CTE, there has to be some kind of iteration logic using a WHILE loop or something. The only other way is by using a tally table or number table approach



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Contrassegnato come risposta Sridhar Gaddam mercoledì 21 marzo 2018 18:40
    mercoledì 21 marzo 2018 18:26

Tutte le risposte

  • No , you didn't change query the same way. That is the reason why you are getting less no of rows while using temp table.

    Output from  CTE_T_BAL_PERIOD_LIMIT is inserted into #TempA but CTE_A_PER_BY_YEAR output is not used in re-written query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    mercoledì 21 marzo 2018 16:50
  • In addition to what Sarat(SS) said, your CTE is recursive and hence produces more records. Your split query uses records from the CTE_T_BAL_PERIOD_LIMIT and then filters them for CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD which is in the same table hence you get less records.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    mercoledì 21 marzo 2018 17:22
  • Thanks for the reply 

    SELECT SOURCE_SYSTEM
                   ,CIE
                   ,ACCOUNT
                   ,DEPARTMENT
                   ,PRODUCT_LINE
                   ,TRADING_PARTNER
                   ,FISCAL_YEAR
                   ,MIN_PERIOD AS PERIOD
                   ,MIN_PERIOD
                   ,MAX_PERIOD
                   ,TRANS_CURRENCY
                   ,LOCAL_CURRENCY FROM #TEMPA

    UNION ALL

               SELECT CTE_A_PER_BY_YEAR.SOURCE_SYSTEM 

                ,CTE_A_PER_BY_YEAR.CIE

                   ,CTE_A_PER_BY_YEAR.ACCOUNT
                   ,CTE_A_PER_BY_YEAR.DEPARTMENT
                   ,CTE_A_PER_BY_YEAR.PRODUCT_LINE
                   ,CTE_A_PER_BY_YEAR.TRADING_PARTNER
                   ,CTE_A_PER_BY_YEAR.FISCAL_YEAR
                   ,CTE_A_PER_BY_YEAR.PERIOD + 1 AS PERIOD
                   ,CTE_A_PER_BY_YEAR.MIN_PERIOD
                   ,CTE_A_PER_BY_YEAR.MAX_PERIOD
                   ,CTE_A_PER_BY_YEAR.TRANS_CURRENCY
                   ,CTE_A_PER_BY_YEAR.LOCAL_CURRENCY
               FROM #TempA  as CTE_A_PER_BY_YEAR
              WHERE CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD

    The above results in 20 K records where as the CTE used(recursive) results more than 1 lac rows.

    My question is .. IS my above approach correct? ( like re writing the CTE query to populate temp tables and doing a Union All Join)


    Sri

    mercoledì 21 marzo 2018 17:47
  • What is query used to select out from final CTE (CTE_A_PER_BY_YEAR) in your initial query.

    That might explain why you are getting different result sets.

    In your reply you added UNION ALL between same  #TempA table ( in second part of UNION ALL , renamed  #TempA to CTE_A_PER_BY_YEAR) , not sure why you are expecting complete result set with recursive CTE.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    mercoledì 21 marzo 2018 18:20
  • IS my above approach correct? ( like re writing the CTE query to populate temp tables and doing a Union All Join)

    Sorry didnt understand how a recursive logic can be replaced by just a UNION ALL. Where is the iteration in this case? I dont think the output will be same in this case compared to recursive logic

    If you want to replace recursive logic using CTE, there has to be some kind of iteration logic using a WHILE loop or something. The only other way is by using a tally table or number table approach



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Contrassegnato come risposta Sridhar Gaddam mercoledì 21 marzo 2018 18:40
    mercoledì 21 marzo 2018 18:26
  • Hi Sarat..

    if you notice in my second query there is a were clause with a condition WHERE CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD and in select i have period + 1 (  ,CTE_A_PER_BY_YEAR.PERIOD + 1 AS PERIOD).

    To make it simpler.. how to rewrite the recursive CTE below by using Temp tables?

    CTE_A_PER_BY_YEAR (SOURCE_SYSTEM
                                ,CIE
                                ,ACCOUNT
                                ,DEPARTMENT
                                ,PRODUCT_LINE
                                ,TRADING_PARTNER
                                ,FISCAL_YEAR
                                ,PERIOD
                                ,MIN_PERIOD
                                ,MAX_PERIOD
                                ,TRANS_CURRENCY
                                ,LOCAL_CURRENCY
                                )
         AS (SELECT CTE_T_BAL_PERIOD_LIMIT.SOURCE_SYSTEM
                   ,CTE_T_BAL_PERIOD_LIMIT.CIE
                   ,CTE_T_BAL_PERIOD_LIMIT.ACCOUNT
                   ,CTE_T_BAL_PERIOD_LIMIT.DEPARTMENT
                   ,CTE_T_BAL_PERIOD_LIMIT.PRODUCT_LINE
                   ,CTE_T_BAL_PERIOD_LIMIT.TRADING_PARTNER
                   ,CTE_T_BAL_PERIOD_LIMIT.FISCAL_YEAR
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD AS PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MIN_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.MAX_PERIOD
                   ,CTE_T_BAL_PERIOD_LIMIT.TRANS_CURRENCY
                   ,CTE_T_BAL_PERIOD_LIMIT.LOCAL_CURRENCY
               FROM CTE_T_BAL_PERIOD_LIMIT
             UNION ALL
             SELECT CTE_A_PER_BY_YEAR.SOURCE_SYSTEM
                   ,CTE_A_PER_BY_YEAR.CIE
                   ,CTE_A_PER_BY_YEAR.ACCOUNT
                   ,CTE_A_PER_BY_YEAR.DEPARTMENT
                   ,CTE_A_PER_BY_YEAR.PRODUCT_LINE
                   ,CTE_A_PER_BY_YEAR.TRADING_PARTNER
                   ,CTE_A_PER_BY_YEAR.FISCAL_YEAR
                   ,CTE_A_PER_BY_YEAR.PERIOD + 1 AS PERIOD
                   ,CTE_A_PER_BY_YEAR.MIN_PERIOD
                   ,CTE_A_PER_BY_YEAR.MAX_PERIOD
                   ,CTE_A_PER_BY_YEAR.TRANS_CURRENCY
                   ,CTE_A_PER_BY_YEAR.LOCAL_CURRENCY
               FROM CTE_A_PER_BY_YEAR
              WHERE CTE_A_PER_BY_YEAR.PERIOD + 1 <= CTE_A_PER_BY_YEAR.MAX_PERIOD
            )

    Select * from CTE_A_PER_BY_YEAR 


    Sri

    mercoledì 21 marzo 2018 18:27
  • Select * from CTE_A_PER_BY_YEAR 

    This wasn't present in your original post. Looks you are not apply any filter on this, thats ok.

    I think you wrote recursive part wrong. In recursive part your are comparing PERIOD and MAX_PERIOD against CTE and no where CTE_T_BAL_PERIOD_LIMIT used. 

    check this https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-cte-basics/

     


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    mercoledì 21 marzo 2018 18:35