CODEWITHSUNDEEP
grailsgrails-ormlarge-data
jason saldo
jason saldo

Reputation: 9960

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.

Upvotes: 2

Views: 7813

Answers (5)

Prakash Bhavnath
Prakash Bhavnath

Reputation: 279

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();

Upvotes: 1

Douglas Mendes
Douglas Mendes

Reputation: 322

A few things worth noting:

Upvotes: 1

Burt Beckwith
Burt Beckwith

Reputation: 75681

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.

Upvotes: 4

user1067920
user1067920

Reputation: 1572

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()
        }
    }
}

Upvotes: 1

Javid Jamae
Javid Jamae

Reputation: 9029

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!

Upvotes: 3

Related Questions