遍历Postgres/PHP/PDO中结果集的最佳实践? [英] Best practice to iterate over result set in Postgres/PHP/PDO?
问题描述
我正在将PHP 5.3.6和PDO一起使用来访问Postgres 9.0.4.我被要求减少报告的内存占用.当前的实现很简单:执行查询，执行fetchAll()，然后在结果数组中使用foreach()进行迭代.显然，这不会扩展到庞大的结果集:它可能会暂时消耗100MB或更多空间.
I'm using PHP 5.3.6 with PDO to access Postgres 9.0.4. I've been asked to reduce the memory footprint of a report. The current implementation is simple: execute the query, do a fetchAll() and then iterate with foreach() through the resulting array. This obviously doesn't scale with huge result sets: it can temporarily consume 100MB or more.
我有一个新的实现，该实现采用PDO语句句柄，然后使用foreach()直接对其进行迭代，即没有通过fetchAll()的中间数组. (据我所读，在幕后用foreach调用fetch()迭代了一个语句句柄.)这同样快，并且消耗的 way 更少的内存:大约28kB.不过，我不确定自己是否做对了，因为尽管我做了很多谷歌搜索，但很难找到有关以下基本问题的答案:
I have a new implementation which takes the PDO statement handle and then iterates directly on it using foreach(), i.e. no intermediate array via fetchAll(). (From what I've read, iterating a statement handle with foreach calls fetch() under the covers.) This is just as fast and consumes way less memory: about 28kB. Still, I'm not confident I'm doing it right because, although I've done a ton of Googling, it's tough to find answers to basic questions about this:
-
我看过一些文章，它们建议使用游标解决我原来的问题. Postgress PDO驱动程序是否已在内部使用游标?如果需要编写自己的SQL创建游标，我愿意，但是我希望编写尽可能简单的代码(但不要简单！).
I've seen articles that suggest solving my original problem using cursors. Does the Postgress PDO driver already use cursors internally? If writing my own SQL to create a cursor is required, I'm willing to but I'd prefer to write the simplest code possible (but no simpler!).
如果foreach每次迭代都调用fetch()，那不是太网络闲聊了吗?还是很聪明，一次获取许多行，例如500，节省带宽? (这可能意味着它在内部使用了游标.)
If foreach calls fetch() each iteration, isn't that too network chatty? Or is it smart and fetches many rows at once, e.g. 500, to save bandwidth? (This may imply that it uses cursors internally.)
我看过一篇将语句句柄包装在实现Iterator接口的类中的文章.鉴于已经有PDO语句句柄了，这不是多余的吗?还是我错过了什么?
I've seen an article that wraps the statement handle in a class that implements Iterator interface. Isn't this redundant given that a PDO statement handle already does this? Or am I missing something?
我准备SQL语句的调用如下:
My call to prepare the SQL statement looks like this:
$ sth = $ dbh-> prepare($ sql);
$sth = $dbh->prepare($sql);
如果执行此操作，我发现它不会造成内存或速度差异:
I found that it made no memory or speed difference if I did this:
$sth = $dbh->prepare($sql, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ) );
这是因为这还是Postgres PDO驱动程序的默认设置吗?如果它已经在内部使用游标了，那将是有道理的.
Is this because this is the default anyway for the Postgres PDO driver? This would make sense if it is already using cursors internally.
欢迎对此方法的一般评论以及其他解决方法.
General comments about the approach and other ways to solve this problem are welcome.
推荐答案
PDO for Postgres does use cursors internally.
这篇关于遍历Postgres/PHP/PDO中结果集的最佳实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
