Over a million developers have joined DZone.

Performance analysis fetching data with PDO and PHP

· Web Dev Zone

Start coding today to experience the powerful engine that drives data application’s development, brought to you in partnership with Qlik.

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.

Create data driven applications in Qlik’s free and easy to use coding environment, brought to you in partnership with Qlik.

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 best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

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

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

{{ parent.tldr }}

{{ parent.urlSource.name }}