DB Performance: hook_field_prepare_view & fivestar_get_votes to allow for database queries off multiple IDs at once.

So as mentioned in my original post, the use of hook_field_prepare_view should be avoided at all costs, as it can not be cached and requires a lot of database calls on each page load.

Here is my first patch at improving the use of hook_field_prepare_view for use with entity_load, node_load_multiple and node_view_multiple. When using this patch and node_view_multiple() to display your nodes, only a single query will be done per fivestar entry, instead of one for each node in the listing. This can significantly reduce the amount of database calls when displaying fivestar = 'expose' over many nodes at once.

The good thing about this patch, is that it should be backwards compatible and a drop in replacement for fivestar. Benefits will be had, when using node_view_multiple and no down side or backwards compatibility issues. This is done by creating a new function called fivestar_get_votes_multiple() which can take multiple IDs....and then fivestar_get_votes() uses fivestar_get_votes_multiple and passed in a single ID.

For me, with a listing of 20 nodes, each which have 7 fivestar's attached to them, it reduced the amount of queries for page load by 150.

Additional improvements can be made in the future (also performing single queries for user votes), but I consider this a drop in patch, before I start getting crazy and extending this idea further, which may break backwards compatibility.

Here's the documentation from hook_field_prepare_view() which mentions that it should be able to perform over multiple IDs at once and only use one query.

/**
 * Prepare field values prior to display.
 *
 * This hook is invoked before the field values are handed to formatters
 * for display, and runs before the formatters' own
 * hook_field_formatter_prepare_view().
 *
 * Unlike most other field hooks, this hook operates on multiple entities. The
 * $entities, $instances and $items parameters are arrays keyed by entity ID.
 * For performance reasons, information for all available entities should be
 * loaded in a single query where possible.
 *
 * Make changes or additions to field values by altering the $items parameter by
 * reference. There is no return value.
 *
 * @param $entity_type
 *   The type of $entity.
 * @param $entities
 *   Array of entities being displayed, keyed by entity ID.
 * @param $field
 *   The field structure for the operation.
 * @param $instances
 *   Array of instance structures for $field for each entity, keyed by entity
 *   ID.
 * @param $langcode
 *   The language associated to $items.
 * @param $items
 *   $entity->{$field['field_name']}, or an empty array if unset.
 */
function hook_field_prepare_view($entity_type, $entities, $field, $instances, $langcode, &$items) {}

Oops. Added by bad hunk patch of 7.x-2.x-alpha2, here's my off dev.

Using the patch above and being more diligent towards using node_view_multiple I was able to save quite a few queries on most of my pages with fivestar.

Here are my before and after numbers:

Homepage: From 112 votingapi queries to 84

Node Listing Page (20 total): 294 votingapi queries to 7.

Node Page (with not comments + fivestar): 28 votingapi queries to 14.

Node Page (with 3 comments + fivestar): 112 votingapi queries to 77.

Obivisouly the largest gains are when you're listing a lot of nodes. Most of my other pages, maybe like 2 - 4 in a group, but even in this case it reduces the amount of queries to the minimum.

My nodes all have 6 different ratings associated with them + the summary rating. Thus you'll notice all my numbers are multiples of 7. Fivestar is still by far one of the largest performance problems on my site, and I still would like to get these caching, instead of simply just preventing queries for fivestar to be performant.

Here's an updated page which improves my previous patch further by loading all user votes in one shot and I believe also resolves an issue with user votes from previous patch.

This patch a page 20 nodes and 7x fivestar fields each when loaded and viewed via node_load_multiple, node_view_multiple takes 56 queries total.

With out this patch, same page takes 329 queries total.

re-patched against latest -dev. Tested new version on my local development with patch seems to work. Will push to production site some time this week.

Been running my patch in production for almost a year now. Someone should really review this as there's large performance benefits for those with more than 1 rating per node.