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 James Hillyerd <ja...@hillyerd.com> on 2006/01/31 22:59:57 UTC
Problem with avoiding N+1 queries on complex collection property
I tried to search the archives for any answers to this question, but
for some reason it won't let you search for "N+1" and "complex" didn't
find the answer I'm looking for.
I have a set of queries that is successfully loading an object graph
that looks like:
Client:
ContactInfo:
Address
PhoneNumber (Collection)
Using the following SQL Map:
<sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format" namespace="ClientExport">
<resultMap id="clientMap" class="com.activerain.model.Client">
<result property="clientID" column="client_id"/>
<result property="source" column="source"/>
<result property="folderName" column="folder_name"/>
<result property="insertDate" column="insert_date"/>
<result property="contactInfo.contactInfoID" column="contact_info_id"/>
<result property="contactInfo.firstName" column="first_name"/>
<result property="contactInfo.lastName" column="last_name"/>
<result property="contactInfo.organization" column="organization"/>
<result property="contactInfo.emailAddress" column="email_address"/>
<result property="contactInfo.phoneNumbers" column="contact_info_id"
select="getClientExportPhoneNumbersByContact"/>
<result property="contactInfo.address.addressID" column="address_id"/>
<result property="contactInfo.address.addressLine1"
column="address_line1"/>
<result property="contactInfo.address.addressLine2"
column="address_line2"/>
<result property="contactInfo.address.zipCode" column="zip_code"/>
<result property="contactInfo.address.state" column="state"/>
<result property="contactInfo.address.city" column="city"/>
</resultMap>
<resultMap id="phoneNumberMap" class="com.activerain.model.PhoneNumber">
<result property="phoneNumberID" column="phone_number_id"/>
<result property="phoneNumberType" column="phone_number_type_id"/>
<result property="areaCode" column="area_code"/>
<result property="phoneNumber" column="phone_number"/>
<result property="extension" column="extension"/>
</resultMap>
<select id="getClientExportByUserID" resultMap="clientMap">
select
c.client_id,
c.source,
c.folder_name,
c.insert_date,
ci.contact_info_id,
ci.first_name,
ci.last_name,
ci.organization,
ci.email_address,
a.address_id,
a.address_line1,
a.address_line2,
a.city,
a.state,
a.zip_code
from client c
inner join contact_info ci on c.contact_info_id = ci.contact_info_id
inner join address a on ci.address_id = a.address_id
where
c.user_id = #value#
and c.is_deleted = false
order by c.client_id
</select>
<select id="getClientExportPhoneNumbersByContact"
resultMap="phoneNumberMap">
select
p.phone_number_id,
p.phone_number_type_id,
p.area_code,
p.phone_number,
p.extension
from phone_number p
where
p.contact_info_id = #value#
and p.is_deleted = false
</select>
</sqlMap>
The above map works fine, but is very slow due to performing an extra
query per client to load the phone numbers.
When I change the SQL Map to use groupBy (below), my phone number list
always gets set to null on the ContactInfo object (not an empty list,
just null). I cannot find any errors in the log, and when I attach
the debugger, I can see iBATIS creating instances of my PhoneNumber
object, and _sometimes_ calling setPhoneNumberID(), but never any of
the other setters. I've run the database query by hand and can verify
that it is returning the expected results.
If it helps, I'm running iBATIS 2.1.5 build 582 (which appears to be
the most recent non-beta build at this time).
Here is my broken SQL Map:
<sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format" namespace="ClientExport">
<!--
We're only going to populate the parts of the Client object
hierarchy that we need,
reducing the amount of work to keep these resultMap's in sync with
the real Client
class and database tables.
-->
<resultMap id="clientMap" class="com.activerain.model.Client"
groupBy="clientID">
<result property="clientID" column="client_id"/>
<result property="source" column="source"/>
<result property="folderName" column="folder_name"/>
<result property="insertDate" column="insert_date"/>
<result property="contactInfo.contactInfoID" column="contact_info_id"/>
<result property="contactInfo.firstName" column="first_name"/>
<result property="contactInfo.lastName" column="last_name"/>
<result property="contactInfo.organization" column="organization"/>
<result property="contactInfo.emailAddress" column="email_address"/>
<result property="contactInfo.phoneNumbers"
resultMap="ClientExport.phoneNumberMap"/>
<result property="contactInfo.address.addressID" column="address_id"/>
<result property="contactInfo.address.addressLine1" column="address_line1"/>
<result property="contactInfo.address.addressLine2" column="address_line2"/>
<result property="contactInfo.address.zipCode" column="zip_code"/>
<result property="contactInfo.address.state" column="state"/>
<result property="contactInfo.address.city" column="city"/>
</resultMap>
<resultMap id="phoneNumberMap" class="com.activerain.model.PhoneNumber">
<result property="phoneNumberID" column="phone_number_id"/>
<result property="phoneNumberType" column="phone_number_type_id"/>
<result property="areaCode" column="area_code"/>
<result property="phoneNumber" column="phone_number"/>
<result property="extension" column="extension"/>
</resultMap>
<select id="getClientExportByUserID" resultMap="clientMap">
select
c.client_id,
c.source,
c.folder_name,
c.insert_date,
ci.contact_info_id,
ci.first_name,
ci.last_name,
ci.organization,
ci.email_address,
a.address_id,
a.address_line1,
a.address_line2,
a.city,
a.state,
a.zip_code,
p.phone_number_id,
p.phone_number_type_id,
p.area_code,
p.phone_number,
p.extension
from client c
inner join contact_info ci on c.contact_info_id = ci.contact_info_id
inner join address a on ci.address_id = a.address_id
left join phone_number p on ci.contact_info_id = p.contact_info_id
where
c.user_id = #value#
and c.is_deleted = false
and p.is_deleted = false
order by c.client_id, p.phone_number_id
</select>
</sqlMap>
Thanks for any help you can provide.
-james
--
James A. Hillyerd <ja...@hillyerd.com>
Chief Technical Officer - ActiveRain Corp
Re: Problem with avoiding N+1 queries on complex collection property
Posted by Brandon Goodin <br...@gmail.com>.
#1 After thinking about it, I agree an empty list would be better
#2 Following is our unit test that succeeds. From looking at your
configs I could see that the problem is related to the nesting depth
(contactInfo.phoneNumbers). In our unit test we'll have to add a test
to our test case that mimics this issue and resolve it. Between
yourself and Tony can you guys check to see if an issue exists for
this and then create one if it does not. Currently the only way to
resolve this is your original solution.
Successful unit test configs:
<resultMap id="categoryResult" class="testdomain.Category" groupBy="categoryId">
<result property="categoryId" column="catid"/>
<result property="name" column="catname"/>
<result property="description" column="catdescn"/>
<result property="productList" resultMap="productResult"/>
</resultMap>
<resultMap id="productResult" class="testdomain.Product" groupBy="productId">
<result property="productId" column="productid"/>
<result property="categoryId" column="category"/>
<result property="name" column="prodname"/>
<result property="description" column="proddescn"/>
<result property="itemList" resultMap="itemResult"/>
</resultMap>
<select id="getAllCategories" resultMap="categoryResult" >
select
c.catid, c.name as catname, c.descn as catdescn,
p.productid, p.category, p.name as prodname,
p.descn as proddescn, i.itemid, i.productid,
i.listprice, i.unitcost, i.supplier, i.status,
i.attr1, i.attr2, i.attr3, i.attr4, i.attr5,
v.itemid, v.qty
from category c, product p, item i, inventory v
where c.catid = p.category
and p.productid = i.productid
and i.itemid = v.itemid
</select>
Brandon
On 2/1/06, James Hillyerd <ja...@hillyerd.com> wrote:
> I think an empty List would make more sense than null, but that's
> besides the point. It's always setting it to null, even when there
> are four phone numbers. I get no phone numbers period when I try to
> avoid N+1.
>
> -james
>
> On 2/1/06, Brandon Goodin <br...@gmail.com> wrote:
> > I'm curious here. If you want a List to always be there wouldn't it
> > make sense to instantiate a list and set it in the constructor of your
> > Client object? If you do this does ibatis set it to null when there
> > are no phone numbers? I would prefer that ibatis not make assumptions
> > about what default values that you want set on your object. If ibatis
> > is forcing it to null then i think that should be considered a bug and
> > entered into a bug report. If it does not then I think that it is the
> > developer's job insure defaults to be set.
> >
> > Brandon Goodin
> >
>
> --
> James A. Hillyerd <ja...@hillyerd.com>
> Chief Technical Officer - ActiveRain Corp
>
Re: Problem with avoiding N+1 queries on complex collection property
Posted by James Hillyerd <ja...@hillyerd.com>.
I think an empty List would make more sense than null, but that's
besides the point. It's always setting it to null, even when there
are four phone numbers. I get no phone numbers period when I try to
avoid N+1.
-james
On 2/1/06, Brandon Goodin <br...@gmail.com> wrote:
> I'm curious here. If you want a List to always be there wouldn't it
> make sense to instantiate a list and set it in the constructor of your
> Client object? If you do this does ibatis set it to null when there
> are no phone numbers? I would prefer that ibatis not make assumptions
> about what default values that you want set on your object. If ibatis
> is forcing it to null then i think that should be considered a bug and
> entered into a bug report. If it does not then I think that it is the
> developer's job insure defaults to be set.
>
> Brandon Goodin
>
--
James A. Hillyerd <ja...@hillyerd.com>
Chief Technical Officer - ActiveRain Corp
Re: Problem with avoiding N+1 queries on complex collection property
Posted by Brandon Goodin <br...@gmail.com>.
I'm curious here. If you want a List to always be there wouldn't it
make sense to instantiate a list and set it in the constructor of your
Client object? If you do this does ibatis set it to null when there
are no phone numbers? I would prefer that ibatis not make assumptions
about what default values that you want set on your object. If ibatis
is forcing it to null then i think that should be considered a bug and
entered into a bug report. If it does not then I think that it is the
developer's job insure defaults to be set.
Brandon Goodin
On 2/1/06, Tony Qian <da...@aol.com> wrote:
> James,
>
> I had same problem (posted before). seems it is a bug ( i didn't claim
> it was a bug before since I don't know if it was due to my fault in
> configuration file). Luckily, little bit slow was not big deal for the
> project I worked on.
>
> Can some one in the iBATIS group take a look at this issue?
>
> btw, I really liked iBATIS. Implemented in one of my project. Try to
> push other groups to use it too.
>
> thx,
> Tony
>
> James Hillyerd wrote on 1/31/2006, 4:59 PM:
>
> > I tried to search the archives for any answers to this question, but
> > for some reason it won't let you search for "N+1" and "complex" didn't
> > find the answer I'm looking for.
> >
> > I have a set of queries that is successfully loading an object graph
> > that looks like:
> >
> > Client:
> > ContactInfo:
> > Address
> > PhoneNumber (Collection)
> >
> > Using the following SQL Map:
> >
> > <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"
> > namespace="ClientExport">
> > <resultMap id="clientMap" class="com.activerain.model.Client">
> > <result property="clientID" column="client_id"/>
> > <result property="source" column="source"/>
> > <result property="folderName" column="folder_name"/>
> > <result property="insertDate" column="insert_date"/>
> > <result property="contactInfo.contactInfoID"
> > column="contact_info_id"/>
> > <result property="contactInfo.firstName" column="first_name"/>
> > <result property="contactInfo.lastName" column="last_name"/>
> > <result property="contactInfo.organization"
> > column="organization"/>
> > <result property="contactInfo.emailAddress"
> > column="email_address"/>
> > <result property="contactInfo.phoneNumbers"
> > column="contact_info_id"
> > select="getClientExportPhoneNumbersByContact"/>
> > <result property="contactInfo.address.addressID"
> > column="address_id"/>
> > <result property="contactInfo.address.addressLine1"
> > column="address_line1"/>
> > <result property="contactInfo.address.addressLine2"
> > column="address_line2"/>
> > <result property="contactInfo.address.zipCode"
> > column="zip_code"/>
> > <result property="contactInfo.address.state" column="state"/>
> > <result property="contactInfo.address.city" column="city"/>
> > </resultMap>
> >
> > <resultMap id="phoneNumberMap"
> > class="com.activerain.model.PhoneNumber">
> > <result property="phoneNumberID" column="phone_number_id"/>
> > <result property="phoneNumberType"
> > column="phone_number_type_id"/>
> > <result property="areaCode" column="area_code"/>
> > <result property="phoneNumber" column="phone_number"/>
> > <result property="extension" column="extension"/>
> > </resultMap>
> >
> > <select id="getClientExportByUserID" resultMap="clientMap">
> > select
> > c.client_id,
> > c.source,
> > c.folder_name,
> > c.insert_date,
> > ci.contact_info_id,
> > ci.first_name,
> > ci.last_name,
> > ci.organization,
> > ci.email_address,
> > a.address_id,
> > a.address_line1,
> > a.address_line2,
> > a.city,
> > a.state,
> > a.zip_code
> > from client c
> > inner join contact_info ci on c.contact_info_id =
> > ci.contact_info_id
> > inner join address a on ci.address_id = a.address_id
> > where
> > c.user_id = #value#
> > and c.is_deleted = false
> > order by c.client_id
> > </select>
> >
> > <select id="getClientExportPhoneNumbersByContact"
> > resultMap="phoneNumberMap">
> > select
> > p.phone_number_id,
> > p.phone_number_type_id,
> > p.area_code,
> > p.phone_number,
> > p.extension
> > from phone_number p
> > where
> > p.contact_info_id = #value#
> > and p.is_deleted = false
> > </select>
> > </sqlMap>
> >
> > The above map works fine, but is very slow due to performing an extra
> > query per client to load the phone numbers.
> >
> > When I change the SQL Map to use groupBy (below), my phone number list
> > always gets set to null on the ContactInfo object (not an empty list,
> > just null). I cannot find any errors in the log, and when I attach
> > the debugger, I can see iBATIS creating instances of my PhoneNumber
> > object, and _sometimes_ calling setPhoneNumberID(), but never any of
> > the other setters. I've run the database query by hand and can verify
> > that it is returning the expected results.
> >
> > If it helps, I'm running iBATIS 2.1.5 build 582 (which appears to be
> > the most recent non-beta build at this time).
> >
> > Here is my broken SQL Map:
> >
> > <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"
> > namespace="ClientExport">
> > <!--
> > We're only going to populate the parts of the Client object
> > hierarchy that we need,
> > reducing the amount of work to keep these resultMap's in sync
> > with
> > the real Client
> > class and database tables.
> > -->
> > <resultMap id="clientMap" class="com.activerain.model.Client"
> > groupBy="clientID">
> > <result property="clientID" column="client_id"/>
> > <result property="source" column="source"/>
> > <result property="folderName" column="folder_name"/>
> > <result property="insertDate" column="insert_date"/>
> > <result property="contactInfo.contactInfoID"
> > column="contact_info_id"/>
> > <result property="contactInfo.firstName" column="first_name"/>
> > <result property="contactInfo.lastName" column="last_name"/>
> > <result property="contactInfo.organization"
> > column="organization"/>
> > <result property="contactInfo.emailAddress"
> > column="email_address"/>
> > <result property="contactInfo.phoneNumbers"
> > resultMap="ClientExport.phoneNumberMap"/>
> > <result property="contactInfo.address.addressID"
> > column="address_id"/>
> > <result property="contactInfo.address.addressLine1"
> > column="address_line1"/>
> > <result property="contactInfo.address.addressLine2"
> > column="address_line2"/>
> > <result property="contactInfo.address.zipCode"
> > column="zip_code"/>
> > <result property="contactInfo.address.state" column="state"/>
> > <result property="contactInfo.address.city" column="city"/>
> > </resultMap>
> >
> > <resultMap id="phoneNumberMap"
> > class="com.activerain.model.PhoneNumber">
> > <result property="phoneNumberID" column="phone_number_id"/>
> > <result property="phoneNumberType"
> > column="phone_number_type_id"/>
> > <result property="areaCode" column="area_code"/>
> > <result property="phoneNumber" column="phone_number"/>
> > <result property="extension" column="extension"/>
> > </resultMap>
> >
> > <select id="getClientExportByUserID" resultMap="clientMap">
> > select
> > c.client_id,
> > c.source,
> > c.folder_name,
> > c.insert_date,
> > ci.contact_info_id,
> > ci.first_name,
> > ci.last_name,
> > ci.organization,
> > ci.email_address,
> > a.address_id,
> > a.address_line1,
> > a.address_line2,
> > a.city,
> > a.state,
> > a.zip_code,
> > p.phone_number_id,
> > p.phone_number_type_id,
> > p.area_code,
> > p.phone_number,
> > p.extension
> > from client c
> > inner join contact_info ci on c.contact_info_id =
> > ci.contact_info_id
> > inner join address a on ci.address_id = a.address_id
> > left join phone_number p on ci.contact_info_id =
> > p.contact_info_id
> > where
> > c.user_id = #value#
> > and c.is_deleted = false
> > and p.is_deleted = false
> > order by c.client_id, p.phone_number_id
> > </select>
> > </sqlMap>
> >
> > Thanks for any help you can provide.
> >
> > -james
> >
> > --
> > James A. Hillyerd <ja...@hillyerd.com>
> > Chief Technical Officer - ActiveRain Corp
> >
>
>
>
Re: Problem with avoiding N+1 queries on complex collection property
Posted by Tony Qian <da...@aol.com>.
James,
I had same problem (posted before). seems it is a bug ( i didn't claim
it was a bug before since I don't know if it was due to my fault in
configuration file). Luckily, little bit slow was not big deal for the
project I worked on.
Can some one in the iBATIS group take a look at this issue?
btw, I really liked iBATIS. Implemented in one of my project. Try to
push other groups to use it too.
thx,
Tony
James Hillyerd wrote on 1/31/2006, 4:59 PM:
> I tried to search the archives for any answers to this question, but
> for some reason it won't let you search for "N+1" and "complex" didn't
> find the answer I'm looking for.
>
> I have a set of queries that is successfully loading an object graph
> that looks like:
>
> Client:
> ContactInfo:
> Address
> PhoneNumber (Collection)
>
> Using the following SQL Map:
>
> <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"
> namespace="ClientExport">
> <resultMap id="clientMap" class="com.activerain.model.Client">
> <result property="clientID" column="client_id"/>
> <result property="source" column="source"/>
> <result property="folderName" column="folder_name"/>
> <result property="insertDate" column="insert_date"/>
> <result property="contactInfo.contactInfoID"
> column="contact_info_id"/>
> <result property="contactInfo.firstName" column="first_name"/>
> <result property="contactInfo.lastName" column="last_name"/>
> <result property="contactInfo.organization"
> column="organization"/>
> <result property="contactInfo.emailAddress"
> column="email_address"/>
> <result property="contactInfo.phoneNumbers"
> column="contact_info_id"
> select="getClientExportPhoneNumbersByContact"/>
> <result property="contactInfo.address.addressID"
> column="address_id"/>
> <result property="contactInfo.address.addressLine1"
> column="address_line1"/>
> <result property="contactInfo.address.addressLine2"
> column="address_line2"/>
> <result property="contactInfo.address.zipCode"
> column="zip_code"/>
> <result property="contactInfo.address.state" column="state"/>
> <result property="contactInfo.address.city" column="city"/>
> </resultMap>
>
> <resultMap id="phoneNumberMap"
> class="com.activerain.model.PhoneNumber">
> <result property="phoneNumberID" column="phone_number_id"/>
> <result property="phoneNumberType"
> column="phone_number_type_id"/>
> <result property="areaCode" column="area_code"/>
> <result property="phoneNumber" column="phone_number"/>
> <result property="extension" column="extension"/>
> </resultMap>
>
> <select id="getClientExportByUserID" resultMap="clientMap">
> select
> c.client_id,
> c.source,
> c.folder_name,
> c.insert_date,
> ci.contact_info_id,
> ci.first_name,
> ci.last_name,
> ci.organization,
> ci.email_address,
> a.address_id,
> a.address_line1,
> a.address_line2,
> a.city,
> a.state,
> a.zip_code
> from client c
> inner join contact_info ci on c.contact_info_id =
> ci.contact_info_id
> inner join address a on ci.address_id = a.address_id
> where
> c.user_id = #value#
> and c.is_deleted = false
> order by c.client_id
> </select>
>
> <select id="getClientExportPhoneNumbersByContact"
> resultMap="phoneNumberMap">
> select
> p.phone_number_id,
> p.phone_number_type_id,
> p.area_code,
> p.phone_number,
> p.extension
> from phone_number p
> where
> p.contact_info_id = #value#
> and p.is_deleted = false
> </select>
> </sqlMap>
>
> The above map works fine, but is very slow due to performing an extra
> query per client to load the phone numbers.
>
> When I change the SQL Map to use groupBy (below), my phone number list
> always gets set to null on the ContactInfo object (not an empty list,
> just null). I cannot find any errors in the log, and when I attach
> the debugger, I can see iBATIS creating instances of my PhoneNumber
> object, and _sometimes_ calling setPhoneNumberID(), but never any of
> the other setters. I've run the database query by hand and can verify
> that it is returning the expected results.
>
> If it helps, I'm running iBATIS 2.1.5 build 582 (which appears to be
> the most recent non-beta build at this time).
>
> Here is my broken SQL Map:
>
> <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"
> namespace="ClientExport">
> <!--
> We're only going to populate the parts of the Client object
> hierarchy that we need,
> reducing the amount of work to keep these resultMap's in sync
> with
> the real Client
> class and database tables.
> -->
> <resultMap id="clientMap" class="com.activerain.model.Client"
> groupBy="clientID">
> <result property="clientID" column="client_id"/>
> <result property="source" column="source"/>
> <result property="folderName" column="folder_name"/>
> <result property="insertDate" column="insert_date"/>
> <result property="contactInfo.contactInfoID"
> column="contact_info_id"/>
> <result property="contactInfo.firstName" column="first_name"/>
> <result property="contactInfo.lastName" column="last_name"/>
> <result property="contactInfo.organization"
> column="organization"/>
> <result property="contactInfo.emailAddress"
> column="email_address"/>
> <result property="contactInfo.phoneNumbers"
> resultMap="ClientExport.phoneNumberMap"/>
> <result property="contactInfo.address.addressID"
> column="address_id"/>
> <result property="contactInfo.address.addressLine1"
> column="address_line1"/>
> <result property="contactInfo.address.addressLine2"
> column="address_line2"/>
> <result property="contactInfo.address.zipCode"
> column="zip_code"/>
> <result property="contactInfo.address.state" column="state"/>
> <result property="contactInfo.address.city" column="city"/>
> </resultMap>
>
> <resultMap id="phoneNumberMap"
> class="com.activerain.model.PhoneNumber">
> <result property="phoneNumberID" column="phone_number_id"/>
> <result property="phoneNumberType"
> column="phone_number_type_id"/>
> <result property="areaCode" column="area_code"/>
> <result property="phoneNumber" column="phone_number"/>
> <result property="extension" column="extension"/>
> </resultMap>
>
> <select id="getClientExportByUserID" resultMap="clientMap">
> select
> c.client_id,
> c.source,
> c.folder_name,
> c.insert_date,
> ci.contact_info_id,
> ci.first_name,
> ci.last_name,
> ci.organization,
> ci.email_address,
> a.address_id,
> a.address_line1,
> a.address_line2,
> a.city,
> a.state,
> a.zip_code,
> p.phone_number_id,
> p.phone_number_type_id,
> p.area_code,
> p.phone_number,
> p.extension
> from client c
> inner join contact_info ci on c.contact_info_id =
> ci.contact_info_id
> inner join address a on ci.address_id = a.address_id
> left join phone_number p on ci.contact_info_id =
> p.contact_info_id
> where
> c.user_id = #value#
> and c.is_deleted = false
> and p.is_deleted = false
> order by c.client_id, p.phone_number_id
> </select>
> </sqlMap>
>
> Thanks for any help you can provide.
>
> -james
>
> --
> James A. Hillyerd <ja...@hillyerd.com>
> Chief Technical Officer - ActiveRain Corp
>