The answer to my last question looks good

i asked for using hibernate for only persistence and for selection some other mechanism

one reply was to use hibernate for both purposes

i m working on a struts based application

i have 1000 records in my ORDERINFO table and each order has almost 10
line items, when i want to retrieve this whole record, i m confronting with famous n+1 select problem.

if i use the same technique means 1000 select statements from hibernate
it takes almost 13 seconds to execute these 1000 select statements

if i use "batch-size" property of hibernate then time reduces to 6-7 secondes, but i think its still too much,

have anyone solution for this problem



Hibernate version: 3.0

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package = "com.ts.hibernateBean">

<class name="ItemBean" table="ITEM">

<id name="itemId" unsaved-value="null" >
<column name="ITEM_ID" not-null="true"/>
<generator class="identity"/>
</id>
<property name="itemName">
<column name="ITEM_NAME"/>
</property>
<property name="itemDescription">
<column name="ITEM_DESCRIPTION"/>
</property>
</class>

<class name="CustomerBean" table="CUSTOMER" lazy="true">
<id name="customerId" unsaved-value="null" >
<column name="CUSTOMER_ID" not-null="true"/>
<generator class="identity"/>
</id>
<property name="customerName">
<column name="CUSTOMER_NAME" not-null="true"/>
</property>
</class>

<class name="OrderBean" table="ORDERINFO" lazy="false">
<id name="orderId" unsaved-value="null" >
<column name="ORDER_ID" not-null="true"/>
</id>
<property name="dateField">
<column name="ORDER_DATE"/>
</property>
<property name="intAmount">
<column name="TOTAL_AMOUNT" not-null="true"/>
</property>
<property name="intCustomerId">
<column name="CUSTOMER_ID" not-null="true"/>
</property>

<many-to-one name="customer" column="CUSTOMER_ID" unique="true" not-null="true" insert="false" update="false"/>

<set name="items" table="ORDER_ITEM" batch-size="10" lazy="false"> <!--batch-size="10"-->
<key column="ORDER_ID"/>
<many-to-many column="ITEM_ID" unique="true" class="ItemBean"/>
</set>
</class>

</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

Query query1 = session.createQuery("from OrderBean order, CustomerBean customer where order.intCustomerId = customer.customerId");
System.out.println("Loading Orders and Customers: Time :" + new Date());
List orderList = query1.list();
Iterator iter1 = orderList.iterator();
Vector orderVector = new Vector();
Vector customerVector = new Vector();
while (iter1.hasNext()) {
Object[] tuple = (Object[]) iter1.next();
OrderBean order = (OrderBean) tuple[0];
orderVector.add(order);
CustomerBean customer = (CustomerBean) tuple[1];
customerVector.add(customer);
}

System.out.println("Orders and Custumers loaded going to jsp: Time :" + new Date());
request.setAttribute("orderVector", orderVector);
request.setAttribute("customerVector", customerVector);





JSP Page
<table border = "1" cellpadding ="2" cellspacing = "4" width="100%">
<tr>
<td align="center"> Date </td>
<td width="35%"> Customer </td>
<td width="60%"> Items Perchased </td>
</tr>

<%
int customerId = 0;
Iterator iter1 = resultVector.iterator();
System.out.println("Loading Items: Time :" + new Date());

while (iter1.hasNext()){
OrderBean order = (OrderBean) iter1.next();
CustomerBean customer = (CustomerBean)customerVector.get(customerId++);
%>
<tr>
<td> <%= order.getDateField()%> </td> <td> <%= customer.getCustomerName() %> </td>
<% if (true){ %>
<td>
<%
Set items = order.getItems();
Iterator iter2 = items.iterator();
while (iter2.hasNext()){
ItemBean item = (ItemBean)iter2.next();
%>
<table border = "1" cellpadding ="2" cellspacing = "4" width="50%">
<tr>
<td> <%= item.getItemName()%> </td>
</tr>
</table>
<%
}
%>
<%
}
%>
</td>
</tr>
<%
}
System.out.println("Items Loaded: Time :" + new Date());
%>




Name and version of the database you are using: SQL Server 2000

You can use still use Hibernate to transfer the data into your Java Objects and either use SQL Queries or HQL queries that will reduce the SQL that's sent to the database for your particular purpose.

I don't have a solution to your problem, but I do have an advice. You're using struts, which means you are supposed to use the MVC design pattern. You know, the biggest idea with MVC is separating M from V from C, right?

That doesn't happen when you use scriplets. When you put scriplets in your jsp, you're destroying everything you wanted to do with struts. Struts gives you mechanisms for doing all that stuff with no scriplets.

So that's it. Sorry for the off-topic. Try to take all scriplets out of your life. :)

I had a similar problem.
I switched FlushMode to Never for example.
Because is default value AUTO means:



Saves 10x performance.

Turn on lazy initialisation for all collections that you do not need, and perhaps for large fields in the root entity as well.

If you're using hibernate 3 they will be lazy by default.

use this hql and pagination

and i saw somtheing weird in
<class name="OrderBean" table="ORDERINFO" lazy="false">
....
<many-to-one name="customer" column="CUSTOMER_ID" unique="true" not-null="true" insert="false" update="false" />

you should add the class attribute class="CustomerBean"


<many-to-one name="customer" column="CUSTOMER_ID" unique="true" not-null="true" insert="false" update="false" class="CustomerBean" />

and then you can change your Hql to

List orderList =session.createQuery("select order from OrderBean order inner join fetch CustomerBean customer ")
.setFirstResult(0)
.setMaxResults(10)
.list();

use the setFirstResult and setMaxResults to make a pagination that should make it very fast and use the join fecth so the list will return only order object is you want the customer you use orde.getCustomer() thats a better way that using array of objects