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.