locked
query cause insufficient disk space in tempdb in production RRS feed

  • Question

  • HI, 

    The following query will cause insufficient disk space tempdb in production, the last join is where the problem is, those tables has like 40 million to 90 million records inside, it will run forever, eventually it will cause space issue for tempdb.

    All i need to do is make sure it will not blow up tempdb, so maybe instead of CTE, i can use real table instead? or i should use view with filter then join view together, instead of use real table with filter like what is been used in query.

    Thanks in advance

    WITH 
    OBISUBWITH0 AS (select D1.c4 as c1
    from 
         (select distinct D1.c1 as c1,
                   D1.c2 as c2,
                   D1.c3 as c3,
                   D1.c4 as c4
              from 
                   (select T11536.FISCAL_PERIOD_ID as c1,
                             T11536.FISCAL_PERIOD_ID - 100 as c2,
                             T11536.FISCAL_YEAR_ID * 100 + 12 as c3,
                             T11536.FISCAL_YEAR_ID * 100 + 12 - 100 as c4,
                             ROW_NUMBER() OVER (PARTITION BY T11536.FISCAL_PERIOD_ID ORDER BY T11536.FISCAL_PERIOD_ID) as c5
                        from 
                             JDEDW.D_FISCAL_DATE T11536
                        where  ( T11536.DATE_DT = {fn TimestampAdd(SQL_TSI_DAY, -(datepart(day,convert(datetime, '2016-09-26', 120))), convert(datetime, '2016-09-26', 120))} and T11536.FISCAL_PATTERN_ID = 1 ) 
                   ) D1
              where  ( D1.c5 = 1 ) 
         ) D1),
    OBISUBWITH1 AS (select D1.c1 as c1
    from 
         (select distinct T745.RPT_CODE_46_DESC as c1
              from 
                   JDEDW.D_BUSINESS_UNIT T745,
                   
                   JDEDW.R_SECURITY_BRIDGE T123086 /* R_Security_Bridge_BU */ 
              where  ( T745.BUSINESS_UNIT_ID = T123086.ALLOWED_VALUE_ID and T745.RPT_CODE_45_SRCCD = 'COO' and T123086.SECURITY_TYPE_SRCCD = 'BU' and T123086.USER_ACCOUNT_SRCCD = 'ALL' and T123086.ACTIVE_FLAG = 1 and (T745.RPT_CODE_46_SRCCD = 'ABS' or T745.RPT_CODE_46_SRCCD = 'AVI' or T745.RPT_CODE_46_SRCCD = 'BNI' or T745.RPT_CODE_46_SRCCD = 'CGS' or T745.RPT_CODE_46_SRCCD = 'EDU' or T745.RPT_CODE_46_SRCCD = 'HLC' or T745.RPT_CODE_46_SRCCD = 'TEC') ) 
         ) D1),
    OBICOMMON0 AS (select T1123.FISCAL_PERIOD_ID as c3,
         T1123.FISCAL_PATTERN_ID as c4,
         T1123.FISCAL_YEAR_ID as c5,
         ROW_NUMBER() OVER (PARTITION BY T1123.FISCAL_YEAR_ID ORDER BY T1123.FISCAL_YEAR_ID DESC) as c6,
         ROW_NUMBER() OVER (PARTITION BY T1123.FISCAL_YEAR_ID, T1123.FISCAL_PERIOD_ID ORDER BY T1123.FISCAL_YEAR_ID DESC, T1123.FISCAL_PERIOD_ID DESC) as c7,
         T1123.FISCAL_PERIOD_END_DT as c8
    from 
         JDEDW.D_FISCAL_PERIOD T1123
    where  ( T1123.FISCAL_PATTERN_ID = 1 ) ),
    SAWITH0 AS (select Case when case D1.c6 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case when ( case D1.c6 when 1 then D1.c3 else NULL end  ) is null then 1 else 0 end, case D1.c6 when 1 then D1.c3 else NULL end ) end as c1,
         Case when case D1.c7 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY D1.c5 ORDER BY case when ( case D1.c7 when 1 then D1.c3 else NULL end  ) is null then 1 else 0 end, case D1.c7 when 1 then D1.c3 else NULL end ) end as c2,
         D1.c3 as c3,
         D1.c4 as c4,
         D1.c5 as c5
    from 
         OBICOMMON0 D1),
    SAWITH1 AS (select min(D1.c1) over (partition by D1.c5)  as c1,
         min(D1.c2) over (partition by D1.c5, D1.c3)  as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from 
         SAWITH0 D1),
    SAWITH2 AS (select distinct D1.c1 + 1 as c1,
         D1.c2 as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from 
         SAWITH1 D1),
    SAWITH3 AS (select Case when case D1.c6 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case when ( case D1.c6 when 1 then D1.c3 else NULL end  ) is null then 1 else 0 end, case D1.c6 when 1 then D1.c3 else NULL end ) end as c1,
         Case when case D1.c7 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY D1.c5 ORDER BY case when ( case D1.c7 when 1 then D1.c3 else NULL end  ) is null then 1 else 0 end, case D1.c7 when 1 then D1.c3 else NULL end ) end as c2,
         D1.c3 as c3,
         D1.c8 as c4,
         D1.c5 as c5
    from 
         OBICOMMON0 D1),
    SAWITH4 AS (select distinct min(D1.c1) over (partition by D1.c5)  as c1,
         min(D1.c2) over (partition by D1.c5, D1.c3)  as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from 
         SAWITH3 D1)
         
         
     --select * from SAWITH4
    select sum(case  when T94054.LEDGER_TYPE_SRCCD = 'AA' and T94054.BEG_BAL_FLAG = 0 then T94054.LEDGER_CURR_PERIOD_AMT else 0 end ) as c1,
         sum(case  when T94054.LEDGER_TYPE_SRCCD = 'AA' and T94054.BEG_BAL_FLAG = 1 then T94054.LEDGER_CURR_PERIOD_AMT else 0 end ) as c2,
         T745.RPT_CODE_46_SRCCD as c3,
         T745.RPT_CODE_46_DESC as c4,
         D6.c4 as c5,
         D6.c3 as c6
    from 
         JDEDW.D_ACCOUNT T10978,
         
         JDEDW.D_BUSINESS_UNIT T745,
         
         JDEDW.R_SECURITY_BRIDGE T123086 /* R_Security_Bridge_BU */ ,
         
         JDEDW.F_ACCT_LEDGER_FP_AGGR2 T94054,
         
         JDEDW.R_SECURITY_BRIDGE T123070 /* R_Security_Bridge_Fact */ ,
         
         SAWITH2 D8,
         
         SAWITH4 D6
    where  ( T745.BUSINESS_UNIT_ID = T94054.BUSINESS_UNIT_ID and T745.BUSINESS_UNIT_ID = T123086.ALLOWED_VALUE_ID and T10978.ACCOUNT_ID = T94054.ACCOUNT_ID and T10978.SOURCE_SYSTEM_ID = T94054.SOURCE_SYSTEM_ID and T94054.BUSINESS_UNIT_ID = T123070.ALLOWED_VALUE_ID and T94054.FISCAL_PERIOD_ID = D8.c3 and T94054.FISCAL_PATTERN_ID = D8.c4 and T123070.SECURITY_TYPE_SRCCD = 'BU' and T123070.USER_ACCOUNT_SRCCD = 'ALL' and T745.RPT_CODE_45_SRCCD = 'COO' and T123070.ACTIVE_FLAG = 1 and T123086.SECURITY_TYPE_SRCCD = 'BU' and T123086.USER_ACCOUNT_SRCCD = 'ALL' and T123086.ACTIVE_FLAG = 1 and D6.c1 = D8.c1 and D6.c2 >= D8.c2 and (T745.RPT_CODE_46_SRCCD = 'ABS' or T745.RPT_CODE_46_SRCCD = 'AVI' or T745.RPT_CODE_46_SRCCD = 'BNI' or T745.RPT_CODE_46_SRCCD = 'CGS' or T745.RPT_CODE_46_SRCCD = 'EDU' or T745.RPT_CODE_46_SRCCD = 'HLC' or T745.RPT_CODE_46_SRCCD = 'TEC') and T745.RPT_CODE_46_DESC in (select distinct D1.c1 as c1
    from 
         OBISUBWITH1 D1) and T10978.OBJECT_SRCCD between '30000' and '39999' and D6.c3 in (select distinct D1.c1 as c1
    from 
         OBISUBWITH0 D1) ) 
    group by T745.RPT_CODE_46_DESC, T745.RPT_CODE_46_SRCCD, D6.c3, D6.c4


    • Edited by jimmyji168 Wednesday, September 28, 2016 8:51 PM
    Wednesday, September 28, 2016 6:38 PM

All replies

  • Basically you just have to play with it and see what can make it run more efficiently. Start with a simple SELECT after the last CTE. Select each of the CTE data. If all of that is quick, then start taking stuff away from the last WHERE clause or GROUP BY and see what happens. Execute the subqueries separately and make sure that they are efficient.

    And see how big the tables are (how much space do they use - google sql table space) and how much free space is there on your tempdb drive?

    • Proposed as answer by Naomi N Wednesday, September 28, 2016 8:14 PM
    • Unproposed as answer by jimmyji168 Wednesday, September 28, 2016 8:51 PM
    Wednesday, September 28, 2016 7:57 PM
  • and of course run an execution  plan.
    Wednesday, September 28, 2016 8:07 PM
  • Post the estimated and when possible actual execution plan.
    Wednesday, September 28, 2016 8:07 PM
  • how i can show the whole execution plan, it is a big one
    Wednesday, September 28, 2016 8:45 PM
  • i already added some none clustered index per recommend by execution plan
    Wednesday, September 28, 2016 9:11 PM
  • try running each of the CTEs independently first. I am guessing that "ROW_NUMBER() OVER" might be an issue if it is a large table (many rows and wide).
    Wednesday, September 28, 2016 9:12 PM
  • each cte all return result quickly, but the last join killed everything
    Wednesday, September 28, 2016 9:15 PM
  • Have you tried to change CTE to #temptable query? Check and let's see the result. Of course, you can try to optimize the query. For example, use join in the last query.
    Thursday, September 29, 2016 3:03 AM
  • Well, when you want help, then you should provide the necessary information..

    post the execution plan.

    Thursday, September 29, 2016 9:38 AM