max_fetch_depth confusion

stolley · **Posted:** Thu May 06, 2004 4:00 am

From the 2.1.3 doco:

Quote:

Set a maximum "depth" for the outer join fetch tree for single-ended associations (one-to-one, many-to-one). A 0 disables default outer join fetching.

Found in the forums, posted by Gavin:

Quote:

max_fetch_depth specifies the maximum depth we will ever fetch into the object graph. This can occasionally be slightly unpredictable. It certainly does not put a bound in the number of outer joins!

http://forum.hibernate.org/viewtopic.php?t=928842

So, now I'm really confused.
Can someone please exaplain to me the difference between these two things:
"setting maximum depth of the outer joing fetch tree"
and
"putting a bound in the number of outer joins"

The FAQ says that setting max_fetch_depth to a "smaller value" can help solve the issue of

Quote:

"Hibernate generates too many outer joins"

.

Anyway, the original reason I started looking at this is that I wanted to know why the recommended max for this setting is 3?
Back when I thought I knew what this setting meant, it occurred to me that 3 is a pretty low number and I was wondering why the Hibernate team recommend it as the maximum we should use?

Also, what's the default? If I don't set the property at all, is it just unlimited?

stolley · **Posted:** Tue May 11, 2004 10:40 pm

boink

shl · **Posted:** Wed May 12, 2004 3:22 am

stolley wrote:

Can someone please exaplain to me the difference between these two things:
"setting maximum depth of the outer joing fetch tree"
and
"putting a bound in the number of outer joins"

stolley, English is not my native language :), but as I got those two expression mean the same, that is "setting maximum depth of the outer joing fetch tree"=="putting a bound in the number of outer joins"

stolley wrote:

The FAQ says that setting max_fetch_depth to a "smaller value" can help solve the issue of

Quote:

"Hibernate generates too many outer joins"

.

For example if you use MySQL or some other RDMS with limited number of outer joins per SQL request you need to "put a bound in the number of outer joins"

stolley wrote:

Anyway, the original reason I started looking at this is that I wanted to know why the recommended max for this setting is 3?
Back when I thought I knew what this setting meant, it occurred to me that 3 is a pretty low number and I was wondering why the Hibernate team recommend it as the maximum we should use?

I think that magic number 3 was chosen maybe because all supported RDBMS that knows about outer joins support 3 outer joins per SQL request.

stolley wrote:

Also, what's the default? If I don't set the property at all, is it just unlimited?

In Hibernate 2.1.2 as I know the default value is infinity :)
With PostreSQL our team uses the default value of max_fetch_depth, we ran some performance test with max_fetch_depth set to 3, 5, 10 and decided that devault (unlimited) value for the PostreSQL is the optimal one

Best regards,
Leonid

michael · **Posted:** Wed May 12, 2004 8:18 am

"Maximum fetched Graph depth" is not necessarily the same as "maximum number of outer joins". If fetching 3 objects deep, you may still get more than 3 outer joins, for example if you have joined-subclass mappings.

3 is a sensible default, because if you fetch deeper, the fetched result sets get really large ...