PHP + mySQL + Pdo查询速度性能

Question

I was wondering if I could speed up a batch of queries by preparing the the Pdo outside the function like this:

$before = microtime(true);

// note this line
$query=$dbh->prepare("SELECT something FROM test WHERE record=:param");


for ($i=0 ; $i<1000 ; $i++) {
    callMe("worldcup2014");
}

function callMe($param) {
  global $dbh, $query;
  $query->bindParam(':param', $param);
  $query->execute();
  $result = $query -> fetch();
}

$after = microtime(true);
echo ($after-$before) . " sec
";

And it is slightly faster than preparing inside the function like this:

$before = microtime(true);

for ($i=0 ; $i<1000 ; $i++) {
    callMe("worldcup2014");
}

function callMe($param) {
  global $dbh;

  // line is now here inside function
  $query=$dbh->prepare("SELECT something FROM test WHERE record=:param");

  $query->bindParam(':param', $param);
  $query->execute();
  $result = $query -> fetch();
}

$after = microtime(true);
echo ($after-$before) . " sec
";

The first example takes about 0.61 secs, sometimes 0.60 secs

The second example takes about 0.65secs, never less than 0.63 secs.

I know it is a minor difference, but are there any other things that should be considered to speed up Pdo queries ? Note: I can't store/cache results. Need to grab fresh data.

Answer 1

Yes, one of the benefits of using prepared queries is performance improvement. If you're going to execute the same query many times, you should prepare it just once, then execute it repeatedly. Preparing a query requires a call to the database, and this is expensive.

You can improve it a little more by only calling bindParam once. bindParam binds to a variable reference, so all you have to do is reassign the variable and call execute(), you don't have to bind the variable again. However, this won't work when you're executing the query in a function as you are, because each time you enter the function you get a new version of the parameter variable, and the reference doesn't persist between function calls.

So it should be like this pseudo-code:

prepare statement
bind parameter to $var
foreach ($array as $var) {
    execute
}

You could turn this into one query with something like this:

$in = implode(', ', array_fill(0, count($array), '?'));
$sql = "SELECT something FROM test WHERE record IN ($in)";
$stmt = $dbh->prepare($sql);
$stmt->execute($array);
$results = $stmt->fetchAll();

This should be far more efficient than repeatedly querying.