SQL Parser Benchmark

benchmark.php

<?php

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ExpandArrayParameters;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\SQL\Parser;
use Doctrine\DBAL\SQLParserUtils;

require __DIR__ . '/vendor/autoload.php';

function expandOld(string $sql, array $params, array $types): array
{
    return SQLParserUtils::expandListParameters($sql, $params, $types);
}

function expandNew(string $sql, array $params, array $types): array
{
    $parser  = new Parser(true);
    $visitor = new ExpandArrayParameters($params, $types);

    $parser->parse($sql, $visitor);

    return [
        $visitor->getSQL(),
        $visitor->getParameters(),
        $visitor->getTypes(),
    ];
}

function dataProvider()
{
    return [
        'Positional: Very simple with one needle' => [
            'SELECT * FROM Foo WHERE foo IN (?)',
            [[1, 2, 3]],
            [Connection::PARAM_INT_ARRAY],
        ],
        'Positional: One non-list before d one after list-needle' => [
            'SELECT * FROM Foo WHERE foo = ? AND bar IN (?)',
            ['string', [1, 2, 3]],
            [ParameterType::STRING, Connection::PARAM_INT_ARRAY],
        ],
        'Positional: One non-list after list-needle' => [
            'SELECT * FROM Foo WHERE bar IN (?) AND baz = ?',
            [[1, 2, 3], 'foo'],
            [Connection::PARAM_INT_ARRAY, ParameterType::STRING],
        ],
        'Positional: One non-list before and one after list-needle' => [
            'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?',
            [1, [1, 2, 3], 4],
            [ParameterType::INTEGER, Connection::PARAM_INT_ARRAY, ParameterType::INTEGER],
        ],
        'Positional: Two lists' => [
            'SELECT * FROM Foo WHERE foo IN (?, ?)',
            [[1, 2, 3], [4, 5]],
            [Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY],
        ],
        'Positional: Empty "integer" array (DDC-1978)' => [
            'SELECT * FROM Foo WHERE foo IN (?)',
            [[]],
            [Connection::PARAM_INT_ARRAY],
        ],
        'Positional: Empty "str" array (DDC-1978)' => [
            'SELECT * FROM Foo WHERE foo IN (?)',
            [[]],
            [Connection::PARAM_STR_ARRAY],
        ],
        'Positional: explicit keys for params and types' => [
            'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
            [1 => 'bar', 2 => 'baz', 0 => 1],
            [2 => ParameterType::STRING, 1 => ParameterType::STRING],
        ],
        'Positional: explicit keys for array params and array types' => [
            'SELECT * FROM Foo WHERE foo IN (?) AND bar IN (?) AND baz = ?',
            [1 => ['bar1', 'bar2'], 2 => true, 0 => [1, 2, 3]],
            [2 => ParameterType::BOOLEAN, 1 => Connection::PARAM_STR_ARRAY, 0 => Connection::PARAM_INT_ARRAY],
        ],
        'Positional starts from 1: One non-list before and one after list-needle' => [
            'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ? AND foo IN (?)',
            [0 => 1, 1 => [1, 2, 3], 2 => 3, 3 => [5, 6]],
            [
                ParameterType::INTEGER,
                Connection::PARAM_INT_ARRAY,
                ParameterType::INTEGER,
                Connection::PARAM_INT_ARRAY,
            ],
        ],
        'Named: Very simple with param int' => [
            'SELECT * FROM Foo WHERE foo = :foo',
            ['foo' => 1],
            ['foo' => ParameterType::INTEGER],
        ],
        'Named: Very simple with param int and string' => [
            'SELECT * FROM Foo WHERE foo = :foo AND bar = :bar',
            ['bar' => 'Some String','foo' => 1],
            ['foo' => ParameterType::INTEGER, 'bar' => ParameterType::STRING],
        ],
        'Named: Very simple with one needle' => [
            'SELECT * FROM Foo WHERE foo IN (:foo)',
            ['foo' => [1, 2, 3]],
            ['foo' => Connection::PARAM_INT_ARRAY],
        ],
        'Named: One non-list before d one after list-needle' => [
            'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)',
            ['foo' => 'string', 'bar' => [1, 2, 3]],
            ['foo' => ParameterType::STRING, 'bar' => Connection::PARAM_INT_ARRAY],
        ],
        'Named: One non-list after list-needle' => [
            'SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz',
            ['bar' => [1, 2, 3], 'baz' => 'foo'],
            ['bar' => Connection::PARAM_INT_ARRAY, 'baz' => ParameterType::STRING],
        ],
        'Named: One non-list before and one after list-needle' => [
            'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz',
            ['bar' => [1, 2, 3],'foo' => 1, 'baz' => 4],
            [
                'bar' => Connection::PARAM_INT_ARRAY,
                'foo' => ParameterType::INTEGER,
                'baz' => ParameterType::INTEGER,
            ],
        ],
        'Named: Two lists' => [
            'SELECT * FROM Foo WHERE foo IN (:a, :b)',
            ['b' => [4, 5],'a' => [1, 2, 3]],
            ['a' => Connection::PARAM_INT_ARRAY, 'b' => Connection::PARAM_INT_ARRAY],
        ],
        'Named: With the same name arg type string' => [
            'SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg',
            ['arg' => 'Some String'],
            ['arg' => ParameterType::STRING],
        ],
        'Named: With the same name arg' => [
            'SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)',
            ['arg' => [1, 2, 3]],
            ['arg' => Connection::PARAM_INT_ARRAY],
        ],
        'Named: Same name, other name in between (DBAL-299)' => [
            'SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)',
            ['foo' => 2,'bar' => 3],
            ['foo' => ParameterType::INTEGER,'bar' => ParameterType::INTEGER],
        ],
        'Named: Empty "integer" array (DDC-1978)' => [
            'SELECT * FROM Foo WHERE foo IN (:foo)',
            ['foo' => []],
            ['foo' => Connection::PARAM_INT_ARRAY],
        ],
        'Named: Two empty "str" array (DDC-1978)' => [
            'SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)',
            ['foo' => [], 'bar' => []],
            ['foo' => Connection::PARAM_STR_ARRAY, 'bar' => Connection::PARAM_STR_ARRAY],
        ],
        [
            'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar OR baz = :baz',
            ['foo' => [1, 2], 'bar' => 'bar', 'baz' => 'baz'],
            ['foo' => Connection::PARAM_INT_ARRAY, 'baz' => 'string'],
        ],
        [
            'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
            ['foo' => [1, 2], 'bar' => 'bar'],
            ['foo' => Connection::PARAM_INT_ARRAY],
        ],
        'Named parameters and partially implicit types' => [
            'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
            ['foo' => 'foo', 'bar' => 'bar'],
            ['foo' => ParameterType::INTEGER],
        ],
        'Named parameters and explicit types' => [
            'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
            ['foo' => 'foo', 'bar' => 'bar'],
            ['foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
        ],
        'Null valued parameters (DBAL-522)' => [
            'INSERT INTO Foo (foo, bar) values (:foo, :bar)',
            ['foo' => 1, 'bar' => null],
            ['foo' => ParameterType::INTEGER, 'bar' => ParameterType::NULL],
        ],
        'Escaped single quotes SQL- and C-Style (DBAL-1205)' => [
            "SELECT * FROM Foo WHERE foo = :foo||''':not_a_param''\\'' OR bar = ''':not_a_param''\\'':bar",
            ['foo' => 1, 'bar' => 2],
            ['foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
        ],
        [
            'SELECT NULL FROM dummy WHERE ? IN (?)',
            ['foo', ['bar', 'baz']],
            [1 => Connection::PARAM_STR_ARRAY],
        ],
    ];
}

xhprof_enable(XHPROF_FLAGS_MEMORY | XHPROF_FLAGS_CPU);

register_shutdown_function(static function () : void{
    file_put_contents(
        ini_get('xhprof.output_dir') . '/' . uniqid() . '.app.xhprof',
        serialize(xhprof_disable())
    );
});

$start = microtime(true);

$data   = dataProvider();
$repeat = 1000;

for ($i = 0; $i < $repeat; $i++) {
    foreach ($data as $arguments) {
        // 30 μs
        $result = expandOld(...$arguments);

        // 80 μs
        //$result = expandNew(...$arguments);
        assert($result !== $arguments);
    }
}

$avg = (microtime(true) - $start) / count($data) / $repeat;

printf('%d μs' . PHP_EOL,$avg * 1000000);

new.png

old.png