Clinton,
I can see the point when friendlyVU (???) spoke about getting details of the appropriate parent. "Appropriate" being the operative word. (Lazy loading)
In all our database applications, we have *seldom* required the need to work off a list of
Categories then require to display (use) all the children of the different Categories viz. all the
different products of the *different* Categories. We never needed it somehow.
What we have required, however, is the proces the other way round. Where we work off a list of Products
and while busy with them we require the details of their Categories. So, it is n+1 the other way
round???
Currently we are using a second select with a IN statement containing a comma delimited entries
of the unique parents. We then do the "mapping" in code.
What are your thoughts on this?
Kind regards
Abdullah
Clinton Begin wrote:
Vic,
I'm not sure what you're point was, but N+1 selects is THE most common relational databases performance problem caused by application developers today.
Understanding it is critical if you want to avoid making this mistake. Not every application will have the luxury of simply being 1:1 Table:Screen. In fact, I've almost never seen this to be the case in enterprise applications.
Consider a 3 tier hierarchy like in JPetStore: Categories -1---M- Products -1----M- Items.
For illustration purposes, the number of queries to load this data with an immediate or lazy load strategy is:
1 * count(categories) * count(Products)
<SNIPPED>
