SQL Flow taking hours to run
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
-
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"3 -
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.
3 -
Thanks so much for the great advice on solving this issue! I will look into using Magic 2.0.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive