Optimizing simple join

1

I have this simple join that is bogging down my query. Any suggestions on how to improve it?

Thank you!

join
inmast on (intran.fac + intran.fpartno + intran.fcpartrev) = (inmast.fac + inmast.fpartno + inmast.frev)

2

If your objective is to pick the rows that match the fac, fpartno and partrev, then change the join condition to

nmast ON intran.fac = inmast.fac AND intran.fpartno = inmast.fpartno AND intran.fcpartrev = inmast.frev

That, of course, would be logically incorrect if your objective really is to pick rows where the sum of the 3 columns match even if the individual pairs do not match.

If you are able to change the join condition as described above, then look at what indexes you have on the table. Indexes on the 3 columns involved in the join would help.

1 Like
3

This worked James.

Thanks for your help!