プリペアードステートメントのパフォーマンス測定

# 概要
PHPのリファレンスには、プリペアードステートメントについて以下のように記載されている。
> 同じクエリを異なったパラメータで何度も実行すると、アプリケーションの 動作は目に見えて遅くなるでしょう。 プリペアドステートメントを使用すると、この 解析/コンパイル/最適化 の繰り返しを避けることができます。 端的に言うと、プリペアドステートメントは使用するリソースが少なくいため 高速に動作するということです。

http://php.net/manual/ja/pdo.prepared-statements.php

そこで、プリペアードステートメントを使用した場合と使用しなかった場合のパフォーマンスを比較してみる事にした。

# 検証方法
## 手順
検証用テーブルに、ランダムな値を持つレコードを65536行(2^16行)挿入する。

クエリはバルクインサート
(`INSERT INTO test (num) VALUES (1), (2), ...`)
を使用し、1回のクエリで挿入する行数を

- 2行
- 4行
- 8行
- ...

と、次第に増やしていく。

以上の操作をプリペアードステートメントを使用する場合と使用しない場合の双方で10回ずつ試行し、かかった時間の平均値を比較する。

なお、クエリを実行するスクリプトとDBは別々のサーバに配置されており、
両サーバはローカルネットワークで接続されている。

## 検証環境
- サーバ（AP, DBともに同スペック）
  - OS : CentOS 5.8
  - CPU : Intel(R) Xeon(R) CPU X5675  @ 3.07GHz
  - Memory : 1026080 kB
- DB : MySQL 5.5
- スクリプト : PHP 5.3
- DB接続ドライバ : PDO

*検証用テーブル*

```sql
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
```

*PHPのソースコード*

```php
<?php
error_reporting(E_ALL);

define ('BASE', 2);
define ('MAX_EXP', 16);
define ('NUMBER_OF_RECORD', pow(BASE, MAX_EXP));
define ('TEST_COUNT', 10);

for ($exp = 1; $exp <= MAX_EXP; $exp++) {
    $chunkSize = pow(BASE, $exp);
    $emulatePrepares = test($chunkSize, true);
    $usePrepares = test($chunkSize, false);

    print "$chunkSize\t$emulatePrepares\t$usePrepares\n"; 
}

function test($chunkSize, $emulatePrepares) {
    $dbh = createDBHandler($emulatePrepares);
    for ($i = 0; $i < TEST_COUNT; $i++) {
        $numbers = createRandomNumbers(NUMBER_OF_RECORD);
        truncate($dbh);
        $result[] = insertCollectively($dbh, $numbers, $chunkSize);
    }

    return array_sum($result) / TEST_COUNT;
}

/**
 * 配列$numbersを、$chunkSize行ずつインサートする。
 * 
 * $chunkSizeは、$numbersを割り切る数でなければならない。
 */
function insertCollectively(PDO $dbh, array $numbers, $chunkSize) {
    $placeHolders = array_pad(array(), $chunkSize, '(?)');
    $valuesClause = implode(', ', $placeHolders);
    $stmt = $dbh->prepare('INSERT INTO test (num) VALUES ' . $valuesClause);

    $before = microtime(true);

    $dbh->beginTransaction();
    foreach (array_chunk($numbers, $chunkSize) as $chunkedNumbers) {
        $i = 1;
        foreach ($chunkedNumbers as $number) {
            $stmt->bindValue($i++, $number, PDO::PARAM_INT);
        }
        $stmt->execute();
    }
    $dbh->commit();

    return microtime(true) - $before;
}

function truncate($dbh) {
    $dbh->exec('TRUNCATE TABLE test');
}

function createDBHandler($emulatePrepares) {
    $dbh = new PDO('mysql:XXXXX', 'XXXXX', 'XXXXX');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);

    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulatePrepares);

    return $dbh;
}

function createRandomNumbers($count) {
    $numbers = array();
    for ($i = 0; $i < $count; $i++) {
        $numbers[] = mt_rand();
    }
    return $numbers;
}
?>
```

# 検証結果

|bulk count|emulate prepare|use prepare|
|-----------:|------------:|:------------:|
|2|5.053875351|5.017175078|
|4|2.835401201|2.725035954|
|8|1.637799835|1.598171449|
|16|1.020874143|0.973363829|
|32|0.769477415|0.734067106|
|64|0.645589924|0.63260026|
|128|0.596083951|0.568830323|
|256|0.556294942|0.562757993|
|512|0.52461729|0.583038902|
|1024|0.532318234|0.696268725|
|2048|0.543481255|0.841279483|
|4096|0.560284638|1.188867807|
|8192|0.547622371|1.861706996|
|16384|0.539314747|3.190056157|
|32768|0.535651708|5.886143303|

![preparetest.png](https://qiita-image-store.s3.amazonaws.com/0/39426/36ec1256-545a-f531-8b4d-63d2b4d8f03e.png)

意外な事に、

- 1度に挿入する行数が2〜256行の辺りまでは双方とも同程度のパフォーマンスで、
- 256行を超えた頃から、プリペアードステートメントを使用しない方がパフォーマンスが上がる

という、公式のリファレンスの説明とは逆の結果になった。
理由は分からない。


ちなみに、1度に65536行を挿入しようとすると、以下のようなエラーが発生した。

```
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders' in /tmp/test.php:36
Stack trace:
#0 /tmp/test.php(36): PDO->prepare('INSERT INTO tes...')
#1 /tmp/test.php(22): insertCollectively(Object(PDO), Array, 65536)
#2 /tmp/test.php(12): test(65536, false)
#3 {main}
  thrown in /tmp/test.php on line 36
```

1回のクエリで使用できるプリペアードステートメントの数には、限界があるらしい。