Monday, August 6, 2012

MySQLi prepared statements

Lessons learned:
  • Prepared statements are 13 percent faster than normal statements with escaping
  • Prepared statements are 8 percent faster than normal statements without escaping
  • To get improvements, you need at least 10000 inserts for 1 statement
  • Using insert...set is 0.5-1 percent faster than insert...values

Here is the code:
$db = new mysqli('127.0.0.1', 'root', '', 'test');
$db->query('create table if not exists prep (i1 int, i2 int, s1 varchar(255)) engine=myisam');
$db->query('truncate table prep');

$start = microtime(true);
$stmt = $db->prepare('insert into prep (i1,i2,s1) values (?,?,?)');
$i=0; $j=0; $s=null;
$stmt->bind_param('iis', $i, $j, $s);
for ($i=0; $i<100000; $i++) {
  $j = $i*2;
  $s = 'hello world'.$i;
  $stmt->execute();
}
echo 'prep values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
$stmt = $db->prepare('insert into prep set i1=?, i2=?, s1=?');
$i=0; $j=0; $s=null;
$stmt->bind_param('iis', $i, $j, $s);
for ($i=0; $i<100000; $i++) {
  $j = $i*2;
  $s = 'hello world'.$i;
  $stmt->execute();
}
echo 'prep set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $db->query("insert into prep (i1,i2,s1) values (".$i.",".($i*2).",'hello world".$i."')");
}
echo 'no escape values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $db->query("insert into prep set i1=".$i.", i2=".($i*2).", s1='hello world".$i."'");
}
echo 'no escape set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) if (!is_numeric($item)) $item = $db->real_escape_string($item);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape string values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) if (!is_numeric($item)) $item = $db->real_escape_string($item);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape string set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) $item = $db->real_escape_string($item);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = [$i, $i*2, 'hello world'.$i];
  foreach ($arr as &$item) $item = $db->real_escape_string($item);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = array_map([$db, 'real_escape_string'], [$i, $i*2, 'hello world'.$i]);
  $db->query("insert into prep (i1,i2,s1) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
echo 'escape map values '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
$db->query('truncate table prep');

$start = microtime(true);
for ($i=0; $i<100000; $i++) {
  $arr = array_map([$db, 'real_escape_string'], [$i, $i*2, 'hello world'.$i]);
  $db->query("insert into prep set i1='".$arr[0]."', i2='".$arr[1]."', s1='".$arr[2]."'");
}
echo 'escape map set '.number_format(microtime(true)-$start, 2)."\n";

assert($db->query('select count(*) from prep')->fetch_row()[0]==100000);
prep values 13.08
prep set 13.02
no escape values 14.31
no escape set 14.18
escape string values 15.06
escape string set 14.99
escape values 15.10
escape set 15.04
escape map values 15.51
escape map set 15.45

3 comments:

  1. Hi, sorry for my bad level in php but in Prepare mode, the bind is not inside the loop ??? it should decrease your measures.
    Thank's for the test

    ReplyDelete
    Replies
    1. mysqli_stmt_bind_param() requires parameters to be passed by reference, so there is only one call, see: http://www.php.net/manual/en/mysqli-stmt.bind-param.php

      Delete
    2. Thank's Thomas.
      I read before this article another test where the developper put the bind inside the loop. He measured performances worst with prepare statement... of course, I was surprised.
      Well, MySql parses quickly queries (in compare with Oracle)
      regards

      Delete

Labels

performance (23) benchmark (6) MySQL (5) architecture (5) coding style (5) memory usage (5) HHVM (4) C++ (3) Java (3) Javascript (3) MVC (3) SQL (3) abstraction layer (3) framework (3) maintenance (3) Go (2) Golang (2) HTML5 (2) ORM (2) PDF (2) Slim (2) Symfony (2) Zend Framework (2) Zephir (2) firewall (2) log files (2) loops (2) quality (2) real-time (2) scrum (2) streaming (2) AOP (1) Apache (1) Arrays (1) C (1) DDoS (1) Deployment (1) DoS (1) Dropbox (1) HTML to PDF (1) HipHop (1) OCR (1) OOP (1) Objects (1) PDO (1) PHP extension (1) PhantomJS (1) SPL (1) SQLite (1) Server-Sent Events (1) Silex (1) Smarty (1) SplFixedArray (1) Unicode (1) V8 (1) analytics (1) annotations (1) apc (1) archiving (1) autoloading (1) awk (1) caching (1) code quality (1) column store (1) common mistakes (1) configuration (1) controller (1) decisions (1) design patterns (1) disk space (1) dynamic routing (1) file cache (1) garbage collector (1) good developer (1) html2pdf (1) internationalization (1) invoice (1) just-in-time compiler (1) kiss (1) knockd (1) legacy code (1) legacy systems (1) logtop (1) memcache (1) memcached (1) micro framework (1) ncat (1) node.js (1) openssh (1) pfff (1) php7 (1) phpng (1) procedure models (1) ramdisk (1) recursion (1) refactoring (1) references (1) regular expressions (1) search (1) security (1) sgrep (1) shm (1) sorting (1) spatch (1) ssh (1) strange behavior (1) swig (1) template engine (1) threads (1) translation (1) ubuntu (1) ufw (1) web server (1) whois (1)