Make SQLite faster by combining multiple inserts and updates in a single query

Problem/Motivation

The original problem was that SQLite has a variable limit of 999 for versions before 3.32.0 (core 10 requires SQLITE_MINIMUM_VERSION = '3.26') which is not configurable and which makes this database driver unusable for medium sized sites. The solution that we came up with is combining multiple inserts and updates in a single query, which makes SQLite a lot faster. The newer minimum required version of SQLite has a much higher variable limit and therfor it is no longer a problem in combination with Drupal. Only it makes multiple insert queries and multiple upsert queries a lot faster. See #2031261-124: Make SQLite faster by combining multiple inserts and updates in a single query → . In the order of from 108k microseconds to 16k microseconds.

Proposed resolution

Combining multiple inserts and in a single query and combining multiple upserts and in a single query. For both queries is having more than 50 variables the limit for merging it into a single query.

Original report by @axel.rutz

SQLite performance Optimization: PDO::ATTR_EMULATE_PREPARES

Like we've done on Mysql and Postgres we should also do on Sqlite. See #827554: PostgreSQL performance Optimization: PDO::ATTR_EMULATE_PREPARES → .

(As suggested by Damien Tournoud in #2028713-11: Menu router does "too many SQL variables" on SQLite resulting in silent nondisplay of menu items → )

See #2028713: Menu router does "too many SQL variables" on SQLite resulting in silent nondisplay of menu items → but this can bite us in any other place too.

A first patch that set PDO::ATTR_EMULATE_PREPARES on the connection (proposed by Damien Tournoud) did not work out probably due to a bug in the underlying driver. This can be reproduced as outlined in #2028713-18: Menu router does "too many SQL variables" on SQLite resulting in silent nondisplay of menu items → :

php > $options = array(
php (   PDO::ATTR_EMULATE_PREPARES => true,
php (   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
php ( );
php > $dbh=new PDO('sqlite:dummy.sqlite', NULL, NULL, $options);
php > $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); // Give it another try.
php > $values = range(1,1111);
php > $placeholders = implode(',', array_fill_keys(array_keys($values), '?'));
php > $query = "select v from (select 0 v) where v not in ($placeholders);";
php > $stmt = $dbh->prepare($query);
PHP Warning:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 too many SQL variables' in php shell code:1