ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Problem with avoiding N+1 queries on complex collection property
Date Thu, 02 Feb 2006 02:32:26 GMT
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 <daqiqian2@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 <james@hillyerd.com>
>  > Chief Technical Officer - ActiveRain Corp
>  >
>
>
>

Mime
View raw message