list vs scroll question about efficiency.

NeMeDemandePas · **Joined:** Thu Apr 06, 2006 6:06 pm **Posts:** 10

Hibernate version: 3.0

Mapping documents: none for this case

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

Code:

SQLQueryImpl theQuery = (SQLQueryImpl)session.createSQLQuery(sqlString);
         theQuery.addScalar("startdate", Hibernate.TIMESTAMP)
                 .addScalar("enddate", Hibernate.TIMESTAMP)
                 .addScalar("STANDARD", Hibernate.LONG)
                 .addScalar("pri", Hibernate.LONG)
                 .addScalar("building", Hibernate.STRING)
                 .addScalar("rcc", Hibernate.STRING)
                 .addScalar("stockno", Hibernate.STRING);
         if (moreDetails != null)
         {
            theQuery.addScalar("manifest", Hibernate.STRING)
                    .addScalar("ccn", Hibernate.STRING)
                    .addScalar("docno", Hibernate.STRING)
                    .addScalar("pickdate", Hibernate.TIMESTAMP)
                    .addScalar("manifestdate", Hibernate.TIMESTAMP);
         }
         if (timeFrame == criticalTimeFrame)
         {
            theQuery.addScalar("palletid", Hibernate.LONG);
         }
         Iterator i = theQuery.list().iterator();
         Object[] next = null;
         while (i.hasNext())
         {
            next = (Object[])i.next();
         .........//code ommitted for clarification

Name and version of the database you are using:Oracle 9i

Here's my question. This code works fine. It returns 43500 rows of data.
Creating these Object[] 's takes a considerably long time. So the query takes about a minute. The query itself when i run it from toad only takes about 4 seconds before it displays the results. I changed the code to scroll() instead of list(). It returned really quickly (about 5 seconds) but then it takes a really long time to scroll through the results and create the objects from the results.

I know it can be faster, because our current implementation using a ResultSet (non hibernate version) can complete everything consistently at around 15 seconds.

One other option I looked in to, but did not implement was the "select new" but I don't think it will work any faster, and I don't think it's possible with a createSQLQuery, and I don't want to convert the SQL query to HQL.

Does anyone have any suggestions as to how I might be able to speed up the returning of the results?
Thanks,
Nate[/code]

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Given that this is a tight loop, and no special ORM features are needed, I'd say that the best option would be to remove Hibernate from the equation entirely. It is reasonable to use raw JDBC for this loop. This also allows you to use positional access to the result set (rst.getLong(3), instead of named access (rst.getLong("STANDARD"): positional access is icky, but it's undoubtedly faster. And seeing as you're reading from the result set in very close proximity to the place where you create the query, the high degree of coupling isn't a major problem.

NeMeDemandePas · **Joined:** Thu Apr 06, 2006 6:06 pm **Posts:** 10

Hey, I sure appreciate how you always are swift to reply to my questions. I'll try the raw jdbc. Hopefully it'll make it faster.
Thanks again for taking the time to look at my question,
Nate