title: Need help improving the performance of large datasets in grails
body: <p>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 &lt; 1 minute.</p>
  
  <pre><code>Class MyController { 
  
   def index = {...}
   ...
   def csv = {
     ...
     def rs = DomainClass.createCritera().scroll {}
  
     while(rs.next()){
      response.getOutputStream().print(rs.getString(1)\n)
     }
     ...
   }
  </code></pre>
  
  <p>DB = SQL Server 2005 server on a dedicated box separate from my dev machine.</p>
  
  <p>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.</p>
  
  <p>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.  </p>
  
  <p>original hibernate <a href="http://opensource.atlassian.com/projects/hibernate/browse/HB-1181" rel="nofollow noreferrer">issue</a>. </p>
  
  <h2>Solution: Bypass hibernate. From 10 minutes to 15 seconds.</h2>
  
  <pre><code>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 )
     })
     ...
   }
  </code></pre>
  
  <p>*same = Cut and paste from the SQL Server Profiler, but excluding the wrapping sp_cursorprepexec sproc.</p>