ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Hillyerd <ja...@hillyerd.com>
Subject Problem with avoiding N+1 queries on complex collection property
Date Tue, 31 Jan 2006 21:59:57 GMT
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