循环中的 PDO::fetchAll 与 PDO::fetch [英] PDO::fetchAll vs. PDO::fetch in a loop

查看:28
本文介绍了循环中的 PDO::fetchAll 与 PDO::fetch的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

只是一个简单的问题.

在循环中使用 PDO::fetchAll() 和 PDO::fetch() 是否有性能差异(对于大型结果集)?

Is there any performance difference between using PDO::fetchAll() and PDO::fetch() in a loop (for large result sets)?

我正在获取用户定义类的对象,如果这有什么不同的话.

I'm fetching into objects of a user-defined class, if that makes any difference.

我最初未受过教育的假设是 fetchAll 可能更快,因为 PDO 可以在一个语句中执行多个操作,而 mysql_query 只能执行一个.但是,我对 PDO 的内部工作知之甚少,文档对此没有任何说明,以及 fetchAll() 是否只是转储到数组中的 PHP 端循环.

My initial uneducated assumption was that fetchAll might be faster because PDO can perform multiple operations in one statement while mysql_query can only execute one. However I have little knowledge of PDO's inner workings and the documentation doesn't say anything about this, and whether or not fetchAll() is simply a PHP-side loop dumped into an array.

有什么帮助吗?

推荐答案

带有 20 万条随机记录的小型基准测试.正如预期的那样,fetchAll 方法速度更快,但需要更多内存.

Little benchmark with 200k random records. As expected, the fetchAll method is faster but require more memory.

Result :
fetchAll : 0.35965991020203s, 100249408b
fetch : 0.39197015762329s, 440b

使用的基准代码:

<?php
// First benchmark : speed
$dbh = new PDO('mysql:dbname=testage;dbhost=localhost', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM test_table WHERE 1';
$stmt = $dbh->query($sql);
$data = array();
$start_all = microtime(true);
$data = $stmt->fetchAll();
$end_all = microtime(true);

$stmt = $dbh->query($sql);
$data = array();
$start_one = microtime(true);
while($data = $stmt->fetch()){}
$end_one = microtime(true);

// Second benchmark : memory usage
$stmt = $dbh->query($sql);
$data = array();
$memory_start_all = memory_get_usage();
$data = $stmt->fetchAll();
$memory_end_all = memory_get_usage();

$stmt = $dbh->query($sql);
$data = array();
$memory_end_one = 0;
$memory_start_one = memory_get_usage();
while($data = $stmt->fetch()){
  $memory_end_one = max($memory_end_one, memory_get_usage());
}

echo 'Result : <br/>
fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/>
fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>';

这篇关于循环中的 PDO::fetchAll 与 PDO::fetch的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆