Sql – groovy sql eachRow and rows method

groovysql

I am new to grails and groovy.
Can anyone please explain to me the difference between these two groovy sql methods

sql.eachRow
sql.rows

Also, which is more efficient?

I am working on an application that retrieves data from the database(the resultset is very huge) and writes it to CSV file or returns a JSON format.

I was wondering which of the two methods mentioned above to use to have the process done faster and efficient.

Best Answer

Can anyone please explain to me the difference between these two groovy sql methods sql.eachRow sql.rows

It's difficult to tell exactly which 2 methods you're referring 2 because there are a large number of overloaded versions of each method. However, in all cases, eachRow returns nothing

void eachRow(String sql, Closure closure)

whereas rows returns a list of rows

List rows(String sql) 

So if you use eachRow, the closure passed in as the second parameter should handle each row, e.g.

sql.eachRow("select * from PERSON where lastname = 'murphy'") { row ->
    println "$row.firstname"
}

whereas if you use rows the rows are returned, and therefore should be handled by the caller, e.g.

rows("select * from PERSON where lastname = 'murphy'").each {row ->
    println "$row.firstname"        
}

Also, which is more efficient?

This question is almost unanswerable. Even if I had implemented these methods myself there's no way of knowing which one will perform better for you because I don't know

  • what hardware you're using
  • what JVM you're targeting
  • what version of Groovy you're using
  • what parameters you'll be passing
  • whether this method is a bottleneck for your application's performance

or any of the other factors that influence a method's performance that cannot be determined from the source code alone. The only way you can get a useful answer to the question of which method is more efficient for you is by measuring the performance of each.

Despite everything I've said above, I would be amazed if the performance difference between these two was in any way significant, so if I were you, I would choose whichever one you find more convenient. If you find later on that this method is a performance bottleneck, try using the other one instead (but I'll bet you a dollar to a dime it makes no difference).