Improve Give's performance donations queries

[Gilles2705]

We're experimented GiveWP users and notice recently that we could Improve Give's performance when querying donations..

For example, the API query that fetches the list of donations and displays it in the "list of donations" back office view is based on the much-used

give()->donations->prepareQuery()

which, in order to add donation metadata, performs an absolutely monstrous join. It's so huge that MySQL writes intermediate tables to disk to get by.

The result is an SQL query that takes 10 seconds on a site with around 1000 donations.

We have a solution to propose that would reduce the query time to 0.086 seconds.

SOLUTION

We think that the attachMeta, getRowAsModel and getAllAsModel methods of the Give\Framework\Models\ModelQueryBuilder should be modified. It can be relatively light in terms of modifications

Instead of retrieving the metadata by join, it is better to use the update_meta_cache and give_get_meta functions which will do the same work in a much more optimized way.

[JasonTheAdams]

Thanks for adding your thoughts, @Gilles2705! Performance is something we've worked a bunch on but, as you are pointing out, there's still more room. Ideally, we'd have a far more optimized database structure for donations, which is something we're planning to address, but we're still adopting wide use of the models to make this safe.

When building this we did a bunch of tests and found that using JOINs was equal to or more than performant than the N+1 issue of using the WP meta functions. It's really tricky, as we're bypassing the cache doing it this way, but also avoiding N+1 queries — so there's a loss one way or the other. This is especially true when querying many donations at once, as 100 donations with 20 meta is 2000 potential queries. Yikes.

I'm riffing here, but I can think of a couple strategies for improvements:

1. Make better use of object caching within our repositories to cache the whole model
   • This would also mean invalidating the caches when objects are edited
2. Break apart our queries into two pieces: base object and meta
   1. First we'd query all the non-meta data
   2. Then we'd run through all the meta checking to see if any of it is cached
   3. If meta is cached, set aside those values
   4. For all remaining meta keys perform the bulk meta query
   5. Store all meta in the cache

Mind you, the latter would only work if an entire column of meta was cached. If we're querying 100 donations and only 5 have a cached meta key, then it's simpler to just query all 100 for that key. Still, subsequent queries certainly would get faster.

I'm inclined to start with the first strategy, as it makes sense to have anyway, and the latter would require considerable refactoring of the queries.

It sounds like you have tested this a bit, @Gilles2705. You're more than welcome to add a snippet or, better yet, introduce a PR that we can run tests against. I'm sure you're right that the query time can go down quite a bit, but I'm guessing that 0.086 seconds you mentioned is after everything is already cached.