ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: (N + 1) solution does not yield same results as lazy loading solution
Date Wed, 09 Feb 2005 15:21:54 GMT
Ahh...yes of course. :-)  


On Wed, 9 Feb 2005 08:54:08 -0600, Mark Nabours
<MNabours@peabodyenergy.com> wrote:
> 
> 
> Clinton,
> 
> Since I'm the one who reported the issue, I'll report it under JIRA.  I
> certainly appreciate the input from Pascal.
> 
> Thanks,
> Mark
> 
> |---------+------------------------------------->
> |         |                        Clinton Begin|
> |         |            <clinton.begin@gmail.com>|
> |         |                                     |
> |         |                  02/09/2005 08:49 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
                                    |
>   |                                                                                 
                                    |
>   |                                                                                 
                                    |
>   |                                                                                 
                                    |
>   |                                                                                 
                                    |
>   |                                                                                 
                                    |
>   >----------------------------------------------------------------------------------------------------------------------|
> 
> Ahhh...okay.  Even without the exception, I can see the problem here.
> 
> First, please understand that iBATIS is NOT an O/R Mapper, so it
> differs greatly in its behavior.  Your assumption that N+1 and Lazy
> Load will work the same way is somewhat more applicable to an ORM than
> it is to iBATIS.  iBATIS doesn't know the difference between the types
> in the table, so it has no idea what you're definition of the data is.
>  iBATIS just knows rows and results, it does not know types.  That is,
> Category columns are no different from Item columns.
> 
> Now, here's the deal.  Your result data is 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      |
> +---------------+---------------+---------+-----------+
> 
> In the last row, the item columns are NULL.  But, category_code does
> have a value, and you've mapped it in "itemMap".  Because there's
> mapped data in that row, iBATIS will create a row for it.  This
> differs from the lazy loader in that it only cares about the column
> you use as the key (or parameter).  The N+1 solution cares about ALL
> mapped columns --if there's any data to report based on the resultMap,
> it will be mapped.
> 
> <resultMap id="itemMap" class="item">
>   <result property="id" column="item_id" />
>   <result property="name" column="item_name" />
>   <result property="categoryCode" column="category_code" />
> </resultMap>
> 
> Now, let's talk about a solution.
> 
> As a quick sanity check, could you please remove the "category_code"
> mapping from "itemMap"?  This will prove my conclusion and allow us to
> procede with a solution.
> 
> What are the potential solutions?  It will no doubt require an
> additional attribute.  The new attribute should indicate which columns
> are significant to identity in the resultMap.  It's starting to sound
> like a key indicator.
> 
> To date, we've shunned key indicators for various reasons.  Object
> identity is not something we support.  However, this is probably the
> first indication of a possible "requirement" for key support.
> Luckily, there are other reasons why it will help (could help improve
> caching).
> 
> Anyway...
> 
> Pascal, could you please copy your full request into a JIRA ticket,
> and I'll post this response there?  I really would rather use JIRA to
> track large requests like this.  The good news is, the fix probably
> won't be terribly hard to implement and could make it into the next
> release (once our SVN repos gets set up).
> 
> Clinton
> 
> On Tue, 08 Feb 2005 21:19:15 -0800, Pascal DeMilly
> <list.ibatis@newgenesys.com> wrote:
> > 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.
> >
> >
> 
> -----------------------------------------
> 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