• 
      

    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.