You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@ibatis.apache.org by "Mark Nabours (JIRA)" <ib...@incubator.apache.org> on 2005/02/09 16:04:13 UTC

[jira] Created: (IBATIS-69) (N + 1) solution does not yield same results as lazy loading solution

(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
 Attachments: Category.java, Item.java, Item.xml

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