Can you help me understand why SQL Transform takes so long to run and how I can make it faster?

giannzi
giannzi Member
in SQL DataFlows

I tried selecting only the necessary columns and implemented the indexing tips I found in the community, but it is still running for a long time.

image.png

image.png image.png image.png

Answers

  • ggenovese
    ggenovese Coach

    I'd recommend that you break up some of the work into separate transforms. For example your B sub query could be a separate transform that's joined in to the main transform. You can also pre-filter A in it's own transform and then do your joins.

    After you have parts segmented out into separate transforms you can review the execution times of each transform to see which parts are taking longest and make modifications.

    Finally, I'd recommend leaving your dataset in a more granular state and letting analyzer do the aggregations instead. This may ultimately be a more flexible design, and I find that Analyzer can perform aggregations very quickly.

  • MichelleH
    MichelleH Coach

    I agree with all of @ggenovese's recommendations for optimizing the SQL. If you're still not satisfied with the performance after implementing those suggestions, you could also consider replicating this in Magic ETL (still following the previous guidance). I've seen some significant performance improvements since migrating SQL dataflows to Magic ETL, most notably since Magic can process multiple steps concurrently, while the SQL transforms can only be run sequentially.

  • ArborRose
    ArborRose Coach

    I was so disappointed in Domo's SQL performance, I stopped using it. Any time SQL is mentioned in association with Domo, I throw up a little.

    You can break large queries into smaller transforms. And try to avoid large cross joins. But the performance is just not acceptable. Magic ETL is optimized for Domo's backend, tightly integrated and highly parallelized. Whereas SQL Transforms seem to be executed on a different layer, adding overhead and latency.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories