Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Benchmark for the different PDO fetch styles.
<?php
set_time_limit(0);
// List of fetch styles to test
$fetchStyles = array(
'Assoc' => PDO::FETCH_ASSOC,
'Both' => PDO::FETCH_BOTH,
'Lazy' => PDO::FETCH_LAZY,
'Num' => PDO::FETCH_NUM,
'Obj' => PDO::FETCH_OBJ
);
// PDO connection
$pdo = new PDO
(
'mysql:dbname=mysql;host=localhost',
'benchmark', // Username
'benchmark', // Password
array(
PDO::ATTR_CASE => PDO::CASE_LOWER,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_PERSISTENT => true
)
);
// The results array
$result = array();
// Loop each style
foreach($fetchStyles as $key => $style)
{
// Total time for each style
$total = 0.0;
for ($i = 0; $i < 100; $i++)
{
// Record the start time
$start = microtime(true);
// Execute
$statement = $pdo->prepare('SELECT * FROM help_topic');
$statement->execute();
$mixed = $statement->fetch($style);
// Record the end time
$stop = microtime(true);
// Convert Start and Stop to Miliseconds
$start = $start * 1000.0;
$stop = $stop * 1000.0;
// Calculate the time taken
$diff = ($stop - $start);
// Add the difference to the total
$total = $total + $diff;
}
// Store the result
$result[$key] = $total;
}
// Sort the results
asort($result, SORT_NUMERIC);
// Output the result
// Time taken is in Milliseconds
var_dump($result);
@boussou

This comment has been minimized.

Copy link

commented Jun 17, 2014

1 single fetch is not enough !

@boussou

This comment has been minimized.

Copy link

commented Jun 17, 2014

//here is my version (don't want to fork)

PDO::FETCH_ASSOC, 'Both' => PDO::FETCH_BOTH, 'Lazy' => PDO::FETCH_LAZY, 'Num' => PDO::FETCH_NUM, 'Obj' => PDO::FETCH_OBJ, 'Class' => PDO::FETCH_CLASS ); ``` $path="/path/to sqlite file"; ``` // PDO connection $pdo = new PDO ( // 'mysql:dbname=mysql;host=localhost', 'sqlite:'.$path.'/models.db3', 'benchmark', // Username 'benchmark', // Password array( PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL, PDO::ATTR_PERSISTENT => true ) ); // The results array $result = array(); // Loop each style foreach($fetchStyles as $key => $style) { // Total time for each style $total = 0.0; ``` for ($i = 0; $i < 100; $i++) { // Execute $statement = $pdo->prepare('SELECT * FROM models'); $statement->execute(); // Record the start time $start = microtime(true); if($key=='Class') for ($i = 0; $i < 10000; $i++) { $mixed = $statement->fetchObject("models"); } else for ($i = 0; $i < 10000; $i++) { $mixed = $statement->fetch($style); } // Record the end time $stop = microtime(true); // Convert Start and Stop to Miliseconds $start = $start * 1000.0; $stop = $stop * 1000.0; // Calculate the time taken $diff = ($stop - $start); // Add the difference to the total $total = $total + $diff; } // Store the result $result[$key] = $total; ``` } // Sort the results asort($result, SORT_NUMERIC); // Output the result // Time taken is in Milliseconds var_dump($result); print_r($result);
@boussou

This comment has been minimized.

Copy link

commented Jun 17, 2014

I added Fetch_class & more loops upon 10000 records

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.