";
}
} else {
echo "openDatabase(2):" . mysql_error() . "
";
}
return $db_link;
}
/*
* Oeffnet die Datenbankverbindung
* fuer ein Prepared Statement
*
* @param void
* @return object $dbc
*/
function openDatabasePS() {
return new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
}
/*
* psql Version 2008-10-05
*
* Prepares and executes a (simple) prepared statement
*
* @param $sql string prepared statement
* @param $params array params to be bind
* @param $single_resultset bool if set to 1 psql will give only the first resultset and disregard any other.
* @return bool, int or array if transaction fails false, if there is a result set an array of rows, if not just the number of affected rows
*/
$dbc = openDatabasePS();
function psql($sql, $params = array (), $single_resultset = 0) {
global $dbc;
$stmt = $dbc->prepare($sql);
if (!is_array($params)) { //this allows a shorthand for single-valued querys
$params = array (
1 => $params
);
}
if (isset ($params[0])) { //array is probably numeric and starts at 0 which it shouldn't
$count = count($params);
$params_temp = array ();
for ($i = 1; $i <= $count; $i++) {
$params_temp[$i] = $params[$i -1];
}
$params = $params_temp;
}
foreach ($params AS $name => $val) {
$stmt->bindValue($name, htmlspecialchars(html_entity_decode($val)));
}
if ($stmt->execute()) {
if ($stmt->columnCount() == 0) {
$rowCount = $stmt->rowCount();
if ($rowCount == 1) {
$return = $dbc->lastInsertId();
} else {
$return = $stmt->rowCount();
}
} else {
if ($single_resultset) {
$return = $stmt->fetch(PDO :: FETCH_ASSOC);
} else {
$return = array ();
while ($row = $stmt->fetch(PDO :: FETCH_ASSOC)) {
$return[] = $row;
}
}
}
} else {
trigger_error('Could not execute statement, errorInfo: ' . join(", ", $stmt->errorInfo()));
$return = false;
}
return $return;
}
/*=============================================*/
/*starting test-suit...*/
$max = 5000;
$table = 'testuhuh';
echo '' .
'Comparison of SQL query methods. Each ' . $max . ' cycles. Less is better.' .
' | INSERT [s] | Load every single line [s] | Load all and loop through [ms] | Delete [s] |
|---|
';
psql('DROP TABLE IF EXISTS ' . $table);
psql('CREATE TABLE `' . DB_NAME . '`.`' . $table . '` (`id` INT NOT NULL AUTO_INCREMENT , `text` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ');
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
psql('INSERT INTO ' . $table . '(text) VALUES(?)', $i);
}
$timediff10 = (microtime(true) - $time);
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
psql('SELECT * FROM ' . $table . ' WHERE text=?', $i, 1);
}
$timediff11 = (microtime(true) - $time);
$time = microtime(true);
$res = psql('SELECT * FROM ' . $table);
// $j = 0; //just so we can confirm it really loops through the same amount as the others
foreach ($res AS $row) {
// $j++;
}
$timediff12 = (microtime(true) - $time) * 1000;
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
psql('DELETE FROM ' . $table . ' WHERE text=?', $i);
}
$timediff13 = (microtime(true) - $time);
echo '| PSQL | ' . $timediff10 . ' | ' . $timediff11 . ' | ' . $timediff12 . ' | ' . $timediff13 . ' |
|---|
';
openDatabase();
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
mysql_query('INSERT INTO ' . $table . '(text) VALUES(' . mysql_real_escape_string($i) . ')');
}
$timediff20 = (microtime(true) - $time);
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
$res = mysql_query('SELECT * FROM ' . $table . ' WHERE text = "' . mysql_real_escape_string($i) . '"');
mysql_fetch_assoc($res);
}
$timediff21 = (microtime(true) - $time);
$time = microtime(true);
$j = 0;
$res = mysql_query('SELECT * FROM ' . $table . '');
while ($row = mysql_fetch_assoc($res)) {
// $j++;
}
$timediff22 = (microtime(true) - $time) * 1000;
$time = microtime(true);
for ($i = 0; $i < $max; $i++) {
$res = mysql_query('DELETE FROM ' . $table . ' WHERE text = "' . mysql_real_escape_string($i) . '"');
}
$timediff23 = (microtime(true) - $time);
echo '| mysql_query and mysql_fetch_assoc | ' . $timediff20 . ' | ' . $timediff21 . ' | ' . $timediff22 . ' | ' . $timediff23 . ' |
|---|
';
$dbc = openDatabasePS();
$time = microtime(true);
$sql = 'INSERT INTO ' . $table . '(text) VALUES(?)';
$stmt = $dbc->prepare($sql);
$stmt->bindParam(1, $i);
for ($i = 0; $i < $max; $i++) {
$stmt->execute();
}
$timediff30 = (microtime(true) - $time);
$time = microtime(true);
$sql = 'SELECT * FROM ' . $table . ' WHERE text=?';
$stmt = $dbc->prepare($sql);
$stmt->bindParam(1, $i);
for ($i = 0; $i < $max; $i++) {
$stmt->execute();
}
$timediff31 = (microtime(true) - $time);
$time = microtime(true);
$sql = 'SELECT * FROM ' . $table;
$stmt = $dbc->prepare($sql);
$stmt->execute();
// $j = 0;
while ($row = $stmt->fetch(PDO :: FETCH_ASSOC)) {
// $j++;
}
$timediff32 = (microtime(true) - $time) * 1000;
$time = microtime(true);
$sql = 'DELETE FROM ' . $table . ' WHERE text=?';
$stmt = $dbc->prepare($sql);
$stmt->bindParam(1, $i);
for ($i = 0; $i < $max; $i++) {
$stmt->execute();
}
$timediff33 = (microtime(true) - $time);
echo '| Standard prepared statements | ' . $timediff30 . ' | ' . $timediff31 . ' | ' . $timediff32 . ' | ' . $timediff33 . ' |
|---|
';
echo '
';
echo ' . ',' . ($timediff20 * 1000) . ',' . ($timediff30 * 1000) . ')
';
echo ' . ',' . ($timediff21 * 1000) . ',' . ($timediff31 * 1000) . ')
';
echo ' . ',' . ($timediff22 * 1000) . ',' . ($timediff32 * 1000) . ')
';
echo ' . ',' . ($timediff23 * 1000) . ',' . ($timediff33 * 1000) . ')
';
?>