ETL so much faster than SQL at joining

Not so much a question but a statement of my experience with DOMO

I am not a SQL person (if that is a thing). I had only been using SQL to join datasets where ETL would run into the 10 000 repeats limit that would prevent it from running a Left Join.

I later realised that you cna go around the 10000 limit by using a Right Join but at that stage I already had some dataflows using SQL to do some joints.

In an attempt to see if I could speed up some dataflows (or series of dataflows) I tried right joining instead of SQL and the difference quite remarkables.

1 data set went from 22min to 3 min, another from 3 min to 40 sec.

I would recommend trying this to anyone looking at reducing processign times, or just normal practice.

Find more posts tagged with

Comments

eric_tetik

Hi @rado98,

A reason for that drop in run time could be because the dataset that is on the "right" of the join could have significantly less rows than on the left, thus bringing through less rows in total, less rows to process later in the ETL, etc. Have you noticed a drop in row count in the ETLs where you've changed from a Left Join to a Right Join?

This blog post explains the differences between an inner join, left join and right join very well.

Regards,

Eric

rado98

The operatinos are escentially the same and the outputs is exactly the same.

Joining usign SQL 22min

Joining using Magic ETL 3min

Mr_53

In our experience MySQL is a little tricky to optimize with indexing, so we have switched to redshift which handles the indexing for us, and we have seen dramatic speed increase even over ETLs. Redshift also has the added benifit of choosing only the columns you want to bring in on the input itself which has sped up our queries as well.

Randyb

The reason for that is more of an underlying structural component of DOMO. When you run SQL statements there are two things that DOMO does or doesn't do that impacts the run time of the SQL.

First, the resources it applies to the SQL run is not as great as the resources that are applied to anything that is run in the ETL.

The other component, which is the real impact is that when the SQL is running it basically runs in order, so the first step is running, everything else waits until its finished, then it steps down to the next layer.

When its running in the ETL, DOMO has designed their system to truely optimize all resources, meaning that almost all of the processes run at the same time, some finishing sooner than others and then compiles the results when all are done.

If you take a look at a running ETL process you will see all of the components showing as running or completed and there is no specific order in which they appear, the last in the line could be the first to finish.

So yes, the ETL by design is much faster.

Hope that helps explain what is going on in the background and why there is such a difference in the run time of complex routines.

Randy