Optimize several queries for review requests.

Review Request #1496 — Created April 1, 2010 and submitted

Information

Review Board

Reviewers

Optimize several queries for review requests.

The Dashboard, despite recent improvements, is still slow for really huge
deployments. Most of this turned out to be a bug where, instead of passing in
a calculated list of IDs to another query, we were passing in the query to
get the list of IDs. This is slow. We're now properly getting that list of
IDs.

We also do smaller, less complicated queries for many things. Previously,
we were doing complex queries that caused the Profile and Group tables to be
joined. Now we look up the values we need beforehand and pass in the IDs
to the more complicated query. This reduces the overall time for these
queries and helps use the database sql query cache.

This also fixes the Number of Reviews column to not perform a query per-row.
Tested with a large database of hundreds of thousands of reviews. A particular dashboard visit went down from 22 seconds to just under 6.
chipx86
Review request changed
Change Summary:
Added a performance fix for the Number of Reviews column.
Description:
   

Optimize several queries for review requests.

   
   

The Dashboard, despite recent improvements, is still slow for really huge

    deployments. Most of this turned out to be a bug where, instead of passing in
    a calculated list of IDs to another query, we were passing in the query to
    get the list of IDs. This is slow. We're now properly getting that list of
    IDs.

   
   

We also do smaller, less complicated queries for many things. Previously,

    we were doing complex queries that caused the Profile and Group tables to be
    joined. Now we look up the values we need beforehand and pass in the IDs
    to the more complicated query. This reduces the overall time for these
    queries and helps use the database sql query cache.

  +
  +

This also fixes the Number of Reviews column to not perform a query per-row.

david
  1. Nice optimizations.
  2.