MySQL Recursion Performance/Speed Issues

etszyingho

Hello! I am looking for suggestions on how to improve the performance of my mysql dataflow.

Currently I have a recursive dataflow built with a Google Analytics connector, and I'm using a concatenated key created by joining multiple columns together to delete and upsert new data from the historical dataset.

I'm using the following syntax in a SQL transform function:

DELETE from historical_data WHERE concat_key IN (SELECT concat_key FROM new_data);

The recursion works but it takes really long to run (50+ minutes for 82K rows of total input). I'm currently manually running it and the historical dataset has about 44K and new data has about 38K. Has anyone had experience working with mySQL recursions and can suggest a different way to create a recursive dataflow that increases the speed?

MarkSnodgrass

You may want to review this KB article regarding a recursive MySQL dataflow. You can avoid having to execute a delete statement, which is likely causing your timing issues.

https://domohelp.domo.com/hc/en-us/articles/360043428133-Creating-a-Recursive-Snapshot-SQL-DataFlow

MarkSnodgrass

You may want to review this KB article regarding a recursive MySQL dataflow. You can avoid having to execute a delete statement, which is likely causing your timing issues.

https://domohelp.domo.com/hc/en-us/articles/360043428133-Creating-a-Recursive-Snapshot-SQL-DataFlow

etszyingho

Thank you @MarkSnodgrass !

I thought you would find it amusing to know that this helped reduce the processing time for my dataflow from 1h 17m to 53s.

MarkSnodgrass

Awesome @etszyingho !!!

MarkSnodgrass

@etszyingho if you can mark the answer as accepted, that will help others in the community. Great to see the massive improvement!

GrantSmith

Hi @etszyingho

I'd recommend utilizing ETL 2.0 if at all possible as it will be more performant than MySQL dataflows but it looks like you're at a good spot with your current performance with @MarkSnodgrass 's solution.

https://domohelp.domo.com/hc/en-us/articles/360057087393-Creating-a-Recursive-Snapshot-DataFlow-in-Magic-ETL-v2-Beta-