Ahh...yes of course. :-)
On Wed, 9 Feb 2005 08:54:08 -0600, Mark Nabours <[EMAIL PROTECTED]> 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| > | | <[EMAIL PROTECTED]>| > | | | > | | 02/09/2005 08:49 AM| > | | | > | | | > | | Please respond to| > | | [EMAIL PROTECTED]| > | | r.apache.org| > | | | > |---------+-------------------------------------> > > >----------------------------------------------------------------------------------------------------------------------| > | > | > | > | > | > To| > | [email protected] > | > | > 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 > <[EMAIL PROTECTED]> 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. > >
