You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by friendVU admin <ma...@friendvu.com> on 2005/02/09 12:39:17 UTC
What is N+1
What is n+1?
.V
Re: What is N+1
Posted by Brice Ruth <bd...@gmail.com>.
Vic, probably isn't an issue for you since you only use HashMaps ;)
On Wed, 09 Feb 2005 08:12:32 -0600, Vic Cekvenich <vi...@friendvu.com> wrote:
> Which I could do with a join on SQL side. I can't see how this is
> something I'd use or how it' scale, but.... I guess somone uses it.
> THANKS for the education.
> .V
>
> Kris Jenkins wrote:
>
> >
> >
> > The N+1 selects problem is when you query for n parents, and you need
> > one extra query for the children. Internally, this happens:
> >
> > List parents = sqlMap.queryForList( "getParents", parameter );
> > for ( Iterator i = parents.iterator(); i.hasNext(); ) {
> > Parent p = (Parent) i.next();
> > List children = sqlMap.queryForList( "getChild", p.getId() );
> > p.setChildren( children );
> > }
> >
> > So you end up with 1 query for the parent list, plus one extra for
> > each parent to get the children = n + 1. Rather inefficient.
> > This is the problem that 2.0.9 fixes.
> >
> > Sorry for the confusion.
> > Kris
> >
>
> --
> Forums, Boards, Blogs and News in RiA <http://www.boardVU.com>
>
Re: R: What is N+1
Posted by Michal Malecki <mi...@poczta.onet.pl>.
I wonder one thing, which Abdullah and I have already mentioned - what if
parent is big and has a lot of children. We are getting through the net n
times parent record. The most efficient would be, I think, to make 2
selects - one for the parent records, and one for all of the children, plus
the parent pk. But I'm afraid it would look ugly in the configuration.
Anyway, maybe we can invent something that would be both readable and
efficient?
Michal Malecki
> 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)
>
> So if we have 5 categories and 25 products (a relatively small data
set)...
> ...you'll generate 125 hits against your database.
>
> Now consider 25 categories and 125 products (still a very small data set),
> ...you'll generate 3125 hits against your database!!!
>
> You can see how this could pose a HUGE issue, even with only a small
> amount of data.
>
> With the N+1 selects solution, you can do this with one query, with
> one hit to the database -- no matter how many relationships you have
> or how much data.
>
> Trust me bud, this is VERY good advice.
>
> Cheers,
> Clinton
>
>
> On Wed, 09 Feb 2005 08:36:44 -0600, friendVU admin
> <ma...@friendvu.com> wrote:
> > I see.
> > I am not sure if that is good advice. I think if you click on a table
> > you get the detail, that makes sense.
> > Else... row processing is not for SQL, SQL is based on relational
> > algebra and Set Theory and Venn diagrams, ie... you work on a set of
> > records, not cursor processing.
> > I am sure some may find limited use for it.... IMO, it's something to
> > avoid.
> >
> > .V
> >
> > Fabrizio Gianneschi wrote:
> >
> > >The reason is explained well in the SQLMaps documentation:
> > >
> > >
> >
> >
Re: R: What is N+1
Posted by Huy <hu...@swiftdsl.com.au>.
friendVU admin wrote:
[...]d
>
> Ex: A common thing I used to consult on before Java was Performance and
> Tuning SQL DBs. A client would have a slow querry, and they say make it
> fast. I can! I used to take things that take 15 minutes down to split
> second. One tecquique is to break a complex querry into 2 simpler
> querries with less joins; even using a temporary tables. This
> exponentialy increased peformance to sub second.
> (also.. you avoid group by, distinct, order by... imagine sorting 30
> million records BEFORE you start to returun... and that you have 10,000
> concurent users each runing a variation on the querry. Lots of trick to
> get arround that).
I have experienced the same thing .V describes here. I had a query which
would have subselects counting certain things in the same table several
times with each subselect having joins to other tables. No amount of
indexing/joining was able to make it fast so selecting two sets of rows
and doing the operations manually speed it up dramatically.
Huy
> All the best,
>
> .V
>
> Clinton Begin wrote:
>
>> With the N+1 selects solution, you can do this with one query, with
>> one hit to the database -- no matter how many relationships you have
>> or how much data.
>>
>> Trust me bud, this is VERY good advice.
>>
>> Cheers,
>> Clinton
>>
>>
>>
>
>
>
Re: R: What is N+1
Posted by friendVU admin <ma...@friendvu.com>.
The 16 way join and put the DB in App. server RAM?
My experience on several terabytes DBs has been opposite, I hope you
trust me too :-D .
Getting a list of categories, then you click on a catagory and get a
list of products, then you click on a product and get a list of
components is much faster and less stress on a db(and therfore not only
each querry is faster but more scaleable); it reduces a # of joins.
Ex: A common thing I used to consult on before Java was Performance and
Tuning SQL DBs. A client would have a slow querry, and they say make it
fast. I can! I used to take things that take 15 minutes down to split
second. One tecquique is to break a complex querry into 2 simpler
querries with less joins; even using a temporary tables. This
exponentialy increased peformance to sub second.
(also.. you avoid group by, distinct, order by... imagine sorting 30
million records BEFORE you start to returun... and that you have 10,000
concurent users each runing a variation on the querry. Lots of trick to
get arround that).
All the best,
.V
Clinton Begin wrote:
>With the N+1 selects solution, you can do this with one query, with
>one hit to the database -- no matter how many relationships you have
>or how much data.
>
>Trust me bud, this is VERY good advice.
>
>Cheers,
>Clinton
>
>
>
Re: R: What is N+1
Posted by Clinton Begin <cl...@gmail.com>.
I'm not aware of any such optimization...
I do know that depending on the driver and database that you use, it's
generally one of three things that kills performance:
1) Connection, or transaction initiation (with Oracle this is a big deal)
2) The actual JOIN itself (mostly a DB design issue, outer join
performance etc.)
3) ResultSet iteration and data retrieval (lots of factors here,
width, network, disk I/O etc)
Generally, N+1 solutions work well where #2 is not an issue, but #1
and #3 are. This is more often the case because "most" databases are
designed with JOIN performance in mind.
Otherwise, multiple requests via lazy or immediate loading works if
you have (#1 & 3) superfast I/O, a good driver, pooled connections,
and a simple application design that is basically 1:1 table:screen.
I'll admit (for Vic), I've worked with databases so bad that I've done
in-memory joins of JavaBeans after retrieving table data separately.
I even have a neat little BeanJoin utility that does joins on
JavaBeans using their properies!
Cheers,
Clinton
On Wed, 09 Feb 2005 18:16:33 +0200, Abdullah Kauchali
<ab...@isanusi.com> wrote:
>
> >Clinton wrote: N+1 selects the other way around is a 1:1 or an M:1 relationship
> >(child has ref to parent), which iBATIS has always supported through
> >nested property mappings in the resultMap...for example:
> >
> ><result property "product.id" column"PRODUCT_ID" />
> ><result property "product.name" column"PRODUCT_NAME" />
> >
> >Doing the mapping in code is fine, it's just more work for you. It
> >gets a lot harder when you have 2 or 3 relationships....which iBATIS
> >supports out of the box.
> >
> >
> >
> Yes! :) Can I ask another question regarding simple joins: do you
> have any knowledge
> of JDBC drivers doing some sort of optimisations to avoid duplicating
> the parent
> table's records. (In an earlier post we spoke about the increase in
> payload size and was
> wondering if JDBC itself handled the "normalised" (if-u-will) resultset
> in transit?)
>
> >Overall, each application will have different needs. Some will work
> >best with immediate load (multiple queries, same connection), otherw
> >will work best with lazy load (multiple queries, different
> >connection), and others still will require the N+1 selects solution
> >(single query, single connection).
> >
> >I just wanted to make sure nobody was painting all solutions with the
> >same brush.... ;-)
> >
> >
> For sure!
>
>
Re: R: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
>Clinton wrote: N+1 selects the other way around is a 1:1 or an M:1 relationship
>(child has ref to parent), which iBATIS has always supported through
>nested property mappings in the resultMap...for example:
>
><result property "product.id" column"PRODUCT_ID" />
><result property "product.name" column"PRODUCT_NAME" />
>
>Doing the mapping in code is fine, it's just more work for you. It
>gets a lot harder when you have 2 or 3 relationships....which iBATIS
>supports out of the box.
>
>
>
Yes! :) Can I ask another question regarding simple joins: do you
have any knowledge
of JDBC drivers doing some sort of optimisations to avoid duplicating
the parent
table's records. (In an earlier post we spoke about the increase in
payload size and was
wondering if JDBC itself handled the "normalised" (if-u-will) resultset
in transit?)
>Overall, each application will have different needs. Some will work
>best with immediate load (multiple queries, same connection), otherw
>will work best with lazy load (multiple queries, different
>connection), and others still will require the N+1 selects solution
>(single query, single connection).
>
>I just wanted to make sure nobody was painting all solutions with the
>same brush.... ;-)
>
>
For sure!
Re: R: What is N+1
Posted by Clinton Begin <cl...@gmail.com>.
Hi Abdullah,
N+1 selects the other way around is a 1:1 or an M:1 relationship
(child has ref to parent), which iBATIS has always supported through
nested property mappings in the resultMap...for example:
<result property "product.id" column"PRODUCT_ID" />
<result property "product.name" column"PRODUCT_NAME" />
Doing the mapping in code is fine, it's just more work for you. It
gets a lot harder when you have 2 or 3 relationships....which iBATIS
supports out of the box.
Overall, each application will have different needs. Some will work
best with immediate load (multiple queries, same connection), otherw
will work best with lazy load (multiple queries, different
connection), and others still will require the N+1 selects solution
(single query, single connection).
I just wanted to make sure nobody was painting all solutions with the
same brush.... ;-)
Cheers,
clinton
On Wed, 09 Feb 2005 17:32:36 +0200, Abdullah Kauchali
<ab...@isanusi.com> wrote:
> 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>
> >
> >
>
Re: R: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
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>
>
>
Re: R: What is N+1
Posted by Clinton Begin <cl...@gmail.com>.
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)
So if we have 5 categories and 25 products (a relatively small data set)...
...you'll generate 125 hits against your database.
Now consider 25 categories and 125 products (still a very small data set),
...you'll generate 3125 hits against your database!!!
You can see how this could pose a HUGE issue, even with only a small
amount of data.
With the N+1 selects solution, you can do this with one query, with
one hit to the database -- no matter how many relationships you have
or how much data.
Trust me bud, this is VERY good advice.
Cheers,
Clinton
On Wed, 09 Feb 2005 08:36:44 -0600, friendVU admin
<ma...@friendvu.com> wrote:
> I see.
> I am not sure if that is good advice. I think if you click on a table
> you get the detail, that makes sense.
> Else... row processing is not for SQL, SQL is based on relational
> algebra and Set Theory and Venn diagrams, ie... you work on a set of
> records, not cursor processing.
> I am sure some may find limited use for it.... IMO, it's something to
> avoid.
>
> .V
>
> Fabrizio Gianneschi wrote:
>
> >The reason is explained well in the SQLMaps documentation:
> >
> >
>
>
Re: R: What is N+1
Posted by friendVU admin <ma...@friendvu.com>.
I see.
I am not sure if that is good advice. I think if you click on a table
you get the detail, that makes sense.
Else... row processing is not for SQL, SQL is based on relational
algebra and Set Theory and Venn diagrams, ie... you work on a set of
records, not cursor processing.
I am sure some may find limited use for it.... IMO, it's something to
avoid.
.V
Fabrizio Gianneschi wrote:
>The reason is explained well in the SQLMaps documentation:
>
>
R: What is N+1
Posted by Fabrizio Gianneschi <fa...@gruppoatlantis.com>.
The reason is explained well in the SQLMaps documentation:
"Lazy Loading vs. Joins (1:1)
Its important to note that using a join is not always better. If you are in
a situation where it is rare to
access the related object [-the Child-] then it might actually be faster to
avoid the join and the unnecessary loading of all [-Child-] properties. This
is especially true for database
designs that involve outer joins or nullable and/or non-indexed columns. In
these situations it might be
better to use the sub-select solution with the lazy loading and bytecode
enhancement options enabled (see
SQL Map Config settings). The general rule of thumb is: use the join if you
re more likely going to access
the associated properties than not. Otherwise, only use it if lazy loading
is not an option."
Fabrizio
-----Messaggio originale-----
Da: Vic Cekvenich [mailto:vin@friendvu.com]
Inviato: mercoledì 9 febbraio 2005 15.13
A: ibatis-user-java@incubator.apache.org
Oggetto: Re: What is N+1
Which I could do with a join on SQL side. I can't see how this is
something I'd use or how it' scale, but.... I guess somone uses it.
THANKS for the education.
.V
Kris Jenkins wrote:
>
>
> The N+1 selects problem is when you query for n parents, and you need
> one extra query for the children. Internally, this happens:
>
> List parents = sqlMap.queryForList( "getParents", parameter );
> for ( Iterator i = parents.iterator(); i.hasNext(); ) {
> Parent p = (Parent) i.next();
> List children = sqlMap.queryForList( "getChild", p.getId() );
> p.setChildren( children );
> }
>
> So you end up with 1 query for the parent list, plus one extra for
> each parent to get the children = n + 1. Rather inefficient.
> This is the problem that 2.0.9 fixes.
>
> Sorry for the confusion.
> Kris
>
--
Forums, Boards, Blogs and News in RiA <http://www.boardVU.com>
Re: What is N+1
Posted by Vic Cekvenich <vi...@friendvu.com>.
Which I could do with a join on SQL side. I can't see how this is
something I'd use or how it' scale, but.... I guess somone uses it.
THANKS for the education.
.V
Kris Jenkins wrote:
>
>
> The N+1 selects problem is when you query for n parents, and you need
> one extra query for the children. Internally, this happens:
>
> List parents = sqlMap.queryForList( "getParents", parameter );
> for ( Iterator i = parents.iterator(); i.hasNext(); ) {
> Parent p = (Parent) i.next();
> List children = sqlMap.queryForList( "getChild", p.getId() );
> p.setChildren( children );
> }
>
> So you end up with 1 query for the parent list, plus one extra for
> each parent to get the children = n + 1. Rather inefficient.
> This is the problem that 2.0.9 fixes.
>
> Sorry for the confusion.
> Kris
>
--
Forums, Boards, Blogs and News in RiA <http://www.boardVU.com>
Re: What is N+1
Posted by Kris Jenkins <kr...@yahoo.co.uk>.
Kris Jenkins wrote:
> friendVU admin wrote:
>
>> What is n+1?
>> .V
>
>
>
> n++! :-P
>
> It's when each child object in a complex query has to be selected
> seperately. If 'parent' has 10 'children', you end up with 10+1
> select statements being issued. See the developer's guide under
> 'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
> new groupBy goodness to banish the N+1 problem.
>
> Kris
Yikes! I'm not thinking straight this morning. Sorry!
The N+1 selects problem is when you query for n parents, and you need
one extra query for the children. Internally, this happens:
List parents = sqlMap.queryForList( "getParents", parameter );
for ( Iterator i = parents.iterator(); i.hasNext(); ) {
Parent p = (Parent) i.next();
List children = sqlMap.queryForList( "getChild", p.getId() );
p.setChildren( children );
}
So you end up with 1 query for the parent list, plus one extra for each
parent to get the children = n + 1. Rather inefficient.
This is the problem that 2.0.9 fixes.
Sorry for the confusion.
Kris
--
Kris Jenkins
Email: kris@jenkster.com
Blog: http://cafe.jenkster.com/
Wiki: http://wiki.jenkster.com/
Re: What is N+1
Posted by Michal Malecki <mi...@poczta.onet.pl>.
That's question which I am asking to myself for some time now. I am afraid
that it is strongly dependant on the case (db engine, size of parent and
child, how many children has parent). Using n+1 killer (groupby in ibatis)
is much cleaner in Java code, co I think it's better, unless developing some
mission critical application.
Michal Malecki
ps one question - do we need to have sorted result by the grouped by column?
> Ok, the flip-side of this approach is that you increase the size of the
> returned result by N*(size of the lookup).
>
> So if the lookup is 10 bytes and you are fetching 10 records of the
> child, your payload size is 100 bytes
> instead of 10 bytes. ( I wonder if JDBC makes some internal
> optimisations - keeps a pointer or something
> to same record if duplicates exist?)
>
> That's not a problem per se, but which one is better:
>
> 1. This approach or ...
> 2. Using an "in clause" and making another round-trip?
>
> Cheers
>
> Abdullah
>
>
> Michal Malecki wrote:
>
> >Well, I think you need only simple join in your sql, no extra features of
> >ibatis needed:
> >select * from CHILD, PARENT where parent_id=PARENT.id;
> >am I right?
> >Michal Malecki
> >
> >
> >
> >>What about relationships from the child up to the parent? Are they also
> >>classified as N+1?
> >>
> >>Suppose we have child table with a fk of parent_id.
> >>
> >>if I do:
> >>
> >> SELECT * from CHILD
> >>
> >>I'll get a column with parent_id with values.
> >>
> >>Now suppose I also want details pertaining to the parent_id along with
> >>the recordset of Child, I need not
> >>do 10 separate selects for the parent ... is this also an N+1 problem?
> >>
> >>What is the preferred iBatis model for this?
> >>
> >>Cheers
> >>
> >>Abdullah
> >>
> >>
> >>Kris Jenkins wrote:
> >>
> >>
> >>
> >>>friendVU admin wrote:
> >>>
> >>>
> >>>
> >>>>What is n+1?
> >>>>.V
> >>>>
> >>>>
> >>>
> >>>n++! :-P
> >>>
> >>>It's when each child object in a complex query has to be selected
> >>>seperately. If 'parent' has 10 'children', you end up with 10+1
> >>>select statements being issued. See the developer's guide under
> >>>'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
> >>>new groupBy goodness to banish the N+1 problem.
> >>>
> >>>Kris
> >>>
> >>>
> >>>
> >
> >
> >
> >
> >
>
Re: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
Qualification:
> Ok, the flip-side of this approach is that you increase the size of
> the returned result by N*(size of the lookup).
>
Where N = number of records with *unique* parents.
Re: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
Ok, the flip-side of this approach is that you increase the size of the
returned result by N*(size of the lookup).
So if the lookup is 10 bytes and you are fetching 10 records of the
child, your payload size is 100 bytes
instead of 10 bytes. ( I wonder if JDBC makes some internal
optimisations - keeps a pointer or something
to same record if duplicates exist?)
That's not a problem per se, but which one is better:
1. This approach or ...
2. Using an "in clause" and making another round-trip?
Cheers
Abdullah
Michal Malecki wrote:
>Well, I think you need only simple join in your sql, no extra features of
>ibatis needed:
>select * from CHILD, PARENT where parent_id=PARENT.id;
>am I right?
>Michal Malecki
>
>
>
>>What about relationships from the child up to the parent? Are they also
>>classified as N+1?
>>
>>Suppose we have child table with a fk of parent_id.
>>
>>if I do:
>>
>> SELECT * from CHILD
>>
>>I'll get a column with parent_id with values.
>>
>>Now suppose I also want details pertaining to the parent_id along with
>>the recordset of Child, I need not
>>do 10 separate selects for the parent ... is this also an N+1 problem?
>>
>>What is the preferred iBatis model for this?
>>
>>Cheers
>>
>>Abdullah
>>
>>
>>Kris Jenkins wrote:
>>
>>
>>
>>>friendVU admin wrote:
>>>
>>>
>>>
>>>>What is n+1?
>>>>.V
>>>>
>>>>
>>>
>>>n++! :-P
>>>
>>>It's when each child object in a complex query has to be selected
>>>seperately. If 'parent' has 10 'children', you end up with 10+1
>>>select statements being issued. See the developer's guide under
>>>'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
>>>new groupBy goodness to banish the N+1 problem.
>>>
>>>Kris
>>>
>>>
>>>
>
>
>
>
>
Re: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
I knew I was missing something simple ... doh! :)
Michal Malecki wrote:
>Well, I think you need only simple join in your sql, no extra features of
>ibatis needed:
>select * from CHILD, PARENT where parent_id=PARENT.id;
>am I right?
>Michal Malecki
>
>
>
>>What about relationships from the child up to the parent? Are they also
>>classified as N+1?
>>
>>Suppose we have child table with a fk of parent_id.
>>
>>if I do:
>>
>> SELECT * from CHILD
>>
>>I'll get a column with parent_id with values.
>>
>>Now suppose I also want details pertaining to the parent_id along with
>>the recordset of Child, I need not
>>do 10 separate selects for the parent ... is this also an N+1 problem?
>>
>>What is the preferred iBatis model for this?
>>
>>Cheers
>>
>>Abdullah
>>
>>
>>Kris Jenkins wrote:
>>
>>
>>
>>>friendVU admin wrote:
>>>
>>>
>>>
>>>>What is n+1?
>>>>.V
>>>>
>>>>
>>>
>>>n++! :-P
>>>
>>>It's when each child object in a complex query has to be selected
>>>seperately. If 'parent' has 10 'children', you end up with 10+1
>>>select statements being issued. See the developer's guide under
>>>'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
>>>new groupBy goodness to banish the N+1 problem.
>>>
>>>Kris
>>>
>>>
>>>
>
>
>
>
>
Re: What is N+1
Posted by Kris Jenkins <kr...@yahoo.co.uk>.
>
> Without groupBy in your resultMap, you'll end up with 3 parent
> objects, each with one child. What you wanted was 2 parents, one with
> three children.
Sorry, that should read 'end up with 4 parent objects'. I'm clearly
having a bad day...
Re: What is N+1
Posted by Michal Malecki <mi...@poczta.onet.pl>.
Not exactly - Abdullah fliped the problem and wanted to selected children,
and parent for each other. Then you don't need group by. When you want
parent and list of children - then of course you are right.
Michal Malecki
>
> >Well, I think you need only simple join in your sql, no extra features of
> >ibatis needed:
> >select * from CHILD, PARENT where parent_id=PARENT.id;
> >am I right?
> >
> Well, that's the right query, but only if you use groupBy. Assuming
> you're trying to get Parent objects populated with their correct Child
> objects, you query will return something like:
>
> parent_id | child_id
> ----------+---------
> 1 | a
> 1 | b
> 1 | c
> 2 | d
>
> Without groupBy in your resultMap, you'll end up with 3 parent objects,
> each with one child. What you wanted was 2 parents, one with three
> children.
>
> So you're right, groupBy allows you to do this with one query, but
> before v2.0.9 you'd've needed two separate ones to get the right result.
>
> Kris
>
> --
> Kris Jenkins
> Email: kris@jenkster.com
> Blog: http://cafe.jenkster.com/
> Wiki: http://wiki.jenkster.com/
>
>
>
Re: What is N+1
Posted by Kris Jenkins <kr...@yahoo.co.uk>.
>Well, I think you need only simple join in your sql, no extra features of
>ibatis needed:
>select * from CHILD, PARENT where parent_id=PARENT.id;
>am I right?
>
Well, that's the right query, but only if you use groupBy. Assuming
you're trying to get Parent objects populated with their correct Child
objects, you query will return something like:
parent_id | child_id
----------+---------
1 | a
1 | b
1 | c
2 | d
Without groupBy in your resultMap, you'll end up with 3 parent objects,
each with one child. What you wanted was 2 parents, one with three
children.
So you're right, groupBy allows you to do this with one query, but
before v2.0.9 you'd've needed two separate ones to get the right result.
Kris
--
Kris Jenkins
Email: kris@jenkster.com
Blog: http://cafe.jenkster.com/
Wiki: http://wiki.jenkster.com/
Re: What is N+1
Posted by Michal Malecki <mi...@poczta.onet.pl>.
Well, I think you need only simple join in your sql, no extra features of
ibatis needed:
select * from CHILD, PARENT where parent_id=PARENT.id;
am I right?
Michal Malecki
> What about relationships from the child up to the parent? Are they also
> classified as N+1?
>
> Suppose we have child table with a fk of parent_id.
>
> if I do:
>
> SELECT * from CHILD
>
> I'll get a column with parent_id with values.
>
> Now suppose I also want details pertaining to the parent_id along with
> the recordset of Child, I need not
> do 10 separate selects for the parent ... is this also an N+1 problem?
>
> What is the preferred iBatis model for this?
>
> Cheers
>
> Abdullah
>
>
> Kris Jenkins wrote:
>
> > friendVU admin wrote:
> >
> >> What is n+1?
> >> .V
> >
> >
> >
> > n++! :-P
> >
> > It's when each child object in a complex query has to be selected
> > seperately. If 'parent' has 10 'children', you end up with 10+1
> > select statements being issued. See the developer's guide under
> > 'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
> > new groupBy goodness to banish the N+1 problem.
> >
> > Kris
> >
>
Re: What is N+1
Posted by Abdullah Kauchali <ab...@isanusi.com>.
What about relationships from the child up to the parent? Are they also
classified as N+1?
Suppose we have child table with a fk of parent_id.
if I do:
SELECT * from CHILD
I'll get a column with parent_id with values.
Now suppose I also want details pertaining to the parent_id along with
the recordset of Child, I need not
do 10 separate selects for the parent ... is this also an N+1 problem?
What is the preferred iBatis model for this?
Cheers
Abdullah
Kris Jenkins wrote:
> friendVU admin wrote:
>
>> What is n+1?
>> .V
>
>
>
> n++! :-P
>
> It's when each child object in a complex query has to be selected
> seperately. If 'parent' has 10 'children', you end up with 10+1
> select statements being issued. See the developer's guide under
> 'Avoiding N+1 selects' for the full lowdown. Version 2.0.9 comes with
> new groupBy goodness to banish the N+1 problem.
>
> Kris
>
Re: What is N+1
Posted by Kris Jenkins <kr...@yahoo.co.uk>.
friendVU admin wrote:
> What is n+1?
> .V
n++! :-P
It's when each child object in a complex query has to be selected
seperately. If 'parent' has 10 'children', you end up with 10+1 select
statements being issued. See the developer's guide under 'Avoiding N+1
selects' for the full lowdown. Version 2.0.9 comes with new groupBy
goodness to banish the N+1 problem.
Kris
--
Kris Jenkins
Email: kris@jenkster.com
Blog: http://cafe.jenkster.com/
Wiki: http://wiki.jenkster.com/