Skip to content

Instantly share code, notes, and snippets.

@chrismeller
Created April 13, 2011 01:37
Show Gist options
  • Save chrismeller/916803 to your computer and use it in GitHub Desktop.
Save chrismeller/916803 to your computer and use it in GitHub Desktop.
<?php
// connect
$connect_start = microtime(true);
try {
$pdo = new PDO('sqlite:test.db');
}
catch ( PDOException $e ) {
die('Unable to connect: ' . $e->getMessage());
}
$connect_stop = microtime(true);
// create the test table
$sql = file_get_contents( 'test.sql' );
$create_start = microtime(true);
try {
$pdo->exec( $sql );
}
catch ( PDOException $e ) {
die('Unable to create table: ' . $e->getMessage());
}
$create_stop = microtime(true);
// insert 10,000 records
$insert_start = microtime(true);
try {
$insert = $pdo->prepare( 'insert into test ( value ) values ( :test )' );
}
catch ( PDOException $e ) {
die('Unable to prepare insert statement: ' . $e->getMessage());
}
for ( $i = 0; $i < 10000; $i++ ) {
try {
$insert->execute( array( ':test' => $i ) );
}
catch ( PDOException $e ) {
die('Unable to insert value: ' . $e->getMessage());
}
}
$insert_stop = microtime(true);
// now select back each of the 10000 records sequentially
$select_start = microtime(true);
try {
$select = $pdo->prepare( 'select value from test' );
}
catch ( PDOException $e ) {
die('Unable to prepare select statement: ' . $e->getMessage());
}
// execute it
try {
$select->execute();
}
catch ( PDOException $e ) {
die('Unable to execute select statement: ' . $e->getMessage());
}
while ( $row = $select->fetch() ) {
// nothing
}
$select_stop = microtime(true);
// delete the db
unlink( 'test.db' );
?>
Testing PDO
Testing SQLite
connect: PDO is faster by 0.01814603805542 seconds
create: PDO is slower by 0.0010099411010742 seconds
insert: PDO is slower by 17.156681060791 seconds
select: PDO is slower by 0.0061521530151367 seconds
<?php
error_reporting(-1);
echo 'Testing PDO' . "\n";
include( 'pdo.php' );
$pdo = array();
$pdo['connect'] = $connect_stop - $connect_start;
$pdo['create'] = $create_stop - $create_start;
$pdo['insert'] = $insert_stop - $insert_start;
$pdo['select'] = $select_stop - $select_start;
echo 'Testing SQLite' . "\n";
include( 'sqlite.php' );
$sqlite = array();
$sqlite['connect'] = $connect_stop - $connect_start;
$sqlite['create'] = $create_stop - $create_start;
$sqlite['insert'] = $insert_stop - $insert_start;
$sqlite['select'] = $select_stop - $select_start;
foreach ( array_keys( $pdo ) as $key ) {
$diff = $pdo[ $key ] - $sqlite[ $key ];
echo $key . ': ';
if ( $diff < 0 ) {
echo 'PDO is faster by ' . abs( $diff ) . ' seconds';
}
else if ( $diff > 0 ) {
echo 'PDO is slower by ' . $diff . ' seconds';
}
else {
echo 'PDO is equal';
}
echo "\n";
}
?>
<?php
// connect
$connect_start = microtime(true);
$db = new SQLIteDatabase( 'test.db', null, $error );
if ( $db === false ) {
die('Unable to connect: ' . $error);
}
$connect_stop = microtime(true);
// create the test table
$sql = file_get_contents( 'test.sql' );
$create_start = microtime(true);
$r = $db->queryExec( $sql );
if ( $r === false ) {
die('Unable to create table: ' . sqlite_error_string( $db->lastError() ) );
}
$create_stop = microtime(true);
// insert 10,000 records
$insert_start = microtime(true);
for ( $i = 0; $i < 10000; $i++ ) {
$query = 'insert into test ( value ) values ( ' . sqlite_escape_string( $i ) . ' )';
$r = $db->query( $query, SQLITE_BOTH, $error );
if ( $r === false ) {
die('Unable to insert value: ' . $error );
}
}
$insert_stop = microtime(true);
// now select back each of the 10000 records sequentially
$select_start = microtime(true);
$select = $db->unbufferedQuery( 'select value from test', SQLITE_BOTH, $error );
if ( $select === false ) {
die('Unable to execute select statement: ' . $error);
}
while ( $row = $select->fetch() ) {
// nothing
}
$select_stop = microtime(true);
// delete the db
unlink( 'test.db' );
?>
CREATE TABLE "test" (
"id" integer NOT NULL,
"value" text NOT NULL,
PRIMARY KEY("id")
)
@chrismeller
Copy link
Author

For reference, removing the try/catch around the insert for PDO reduced the difference to about 12 seconds.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment