Performance analysis fetching data with PDO and PHP.
Fetching data from databases is a common operation in our work as developers. There are many drivers (normally I use PDO), but the usage of all of them are similar and switch from one to another is not difficult (they almost share the same interface). In this post I will focus on fetching data. Basically we’ve got two functions: fetch and fetchAll. I’ve created two examples. One with fetch and another one with fetchAll:
// Example with fetch
error_reporting(-1);
$time = microtime(TRUE);
$mem = memory_get_usage();
$dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'username', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare('SELECT * FROM tableName limit 10000');
$stmt->execute();
$i=0;
while ($row = $stmt->fetch()) {
$i++;
}
echo '
<h1>fetch()</h1>
';
echo '
<strong>{$i} </strong>
';
print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));
// Example with fetchAll
error_reporting(-1);
$time = microtime(TRUE);
$mem = memory_get_usage();
$dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'username', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare('SELECT * FROM tableName limit 10000');
$stmt->execute();
$i=0;
$data = $stmt->fetchAll();
foreach ($data as $row) {
$i++;
}
echo '
<h1>fetchAll()</h1>
';
echo '
<strong>{$i}</strong>
';
print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));
if we execute the test we obtain:
fetchAll: [memory] => 31.305999755859
fetch: [memory] => 0.002532958984375
OK. It’s obvious. If we approach to the data extraction with fetchAll method we will use more memory. That’s because we’re mapping the whole recorded to a variable ($data) at once. With the fetch loop we are mapping only on row per iteration. By the way if we change the fetch loop to:
$data = array();
while ($row = $stmt->fetch()) {
$i++;
$data[] = $row;
}
We will use almost the same amount of memory than the fetchAll method
[memory] => 31.267543792725
Conclusion:
Is it better fetch than fetchAll? The answer is simple: No. We only need to take care what are we doing and use the best solution that fix to our need. If we’re handling small recordset, they’re similar, but if we work with big ones we need to realize that the memory usage we are using changes drastically if we use one method or another.
Posted on March 28, 2011, in PDO, php, PostgreSQL and tagged pdo, performance, php. Bookmark the permalink. 9 Comments.
Hi Gonzalo,
First of all, nice post. Although for advanced developers it’s obvious, for others less-advanced it might not be. It is however entirely logical.
Still, I would love to see an article about performance comparisons between the use of objects and arrays (associative or not) for data manipulation.
IMHO, OOP is excellent because allows us to develop (web) applications more maintainable, portable and easily re-usable. However, performance it’s not exactly one of it’s benefits.
Arrays are, in a more natural and native way, a more faster and less resource-needed way to store data in memory and to manipulate it. Not so easy as the object-oriented way.
Hugz.
txs. In fact I wrote this article because I’ve seen a lot of times fetchAll instead of fetch within projects without any reason. If we need to use it, it’s perfect but we always must bear in mind the significant memory usage differences. Probably the outcome is too obvious. BTW, I write your suggestion
Are there any speed differences between the two methods?
I’ve omitted speed because it’s almost the same. The main important difference is the memory usage, and I’ve focussed on it
Actually according to my tests speed difference is significant when using bigger data sets. My current problem is that fetchAll drains the memory and fetch is taking ages compared looping fetched all array.
Dev
http://www.epanorama.net
The difference between both methods is only visible with big datasets, indeed. fetchAll is more comfortable (at least for me) but we need to take into account the big memory usage with big datasets.
Do you flush output to the browser? Another possible problem is the Select statement. (if you use sub-queries within each the performance will be penalized). Another thing may be the latency between web-server and database server.
Hi Gonzalo,
I found that fetch() performs faster that fetchAll() with large set of data.With 1K of data fetch() took .1sec to create form while fetchAll took .8 sec.Can you please let me know the reason.
I wrote this post to explain this issue. Read the second paragraph. It’s problably because the memory that PHP need to create the variable.
Pingback: Database abstraction layers in PHP. PDO versus DBAL « Gonzalo Ayuso | Web Architect