Need help improving the performance of large datasets in grails

This solution works but performance is lower than expected. A query returning 200K rows takes several minutes and pegs the CPU on my dev box. Running the same* query in query analyzer returns all results in < 1 minute.

Class MyController { 

 def index = {...}
 ...
 def csv = {
   ...
   def rs = DomainClass.createCritera().scroll {}

   while(rs.next()){
    response.getOutputStream().print(rs.getString(1)\n)
   }
   ...
 }

DB = SQL Server 2005 server on a dedicated box separate from my dev machine.

I've also noticed via SQL Server Profiler that gorm/hibernate is using sp_cursorprepexec and sp_cursorfetch to read the result 128 rows at a time. I'd like to try not using a cursor if it's an option.

Not sure if it's the problem but can only help. In hibernate it's possible to set the scroll as forward only but I'm having trouble finding a similar setting for grails.

original hibernate issue.

Solution: Bypass hibernate. From 10 minutes to 15 seconds.

Class MyController { 
 def DataSource

 def index = {...}
 ...
 def csv = {
   ...
   def out = response.getOutoutStream()
   Sql sql = new Sql(dataSource)

   sql.eachRow("select c1, c2 from t1",{
     out.println( it.c1 + "," + it.c2 )
   })
   ...
 }

*same = Cut and paste from the SQL Server Profiler, but excluding the wrapping sp_cursorprepexec sproc.

Solution

Hibernate isn't really made for batch loading, but there are some things you can try (most of which require you to drop the ScrollableResult usage and just do regular queries with object results).

Just bypass Hibernate/GORM and go to SQL directly for the (hopefully) handful of places where you need it. Ya, I know, but if worse comes to worse...
Call session.setReadOnly() or query.setReadOnly() to disable Hibernate's state snapshots
Give Hibernate's Stateless Session a try. If all you're doing is reading, this may work fine. The Stateless Session has a much lower overhead than the regular Hibernate session, but you'll give up all your caching and object state tracking. You'll have to do something like this to use it:
```
def Session statelessSession = sessionFactory.openStatelessSession()
statelessSession.beginTransaction()

// ...

statelessSession.getTransaction().commit()
statelessSession.close()
```
Flush the session in batches of 25 or 50. Essentially, as you're iterating over the items that you've brought back, do a session.flush(). If you don't, the session will keep growing until you run out of memory and your garbage collector starts going crazy. This might be why your processor is getting pegged.

Good luck!