Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Performance analysis fetching data with PDO and PHP

DZone's Guide to

Performance analysis fetching data with PDO and PHP

· Web Dev Zone
Free Resource

Discover how to focus on operators for Reactive Programming and how they are essential to react to data in your application.  Brought to you in partnership with Wakanda

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
02 error_reporting(-1);
03 $time = microtime(TRUE);
04 $mem = memory_get_usage();
05
06 $dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'username', 'password');
07 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
08
09 $stmt = $dbh->prepare('SELECT * FROM tableName limit 10000');
10 $stmt->execute();
11
12 $i=0;
13 while ($row = $stmt->fetch()) {
14 $i++;
15 }
16 echo '
17 <h1>fetch()</h1>
18 ';
19 echo '
20 <strong>{$i} </strong>
21
22 ';
23 print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));
view source
print?
01 // Example with fetchAll
02 error_reporting(-1);
03 $time = microtime(TRUE);
04 $mem = memory_get_usage();
05
06 $dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'username', 'password');
07 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
08
09 $stmt = $dbh->prepare('SELECT * FROM tableName limit 10000');
10 $stmt->execute();
11
12 $i=0;
13 $data = $stmt->fetchAll();
14 foreach ($data as $row) {
15 $i++;
16 }
17
18 echo '
19 <h1>fetchAll()</h1>
20 ';
21 echo '
22 <strong>{$i}</strong>
23
24 ';
25 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();
2 while ($row = $stmt->fetch()) {
3 $i++;
4 $data[] = $row;
5 }

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.

Learn how divergent branches can appear in your repository and how to better understand why they are called “branches".  Brought to you in partnership with Wakanda

Topics:

Published at DZone with permission of Gonzalo Ayuso, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}