Hibernate N+1 SELECTs vs Fetch JOINS: The truth depends.

I recently came across a blog @ http://java.dzone.com/articles/hibernate-tuning-queries-using?page=0,0 explaining why the fashionable practice of prefering Fetch JOINS to N+1 SELECTs can be flawed (see last page).

However, the post is flawed as it doesn’t take into account the effect of the number of items per iteration and the number of iterations, as vell as the type of objects relations. The result can vary either way.

For example, to iterate across 2000 items, I could perform 10 iterations of size 200 or 20 iterations of size 100, 50 iterations of size 40 and so on.

For this test I used an Object that contained a one-to-one subobject of another type.  Here are the timings in milliseconds.

No Iterations:

N+1:1156

Fetch JOIN: 359

Number of iterations: 10, number of docs/iteration: 200

N+1:  1250

Fetch JOIN: 391

Number of iterations: 400, number of docs/iteration: 5

N+1: 1907

Fetch JOIN: 2375

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s