ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pascal DeMilly <list.iba...@newgenesys.com>
Subject Re: (N + 1) solution does not yield same results as lazy loading solution
Date Wed, 09 Feb 2005 05:19:15 GMT
Not sure if I followed exactly what you are trying to do but if you are
trying to screen out categories with no items why not doing it in your
select statement as follow:

      <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
            where
                  i.item_id is not NULL
            order by
                  c.category_code;
      </select>

I added the WHERE clause

HTH

Pascal


On Tue, 2005-02-08 at 20:58, Mark Nabours 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.


Mime
View raw message