Hibernate uses 5 queries to retrieve ordered results

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.0.5

Mapping documents:

Code:

<class name="User" table="MM_USERS">
      <id name="id" column="id" type="int" unsaved-value="null"
         length="5">
         <generator class="native" />
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true" unique="false" />
      <set name="memberships" table="MM_USERGROUPLINKS" cascade="none" order-by="name asc" lazy="true" inverse="false">
         <key column="userID" />
         <many-to-many class="project.vo.Group" column="groupID" />
      </set>
   </class>

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

Code:

Transaction tx = session.beginTransaction();
            
            Query query = session.createQuery("select u from User u order by u.name asc");
            Iterator results = query.iterate();
            while (results.hasNext()) {
                User u = (User) results.next();
                System.out.println("User: " + u.getId() + " " + u.getName());
            }
            
            tx.commit();

Full stack trace of any exception that occurs:

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

The generated SQL (show_sql=true):
Hibernate: select user0_.id as col_0_0_ from MM_USERS user0_ order by user0_.nam
e asc
Hibernate: select user0_.id as id0_, user0_.name as name2_0_ from MM_USERS user0
_ where user0_.id=?
Hibernate: select user0_.id as id0_, user0_.name as name2_0_ from MM_USERS user0
_ where user0_.id=?
User: 2 Ang Wee Leong
Hibernate: select user0_.id as id0_, user0_.name as name2_0_ from MM_USERS user0
_ where user0_.id=?
User: 4 Lai Boon Keng
Hibernate: select user0_.id as id0_, user0_.name as name2_0_ from MM_USERS user0
_ where user0_.id=?
User: 1 Lee See Meng
User: 3 Lek Hock Khoon

As per above, I have 4 records in the MM_USERS table. Why does Hibernate use 5 queries when using native SQL, only one is required??

Anyway to optimize this? I can't imagine what happens if I had 1000 records in the table...

Thanks!

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

Based on your query, you are iterating on multiple users. Each user instance generates a select statement for its membership - that is the corrcect behavior.

If you want h3 generate only 1 query for multiple users, I believe you can write your own hql.

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

sorry - i thought you were talking about collections.

Try use query.list()

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Thanks joeserel! That worked!

What's the difference between using iterate() and using list()? Do you have any recommendations on under when I should use which one?

Thanks a lot for the tip!

joeserel wrote:

sorry - i thought you were talking about collections.

Try use query.list()

emmanuel · **Posted:** Wed Jun 22, 2005 7:25 am

everbright wrote:

Thanks joeserel! That worked!

What's the difference between using iterate() and using list()? Do you have any recommendations on under when I should use which one?

Thanks a lot for the tip!

joeserel wrote:

sorry - i thought you were talking about collections.

Try use query.list()

Please, please guys. We've spent nights and days to write a comprehensive reference documentation (translated in many languages), FAQs, and decent JavaDocs. Read them, if not for us or the kindly users answering you, do it for you. You'll be much much faster at work.

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Noted. Apologies for the "noise" I made, and the frustration I've caused :-(

Guess I was to eager to get started using Hibernate. Will read the documentation more carefully in future, before I make postings.

Thanks for the advice! Appreciate it, and what you people are doing for the community :-)

emmanuel wrote:

everbright wrote:

Thanks joeserel! That worked!

What's the difference between using iterate() and using list()? Do you have any recommendations on under when I should use which one?

Thanks a lot for the tip!

joeserel wrote:

sorry - i thought you were talking about collections.

Try use query.list()

Please, please guys. We've spent nights and days to write a comprehensive reference documentation (translated in many languages), FAQs, and decent JavaDocs. Read them, if not for us or the kindly users answering you, do it for you. You'll be much much faster at work.

mahikty · **Posted:** Wed Jun 22, 2005 9:38 am

Hi,

I have found the following relevant lines in the documentation
which clarifies your doubt.

11.4.1.1. Iterating results
Occasionally, you might be able to achieve better performance by executing the query using the iterate() method. This will only usually be the case if you expect that the actual entity instances returned by the query will already be in the session or second-level cache. If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query, usually 1 for the initial select which only returns identifiers, and n additional selects to initialize the actual instances.

The url is
http://www.hibernate.org/hib_docs/v3/re ... ng-iterate

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Yup found that already. Thanks!

mahikty wrote:

Hi,

I have found the following relevant lines in the documentation
which clarifies your doubt.

11.4.1.1. Iterating results
Occasionally, you might be able to achieve better performance by executing the query using the iterate() method. This will only usually be the case if you expect that the actual entity instances returned by the query will already be in the session or second-level cache. If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query, usually 1 for the initial select which only returns identifiers, and n additional selects to initialize the actual instances.

The url is
http://www.hibernate.org/hib_docs/v3/re ... ng-iterate