I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3
I turned on logging for the SQL queries to test some code behavior.
So, assume 2 tables, OneToMany relation. In the code, this will have fields on the objects to represent it.
So for instance, class A will have a list of class B instances as a field.
Suppose I want to get those instances of class B, that are related to class A.
I could simply do
Or, I could write an Ebean function that asks for the ID of A and gets all the instances from the B table where A is present. The result is the same.
I did both, to see what SQL query Ebean would generate. And it does generate a different one.
The first, asking for the instances related to the object, will get Ebean to generate a statement with a left outer join based on the ID of the instance of A.
The second will do a select on the B table.
I'm wondering about the performance of this. On my test setup, which is fairly small, the left outer join is always faster. But I wonder about production databases that are constantly being updated. I did some googling in to the performance of left outer join and it's pretty much all bad things I've found.
So, what's the hidden cost here? Will left outer join works fast at first but mess things up later? Will the simple select statement prove more reliable, if a bit slower?