ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Nabours <MNabo...@peabodyenergy.com>
Subject Re: (N + 1) solution does not yield same results as lazy loading solution
Date Wed, 09 Feb 2005 14:52:54 GMT




Clinton,

You are right about the exception -- the simple example below does not
throw one.

The exception only will occur when the objects within the list contain
primitive properties I believe.  For instance, if the id property on the
item object was an int and my memory serves me correctly, an exception
would be thrown about trying to set id to an invalid value.

I will place this under JIRA.

Keep up the great work!

Thanks,
Mark




|---------+------------------------------------->
|         |                        Clinton Begin|
|         |            <clinton.begin@gmail.com>|
|         |                                     |
|         |                  02/09/2005 08:27 AM|
|         |                                     |
|         |                                     |
|         |                    Please respond to|
|         |            ibatis-user-java@incubato|
|         |                         r.apache.org|
|         |                                     |
|---------+------------------------------------->
  >----------------------------------------------------------------------------------------------------------------------|
  |                                                                                      
                               |
  |                                                                                      
                               |
  |                                                                                      
                             To|
  |        ibatis-user-java@incubator.apache.org                                         
                               |
  |                                                                                      
                             cc|
  |                                                                                      
                               |
  |                                                                                      
                        Subject|
  |        Re: (N + 1) solution does not yield same results as lazy loading solution     
                               |
  |                                                                                      
                               |
  |                                                                                      
                               |
  |                                                                                      
                               |
  |                                                                                      
                               |
  |                                                                                      
                               |
  >----------------------------------------------------------------------------------------------------------------------|




For all of the great information you've provided, you did not provide
the exception.

Also, due to the amount of information you have, you might want to
open a JIRA ticket so we can manage it better.

Cheers,
Clinton


On Tue, 8 Feb 2005 22:58:53 -0600, Mark Nabours
<MNabours@peabodyenergy.com> wrote:
>
>
> Hello,
>
> We have a situation where it would be advantageous for performance
reasons
> to avoid lazy loading and utilize the recently implemented (N + 1)
> solution.  Unfortunately, when utilizing a left join the results are not
> the same as the lazy loading solution.  (Depending on the objects being
> stored in the list, it's possible that the (N+1) solution will throw an
> exception where the lazy loading solution will not.)
>
> To illustrate the problem I've put together a very simple example.
>
> Please consider the following simple tables of data, categories and
items:
>
> +---------------+---------------+
> | category_code | category_name |
> +---------------+---------------+
> | cat1          | Category 1    |
> | cat2          | Category 2    |
> | cat3          | Category 3    |
> +---------------+---------------+
>
> +---------+-----------+---------------+
> | item_id | item_name | category_code |
> +---------+-----------+---------------+
> | item1   | Item 1    | cat1          |
> | item2   | Item 2    | cat1          |
> | item3   | Item 3    | cat1          |
> | item4   | Item 4    | cat1          |
> | item5   | Item 5    | cat2          |
> | item6   | Item 6    | cat2          |
> +---------+-----------+---------------+
>
> The challenge is to load a List of items for each category.
>
> Here are the two extremely simple data objects used to illustrate the
> issue:
>
> //==============================================================
> // Category.java
> //==============================================================
> package com.alliancesys.ibatisdemo.domain;
>
> import java.util.Iterator;
> import java.util.List;
>
> public class Category {
>
>       private String code;
>       private String name;
>       private List items;
>
>       public Category() {
>             super();
>       }
>
>       public String getCode() {
>             return code;
>       }
>
>       public List getItems() {
>             return items;
>       }
>
>       public String getName() {
>             return name;
>       }
>
>       public void setCode(String string) {
>             code = string;
>       }
>
>       public void setItems(List list) {
>             items = list;
>       }
>
>       public void setName(String string) {
>             name = string;
>       }
>
>       /**
>        * @see java.lang.Object#toString()
>        */
>       public String toString() {
>             StringBuffer buffer = new StringBuffer();
>             buffer.append(this.getClass().getName());
>             buffer.append("-");
>             buffer.append("code:");
>             buffer.append(code);
>             buffer.append(",name:");
>             buffer.append(name);
>             buffer.append(",items:[");
>             List items = getItems();
>             if (items != null) {
>                   for (Iterator iter = items.iterator(); iter.hasNext();)
{
>                         buffer.append(iter.next());
>                         buffer.append(";");
>                   }
>             }
>             buffer.append("]");
>             return buffer.toString();
>       }
>
> }
>
> //==============================================================
> // Item.java
> //==============================================================
> package com.alliancesys.ibatisdemo.domain;
>
> public class Item {
>
>       private String id;
>       private String name;
>       private String categoryCode;
>
>       public Item() {
>             super();
>       }
>
>       public String getCategoryCode() {
>             return categoryCode;
>       }
>
>       public String getId() {
>             return id;
>       }
>
>       public String getName() {
>             return name;
>       }
>
>       public void setCategoryCode(String categoryCode) {
>             this.categoryCode = categoryCode;
>       }
>
>       public void setId(String id) {
>             this.id = id;
>       }
>
>       public void setName(String name) {
>             this.name = name;
>       }
>
>       /**
>        * @see java.lang.Object#toString()
>        */
>       public String toString() {
>             StringBuffer buffer = new StringBuffer();
>             buffer.append(this.getClass().getName());
>             buffer.append("-");
>             buffer.append("id:");
>             buffer.append(id);
>             buffer.append(",name:");
>             buffer.append(name);
>             buffer.append(",categoryCode:");
>             buffer.append(categoryCode);
>             return buffer.toString();
>       }
>
> }
>
> Here is the sqlMap file that contains both the lazy loading solution (
> getAllCategoriesWithLazyLoad) and the N + 1 solution (
> getAllCategoriesWithNPlusOne):
>
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
> "http://www.ibatis.com/dtd/sql-map-2.dtd">
>
> <sqlMap namespace="Item">
>
>       <typeAlias
>             alias="item"
>             type="com.alliancesys.ibatisdemo.domain.Item" />
>
>       <typeAlias
>             alias="category"
>             type="com.alliancesys.ibatisdemo.domain.Category" />
>
>       <resultMap
>             id="itemMap"
>             class="item">
>             <result
>                   property="id"
>                   column="item_id" />
>             <result
>                   property="name"
>                   column="item_name" />
>             <result
>                   property="categoryCode"
>                   column="category_code" />
>
>       </resultMap>
>
>       <resultMap
>             id="categoryWithLazyLoad"
>             class="category">
>             <result
>                   property="code"
>                   column="category_code"/>
>             <result
>                   property="name"
>                   column="category_name"/>
>             <result
>                   property="items"
>                   column="category_code"
>                   select="getItemsByCategoryCode" />
>       </resultMap>
>
>       <resultMap
>             id="categoryWithNPlusOne"
>             class="category"
>             groupBy="code">
>             <result
>                   property="code"
>                   column="category_code"/>
>             <result
>                   property="name"
>                   column="category_name" />
>             <result
>                   property="items"
>                   resultMap="Item.itemMap" />
>       </resultMap>
>
>       <select
>             id="getItemsByCategoryCode"
>             parameterClass="java.lang.String"
>             resultMap="itemMap">
>             select
>                   item_id,
>                   item_name,
>                   category_code
>             from
>                   items
>             where
>                   category_code = #value#;
>       </select>
>
>       <select
>             id="getAllCategoriesWithLazyLoad"
>             resultMap="categoryWithLazyLoad">
>             select
>                   category_code,
>                   category_name
>             from
>                   categories;
>       </select>
>
>       <select
>             id="getAllCategoriesWithNPlusOne"
>             resultMap="categoryWithNPlusOne">
>             select
>                   c.category_code,
>                   c.category_name,
>                   i.item_id,
>                   i.item_name
>             from
>                   categories c
>                   left join items i on c.category_code = i.category_code
>             order by
>                   c.category_code;
>       </select>
>
> </sqlMap>
>
> The following test case exercises the two approaches:
>
> package com.alliancesys.ibatis.testing;
>
> import java.io.Reader;
> import java.sql.SQLException;
> import java.util.Iterator;
> import java.util.List;
>
> import com.alliancesys.ibatisdemo.domain.Category;
> import com.ibatis.common.resources.Resources;
> import com.ibatis.sqlmap.client.SqlMapClient;
> import com.ibatis.sqlmap.client.SqlMapClientBuilder;
>
> import junit.framework.TestCase;
>
> public class IBATISAggregateObjectTest extends TestCase {
>
>       /**
>        * Constructor for IBATISAggregateObjectTest.
>        * @param arg0
>        */
>       public IBATISAggregateObjectTest(String arg0) {
>             super(arg0);
>       }
>
>       public static void main(String[] args) {
>             junit.textui.TestRunner.run(IBATISAggregateObjectTest.class);
>       }
>
>       public void testGetCategories() throws Exception {
>             Reader reader =
>                   Resources.getResourceAsReader(
>                         getClass().getClassLoader(),
>                         "sql-map-config.xml");
>             SqlMapClient sqlMapClient =
>                   SqlMapClientBuilder.buildSqlMapClient(reader);
>
>             try {
>
>                   sqlMapClient.startTransaction();
>
>                   //Fetch a list of categories with lazy load of items.
>                   List categoriesWithLazyLoad =
>                         sqlMapClient.queryForList(
> "getAllCategoriesWithLazyLoad", null);
>
>                   for (Iterator iter = categoriesWithLazyLoad.iterator();
>                         iter.hasNext();
>                         ) {
>                         //force lazy load
>                         ((Category)iter.next()).getItems().iterator();
>
>                   }
>                   //print each category
>                   for (Iterator iter = categoriesWithLazyLoad.iterator();
>                         iter.hasNext();
>                         ) {
>                         System.out.println(iter.next());
>                   }
>
>                   // Fetch a list of categories with N+1 solution
>                   List categoriesWithNPlusOne =
sqlMapClient.queryForList(
> "getAllCategoriesWithNPlusOne", null);
>                   // print categories expect same output as before
>                   for (Iterator iter = categoriesWithNPlusOne.iterator();
>                         iter.hasNext();
>                         ) {
>                         System.out.println(iter.next());
>                   }
>             } catch (SQLException e) {
>                   throw e;
>             } finally {
>                   sqlMapClient.endTransaction();
>             }
>
>       }
>
> }
>
> We expect both approaches to yield the same output.  But for the cat3
> category under the lazy loading approach the following is outputted:
> com.alliancesys.ibatisdemo.domain.Category-code:cat3,name:Category
> 3,items:[]
> Indicating that the items property was loaded with an empty List.  This
is
> exactly  what I expect would happen since there are no items for that
> category.  All is well except that performance stinks for our real world
> needs because of the N + 1 issue.
>
> Looking at cat3 for the N + 1 solution approach, the following is
> outputted:
> com.alliancesys.ibatisdemo.domain.Category-code:cat3,name:Category
>
3,items:[com.alliancesys.ibatisdemo.domain.Item-id:null,name:null,categoryCode:cat3;]

> Indicating that a nonexistent item was loaded into the list for the items
> property.
>
> I believe the issue is that the (N + 1) solution approach does not handle
> left joins.  (It's not acceptable for me to switch the join to an inner
> join or I would basically lose cat3 from the collection.)  The results
from
> the left join are as follows:
>
> +---------------+---------------+---------+-----------+
> | category_code | category_name | item_id | item_name |
> +---------------+---------------+---------+-----------+
> | cat1          | Category 1    | item3   | Item 3    |
> | cat1          | Category 1    | item4   | Item 4    |
> | cat1          | Category 1    | item1   | Item 1    |
> | cat1          | Category 1    | item2   | Item 2    |
> | cat2          | Category 2    | item5   | Item 5    |
> | cat2          | Category 2    | item6   | Item 6    |
> | cat3          | Category 3    | NULL    | NULL      |
> +---------------+---------------+---------+-----------+
>
> The cat3 row seems to be problematic unless there are additional
> configuration settings in the SQLMap file that address this.
>
> So in summary, the lazy loading approach yields the correct results, but
we
> need to utilize the (N + 1) solution for performance but it yields bogus
> results.  Is there already a solution for this?  Or should this be logged
> in JIRA?
>
> We would appreciate a quick turnaround on this because the (N + 1)
support
> is such a great feature that we want to be able to utilize to simplify
our
> code.
>
> Thanks,
> Mark
>
> (See attached file: Item.xml)(See attached file: Item.java)(See attached
> file: Category.java)(See attached file: IBATISAggregateObjectTest.java)
>
> -----------------------------------------
> E-mail Disclaimer:  The information contained in this e-mail, and in any
> accompanying  documents, may constitute confidential and/or legally
> privileged  information.  The information is intended only for use by the
> designated recipient.  If you are not the intended recipient (or
> responsible for the delivery of the message to the intended recipient),
> you are hereby notified that any dissemination, distribution, copying,
or
> other use of, or taking of any action in reliance on this e-mail  is
> strictly prohibited. If you have received this email communication  in
> error, please notify the sender immediately and delete the message  from
> your system.
>
>
>



-----------------------------------------
E-mail Disclaimer:  The information contained in this e-mail, and in any
accompanying  documents, may constitute confidential and/or legally
privileged  information.  The information is intended only for use by the
designated recipient.  If you are not the intended recipient (or
responsible for the delivery of the message to the intended recipient),
you are hereby notified that any dissemination, distribution, copying,  or
other use of, or taking of any action in reliance on this e-mail  is
strictly prohibited. If you have received this email communication  in
error, please notify the sender immediately and delete the message  from
your system.


Mime
View raw message