We are having a debate in our company. I use left outer joins quit frequently. I was told by another programmer that there is too much over head and I should not use them. Example, Lets say I have two tables, they would rather me hit the database twice get the information I need from each table store it in memory and join the data on the java side, instead of just doing a left outer join. I think this is absurd and would be bad for performance. Am I wrong
+3
A:
You are not wrong. Using a join is most likely to be more efficient. This is what databases are good at.
Mark Byers
2010-03-02 20:36:09
That was my exact argument to the programmer.
Doug
2010-03-02 21:05:22
+1
A:
My belief is that you should avoid hitting the database twice when you don't need to. I fail to see where a left join would be more of a problem than asking for the data twice. If the FKs are not indexed there could be a performance problem, but that is poor design not a fault of the left joins.
Eventually the same amount of data would have to be returned one way or the other and databases are designed to use joins.
That said, it could be that the way you are writing the queries might not be efficient (using left joins when inner joins are needed or joining to tables not needed at all), without seeing them I have no way to know that.
HLGEM
2010-03-02 20:37:42
I use left outer joins if I want data from the left table to always be there and want data from the right table only if it exist. I would use an inner join if the data must exist in both tables to be returned.
Doug
2010-03-02 21:04:37
A:
We used to do joins in the application rather than in the database sometimes back in the 80s. But really, but 1988 or so the database optimizer was usually good enough with DB2 that wasn't necessary any more.
I can't think of any ordinary scenarios that warrant application-joins. Abnormal situations like when you've got an over-loaded database server, fat network pipe, a ton of capacity on the app server, and a bunch of new queries - might warrant it for some of the queries. Maybe.
But in general joins, including left/right/full outer joins are your friend. They allow you to have smaller tables, get better use out of both storage and memory, etc. But you do want up to date statistics, to have a passing familiarity with the kinds of access paths a database uses and get in the habit of doing explains.
KenFar
2010-03-07 19:05:12