SQL Flow taking hours to run

mroker

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?

JacobFolsom

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.

trafalger

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

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