Prepare Trial Balance using Oracle Managed Cloud data

user15708

We have migrated Oracle Managed Cloud data to DOMO and now trying to develop a Trial balance report.

 

I have added an attached query to create Dataset. When I run the data set it is running continuously and no completion.

 

I tried creating a small query to run but still the same issue. I am using MYSQL for the query. The same query is working fine from the Oracle database quickly.

 

We have approx 3.3. M records

 

select

  gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)

    from `fmc_gl_balances`

    where ledger_id =  gb.`LEDGER_ID`

      and `ACTUAL_FLAG` = 'A'

      and `PERIOD_YEAR` = gb.`PERIOD_YEAR`

      and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`

      and `PERIOD_NUM` =  gb.`PERIOD_NUM`-1

      group by ledger_id,

              period_year,

              period_num,

              code_combination_id) begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` between 2 and 12

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

UNION

select

 gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)

    from `fmc_gl_balances`

    where ledger_id =  gb.`LEDGER_ID`

      and `ACTUAL_FLAG` = 'A'

      and `PERIOD_YEAR` = gb.`PERIOD_YEAR`-1

      and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`

      and `PERIOD_NUM` =  12

      group by ledger_id,

              period_year,

              period_num,

              code_combination_id) begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` = 1

and substring(gc.`SEGMENT2`,1,1) in ('1','2','3')

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

UNION

select

 gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, 0 begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` = 1

and substring(gc.`SEGMENT2`,1,1) in ('4','5','6','7','8')

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

jaeW_at_Onyx

This is not a Domo problem, this looks like a SQL optimization problem.

Do you have any indexes?  Have you tried paring down your query until it runs?

 

If you're not good at optimizing SQL, have you considered using MagicETL?  There's nothing you're doing in your SQL that you can't do in Magic, and Magic will ultimately probably be more performant given the transforms you're doing.