Performance improvement in DB.preparedQuery

[NexusMods]

Sharing this in case it helps others, or can be merged upstream.

While migrating a large forum we were encountering some poor performance that was narrowed down to DB.preparedQuery. Changing the substitution approach yielded a 10x speedup.

Current implementation does:

/* For NULL values, you can't bind, so we adjust the query to actually pass a NULL value */
$pos = 0;
for ( $j=0; $j<$i; $j++ )
{
  $pos = mb_strpos( $query, '?', $pos ) + 1;
}
$query = mb_substr( $query, 0, $pos - 1 ) . 'NULL' . mb_substr( $query, $pos );

We replaced this with:

// Replace the i'th '?' character with 'NULL'.
$found = preg_match_all('/'.preg_quote('?').'/', $query, $matches, PREG_OFFSET_CAPTURE);
if (false !== $found && $found >= $i) {
  $query = substr_replace($query, 'NULL', $matches[0][$i - 1][1], 1);
}

If there is a better place to raise this for upstream then please let me know.

[Stuart Silvester]

Hi,

We wouldn't be able to use a change like that, it isn't multibyte friendly.

This code however hasn't come up in our profiling as being slow (I just checked again too). Could this be something specific to your server(s)?

This block of code is also in use elsewhere but doesn't seem to be an issue for you?

[NexusMods]

When we say slow we're talking the difference between 10ms and 1ms, but that makes a significant difference to the overall time when there are millions of records to process during the v3 to v4 upgrade (specifically inserting each member during IPS\core\setup\upg_40000\_Upgrade::step1).

Multibyte support is a good point, although it wasn't a problem during our upgrade as the prepared query wasn't using any multibyte chars.