Faster DatabaseConnection::prefixTables() when database prefix is an array [#960798]

Attached patch uses a single instance of str_replace which is fed pre-processed find/replace arrays extrapolated from the database prefix setting. Speed increase is found by using array_walk() to modify the prefix array instead of looping through the array using PHP.

For sites where the database prefix is an array of values the speed improvement for this function is about 40-50% depending on size of the prefix array. Where the prefix is a string there is a very small performance hit of about 5%.

Also attached is a reduced test showing speed improvements over the old method (extension renamed).

I have a similar patch for Drupal 6 if anyone is interested in that too.

Comment	File	Size	Author
#3	faster-prefixtables-2.patch	710 bytes	sambauers
#3	faster-prefixtables-speedtest-2.php_.txt	11.49 KB	sambauers
	faster-prefixtables-speedtest.php_.txt	9.09 KB	sambauers
	faster-prefixtables.patch	1.87 KB	sambauers

Comments

Comment #1

sambauers commented 3 November 2010 at 06:38

Attached patch is actually slower than the old method when there are no replacements to be made in the string, but how often does that happen? Not much I'm guessing. On the other hand, patch gets comparatively faster the more replacements there are to be done in the string.

Comment #2

Crell commented 13 November 2010 at 05:42

Category:	bug	» feature
Priority:	Normal	» Minor
Status:	Active	» Postponed (maintainer needs more info)

Actually, DB prefixes are the minority case by a huge margin. That's the case we should optimize for.

If you can provide a patch that speeds up the multi-prefix case but doesn't harm the normal case we can consider it.

Comment #3

sambauers commented 17 November 2010 at 01:34

Status:

Postponed (maintainer needs more info)

» Needs review

Status	File	Size
new	faster-prefixtables-speedtest-2.php_.txt	11.49 KB
new	faster-prefixtables-2.patch	710 bytes

In further testing and tweaking I've reduced this down to a simple replacement of strtr() with str_replace().

This showed small loses in the simplest cases of approximately 5% - 10% on what are already very fast cases. So the loss over 100 iterations is a maximum of 1/100000th of a second on my test bed running PHP 5.3.

On the other hand, the improvement in all cases where there are values in the prefix array is between 25% and 1500%, in some cases saving 1/2 a second over 100 iterations.

I'm uploading a new patch (faster-prefixtables-2.patch) and more thorough isolated test script that compares speed over several cases (faster-prefixtables-speedtest-2.php_.txt).

This is a pretty big deal for our organisation due to the way that our CiviCRM install is setup using an array of prefixes.