SQL Flow taking hours to run

mroker
mroker Member
in Magic ETL

I am trying to run the following flow using the following SQL code:


select adv.`Account`, adv.`LDGRDATE` Adv_Ledgerdate, cre.`LDGRDATE` Prorate_LedgerDate, adv.`Total_Amnt`, 

cre.`Total_Amnt` Prorate_Amount, adv.`Ledger Type`, cre.`Ledger Type` Prorate

from `test_grp` adv

left outer join `credit_prorate` cre ON cre.`Account` = adv.`Account` 

and month(cre.`LDGRDATE`) = month(adv.`LDGRDATE` - interval 1 month) 


The first table is about 599K rows, the second is 300K rows. When running this query in the sample it runs fine, however when running it for the whole flow, it just runs and doesn't stop (has been running now for about 5 hours).

Can anyone assist with why this wouldn't work? Does DOMO not do well with joins? Or are the tables too big?

Comments

  • JacobFolsom
    JacobFolsom Domo Employee
    edited October 2022

    This will run faster is you utilize indexing in MySQL (help article), which would require a transform for each of your tables to create two new columns for the month extracts you are joining on...e.g. LDGRDATE_Month and LDGRDATE_PriorMonth. Once those columns are created, you can add an index to them prior to joining them in your statement above, which should improve runtime.

    I would also recommend utilizing Magic ETL 2 and using the formula tile to extract the month parts and then joining on those columns, this would likely be the most optimal runtime.

    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • trafalger
    trafalger Coach

    To give some context, I’ve seen MySQL dataflows take 4-5 hours and then their Magic 2.0 replacement take 10-15 minutes. Unless you’re doing like a stored procedure Magic 2.0 is the way to go.

  • mroker
    mroker Member

    Thanks so much for the great advice on solving this issue! I will look into using Magic 2.0.

Categories