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 1开发者_如何学运维0 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.

It's simple to drop down to Hibernate directly if something isn't supported by GORM:

import org.hibernate.ScrollMode

class MyController { 

   def index = {...}

   def csv = {
      DomainClass.withSession { session ->
         def rs = session.createCriteria(DomainClass).scroll(ScrollMode.FORWARD_ONLY)
         while (rs.next()) {
            response.outputStream.print rs.getString(1)
         }
      }
   }
}

You could do the same for an HQL query using session.createQuery(...) instead.

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).

1. 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...
2. Call session.setReadOnly() or query.setReadOnly() to disable Hibernate's state snapshots

3. 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()
   

4. 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!

Another way to use Grails criteria and ScrollMode:

Criteria criteria = Domain.createCriteria().buildCriteria{
    eq('id', id)
}
ScrollableResults results = criteria.scroll(ScrollMode.FORWARD_ONLY)

int i = 0
while (results.next()){
    ...
    if (++i % 50 == 0){
        Domain.withSession { Session session ->
            session.flush()
            session.clear()
        }
    }
}

A few things worth noting:

• You shouldn't run queries in the controller since it might open a new db session and transaction for each call.
• If you want only a few columns you could use projections on your criteria.
• It might help to log the sqls that hibernate is firing. Up to this date, hibernate lazy loads every object's nullable one to one relationship even if you don't use it. You shouldn't have to worry if you're using projections, though.

Use batch insert and it faster than gorm cleanup method and stateless session method.Below example helps you how to implement batch insert in grails.

    Date startTime   = new Date()
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();

    (1..50000).each {counter ->
        Person person           = new Person()
        person.firstName        = "abc"
        person.middleName       = "abc"
        person.lastName         = "abc"
        person.address          = "abc"
        person.favouriteGame    = "abc"
        person.favouriteActor   = "abc"

        session.save(person)
        if(counter.mod(100)==0) {
            session.flush();
            session.clear();
        }

        if(counter.mod(10000)==0) {
            Date endTime    =new Date()
            println "Total record insert Counter =>"+counter+" Time =>"+TimeCategory.minus(endTime,startTime)
        }
    }

    tx.commit();
    session.close();