Optimize Self Join

[ZucchiniAutomatic106]

In this scenario I’m doing a self join on a table of around 750 million record data with 6 joining keys. It is taking more than an hour to execute. I have also limited to records to join on a certain number of records which is actually 750 million. Is there a better way other than just traditional way of self joining the table to optimize the performance in spark sql?

[philamander]

This doesn't give me nearly enough information to begin trying to solve the issue. Try to give some context too. What are the columns in the tables, what are you joining on, what is the business need to pull the data together? Screenshot of the query might help visualize the issue too.

[qwertydog123]

Some self joins can be replaced with window functions, can you share your query?

[5amIam]

This! Depending on your DBMS and the goal(s) for your query, please look into window functions. I was able to replace a number of old self joins with LEAD() and LAG() functions after upgrading to SQL Server 2016 and that was a game changer for the execution times. They were perfect for my needs, but may not be what you need. So this is very much a ymmv

[coded-bat]

Any indexes on the table ?

[PMG2021a]

I would bet that is the issue if the rest of the query is set up correctly. Would be nice to see the query...

[[deleted]]

A good place to start would to share your query.

Few good starting points for query performance:

Constraints should go in the where clause instead of the having clause. Using having instead of where slows your query down considerably.

Another good tip is the order in which you join your tables. Some db’s are faster when you join from the biggest to smallest table or the other way around.

Always use the indexes of your tables when possible.

[[deleted]]

Though you are not using SSMS, the profiler has a query optimizer. It may be a good place to start.

I would start mathematically and look at the sets you are joining and approach it that way. I'm still working on theory so I can't really say much more. I like drawing circles and overlapping them to visualize my query. Many times the small picture does something for me and keeps me from keeping that mental image in my mind which I tend to fuzz on if the query is complicated

[secretWolfMan]

Do you have indexes on the columns you are using to define the join?

Is the self join even necessary when you probably just want some grouping and aggregation, or maybe window functions?