groovy sql eachRow 和 rows 方法

groovy sql eachRow and rows method

提问by Shenoy Tinny

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

我是 grails 和 groovy 的新手。谁能向我解释这两种 groovy sql 方法之间的区别

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.

我正在开发一个从数据库中检索数据（结果集非常大）并将其写入 CSV 文件或返回 JSON 格式的应用程序。

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

我想知道使用上面提到的两种方法中的哪一种可以更快、更有效地完成该过程。

回答by Dónal

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

谁能向我解释这两个 groovy sql 方法 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, eachRowreturns nothing

很难准确说出您指的是哪 2 个方法 2，因为每种方法都有大量的重载版本。但是，在所有情况下，eachRow什么都不返回

void eachRow(String sql, Closure closure)

whereas rowsreturns a list of rows

而rows返回行列表

List rows(String sql) 

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

所以如果你使用eachRow，作为第二个参数传入的闭包应该处理每一行，例如

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

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

而如果您使用rows返回的行，则应由调用者处理，例如

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 youbecause 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

• 你使用的是什么硬件
• 你的目标是什么JVM
• 您使用的是什么版本的 Groovy
• 你将传递什么参数
• 此方法是否是应用程序性能的瓶颈

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

尽管我上面说了这么多，但如果这两者之间的性能差异在任何方面都很显着，我会感到惊讶，所以如果我是你，我会选择你觉得更方便的那个。如果您稍后发现此方法是性能瓶颈，请尝试改用另一种（但我敢打赌，一分钱一分货都没有区别）。

回答by Victor Sergienko

They differ in signature only - both support result sets paging, so both will be efficient. Use whichever fits your code.

它们仅在签名上有所不同 -两者都支持结果集分页，因此两者都是有效的。使用适合您的代码的任何一个。

回答by mrembisz

If we set aside minor syntax differences, there is one difference that seems important. Let's consider

如果我们不考虑细微的语法差异，那么有一个差异似乎很重要。让我们考虑一下

sql.rows("select * from my_table").each { row -> doIt(row) }

vs

对比

sql.eachRow("select * from my_table") { row -> doIt(row) }

The first one opens connection, retrieves results, closes connection and returns them. Now you can iterate over the results while connection is released. The drawback is you now have entire result list in memory which in some cases might be a lot.

第一个打开连接，检索结果，关闭连接并返回它们。现在您可以在释放连接时迭代结果。缺点是您现在在内存中拥有整个结果列表，在某些情况下可能会很多。

EachRow on the other hand opens a connection and while keeping it open executes your closure for each row. If your closure operates on the database and requires another connection your code will consume two connections from the pool at the same time. The connection used by eachRow is released after it iterates though all the resulting rows. Also if you don't perform any database operations but closure takes a while to execute, you will be blocking one database connection until eachRow completes.

另一方面，每行打开一个连接，并在保持打开的同时为每一行执行关闭。如果您的闭包对数据库进行操作并需要另一个连接，您的代码将同时消耗池中的两个连接。eachRow 使用的连接在遍历所有结果行后被释放。此外，如果您不执行任何数据库操作但关闭需要一段时间来执行，您将阻塞一个数据库连接，直到 eachRow 完成。

I am not 100% sure but possibly eachRow allows you not to keep all resulting rows in memory but access them through a cursor - this may depend on the database driver.

我不是 100% 确定，但可能 eachRow 允许您不将所有结果行保留在内存中，而是通过游标访问它们 - 这可能取决于数据库驱动程序。

If you don't perform any database operations inside your closure, closure executes fast and results list is big enough to impact memory then I'd go for eachRow. If you do perform DB operations inside closure or each closure call takes significant time while results list is manageable, then go for rows.

如果你不在你的闭包中执行任何数据库操作，闭包执行得很快，结果列表大到足以影响内存，那么我会去eachRow。如果您确实在闭包内执行数据库操作，或者每个闭包调用都需要花费大量时间而结果列表是可管理的，那么请选择rows。