Lack of speed benefit with prepared statements?
I have heard that prepared statements with a MySQL database may offer a speed boost if the query is run multiple times, and I thought I had the perfect example for a project. But I ran some tests and found the exact opposite. Am I using these statements incorrectly (not an ideal situation for prepared statements), or are they just not as fast as I thought they were?
The Situation is the tournament results grid. Several schools have participated in several events and each school has points for each event. To get individual scores for all events, an SQL query with a LEFT JOIN is required, for example:
SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`=:school_id;
I wrote two PHP test scripts to work with sample data (200 events) using native PDO objects ( prepare()
/ bindValue()
/ execute()
versus query()
):
EDIT Modified tests with below suggestions (vanilla query requires fetching, fetching different ids, and out-of-loop preparation binding). Only gives a modest speed advantage for prepared statements:
Prepared expression:
$start = microtime(true);
$sql = 'SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`=:school_id';
echo $sql."<br />\n";
$stmt = $db->prepare($sql);
$sid = 0;
$stmt->bindParam(':school_id', $sid);
for ($i=0; $i<$max; $i++) {
$sid = rand(1,499);
$stmt->execute();
$rs = $stmt->fetchAll();
}
$delta = bcsub(microtime(true), $start, 4);
echo "<strong>Overall time:</strong> $delta<br />\n";
echo "<strong>Average time:</strong> ".($delta/$max)."<br />\n";
Vanilla request:
set_time_limit(15); // Add time for each run
$start = microtime(true);
$sql = 'SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`={$sid}';
echo $sql."<br />\n";
for ($i=0; $i<$max; $i++) {
$sid = rand(1,499);
$stmt = $db->query("SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`={$sid}");
$rs = $stmt->fetchAll();
}
$delta = bcsub(microtime(true), $start, 4);
echo "<strong>Overall time:</strong> $delta<br />\n";
echo "<strong>Average time:</strong> ".($delta/$max)."<br />\n";
I get the same school event scores over and over (school number 10) and setting $max
to 10,000, I get results that show vanilla queries are 30% faster (25.72 seconds versus 36.79). Am I doing it wrong, or is it for sure that prepared statements don't speed up even in a repetitive situation?
EDIT updated benchmarks now get 33.95 seconds versus 34.10 vanilla. Huzzah prepared statements faster. But only for a split second in 10,000 iterations. Perhaps because my query is not that complex (prepared statements cache the parse tree for their benefit)? Or is there still an optimization for this?
source to share
It looks like you cannot compare apples to apples.
PDO::query()
Executes an SQL statement, returning the result set as an object PDOStatement
.
To get the actual results, you need to iterate over the returned object or, as in the prepared statement, call fetchAll()
to load the entire result set into an array
The correct vanilla request loop should be like this:
for ($i=0; $i<$max; $i++) {
$stmt = $db->query($sql);
$rs = $stmt->fetchAll();
}
or, alternatively, removing the call fetchAll()
from the prepared statement loop.
You can also reduce the method calls required for the prepared statement by using bindParam()
insteadbindValue()
$school_id = null;
$stmt->bindParam(':school_id', $school_id);
for ($i=0; $i<$max; $i++) {
$school_id = 10;
$stmt->execute();
$rs = $stmt->fetchAll();
}
source to share
The vanilla query is executing the same query every time, so you are just checking "fetching results from the query cache" and not the execution time of the query. This is not a valid test.
You will need to build the INSIDE query in a loop, so you call the NEW query every time:
for ($i=0; $i<$max; $i++) {
$sql = <<<EOL
SELECT e.id, e.name, c.competing, c.raw, c.final
FROM events e
LEFT JOIN scores c ON e.id=c.event_id
WHERE c.school_id= $i;
EOL;
$rs = $db->query($sql);
}
source to share
None of these people explicitly noticed:
PDO emulates prepared statements with MySQL by default, even if the driver supports them.
This is the exact opposite of what the PHP PDO man page is , however, but look at the source code and you will see that they always, de facto, use emulated prepared statements (I thought it was a coding error, but when I filed the report it was classified as WONT_FIX and I had some kind of Zend dev just stating, “It's our policy to always imitate, just cuz.” It doesn't make sense to me, but good.
To use truly prepared statements, you need to jump over the hoop. If you don't like this, blame the Zend developers as the fix looks like it only takes 5 minutes (just move the if).
$pdo = new PDO($dsn, $user, $pass, array(ATTR::PDO_EMULATE_PREPARES => false));
I would really appreciate it if you make this change, run your test and update us on timing with this code.
source to share