HibernateTemplate.iterate() performance issues

I am using Spring/Hibernate platform with SQL Server. To retrieve records from a large table, I use iterate() method on HibernateTemplate. When I issue a query (with some conditions), it takes about 150 seconds (within JUnit test case) to iterate through about 5000 records. I am able to retrieve all 5000 records within a seconds. Each records has only a few small integer / string fields.

Looking at the logs, I can see Hibernate initializing entity object proxy, opening prepared statement, issuing query, opening result set, closing result set, closing prepared statement for each iteration. I am certain issuing the query every time is what is contributing to this slowness.

Is there anything I can do to fix this issue? I am using default cache settings.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sathish David Kumar NArchitectCommented:
which version of java your using ?
Sathish David Kumar NArchitectCommented:
use generic List for retriving

List=getHibernateTemplate().find("from userTable");

its automatically set the value to your value to bean properties
swsbuyerAuthor Commented:
I am using Java 1.6.

If I use getHibernateTemplate().find() and the table has 6 million records, that will not be pretty. That is why I am using the iterator.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Sathish David Kumar NArchitectCommented:
use where contidtion in that find....

String userid="Sathish"
List<UR bean(POJO)>  = getHibernateTemplate().find("from userTable where userId = "      + userid);
Sathish David Kumar NArchitectCommented:
if your mapping is gd means ...

it will rertive with in a min .....using find   6 million records,

check your pooling and dataSource in dispatcher Servlet
swsbuyerAuthor Commented:
This is not a solution. Even with the conditions I can apply, the data set can still be huge.

I can use getHibernateTemplate().findByCriteria(crit, firstRecord, maxRecords) and retrieve the records in blocks, and that will work ok I assume.

However, I would like to understand why getHibernateTemplate().iterate() is slow. Why can't it retrieve the records in blocks of several records and return me one by one.  If I am the designer of hibernate library, I would do that.

I am almost certain I am missing something.
swsbuyerAuthor Commented:
Hmm! So, if I use something like:

List<Bean> beans = getHibernateTemplate().find(...)

Wouldn't find() load all those 6 mill records in the returned list "bean". That will be need too much RAM assuming that each record is > 100 bytes.

I am not looking to find a workaround. I want to understand why iterator() is slow...
Mick BarryJava DeveloperCommented:
I think iterate() just pulls the ids initially and then makes a query for every row to get the next object.
Thats going to be slow.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sathish David Kumar NArchitectCommented:
can u post your iterate code ??
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.