Benchmark for the different PDO fetch styles.

pdo-fetch-benchmark.php

<?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);

1 single fetch is not enough !

//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);

I added Fetch_class & more loops upon 10000 records