NexusMods Posted October 10, 2023 Posted October 10, 2023 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. G17 Media 1
Stuart Silvester Posted October 10, 2023 Posted October 10, 2023 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 Posted October 10, 2023 Author Posted October 10, 2023 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.
Recommended Posts