You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Clinton Begin (JIRA)" <ib...@incubator.apache.org> on 2005/02/12 22:09:13 UTC
[jira] Closed: (IBATIS-69) (N + 1) solution does not yield same results as lazy loading solution
[ http://issues.apache.org/jira/browse/IBATIS-69?page=history ]
Clinton Begin closed IBATIS-69:
-------------------------------
Assign To: Clinton Begin
Resolution: Invalid
I'll close this issue, as the new solution makes a lot of sense. We can always reopen it as a feature request if we see a need for it.
> (N + 1) solution does not yield same results as lazy loading solution
> ---------------------------------------------------------------------
>
> Key: IBATIS-69
> URL: http://issues.apache.org/jira/browse/IBATIS-69
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: XP Pro, WSAD 5.1.2
> Reporter: Mark Nabours
> Assignee: Clinton Begin
> Attachments: Category.java, Item.java, Item.xml, category_item.sql
>
> 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.
> 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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira