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

@boussou boussou commented Jun 17, 2014

1 single fetch is not enough !

@boussou

This comment has been minimized.

Copy link

@boussou boussou 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

@boussou boussou 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