ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Qian" <daqiqi...@aol.com>
Subject Re: Problem with avoiding N+1 queries on complex collection property
Date Wed, 01 Feb 2006 19:53:25 GMT
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