Greatly improve database query speeds in Review Board.

Review Request #884 — Created June 7, 2009 and submitted

Information

Review Board SVN (deprecated)
trunk

Reviewers

Greatly improve database query speeds in Review Board.

This change introduces a few major things to increase database query
speed.

1) Instead of subqueries for the ship-it count and last reviewed
   timestamp, we now store these directly on the ReviewRequest, making
   sure to set them when publishing a review. This reduces our query
   time a good amount, as we no longer need to do these subqueries on
   every ReviewRequest retrieved from the database.

2) We no longer check for either 'P' (published) or 'S' (submitted)
   flags when showing submitted reviews in the datagrid. We instead now
   check for NOT 'D', which is equivalent to the query we had before,
   but faster.

3) We add the subquery for the new_review_count as late as possible
   for datagrids, after we've figured out the IDs of every ReviewRequest
   we're going to display.
This was tested against a worst-case query where we were showing all
review requests that are either public or owned by the logged-in user
and either published or submitted, ordered primarily by username and
secondarily by summary. This previous took around 65 seconds to complete
on a database with over 80,000 review requests, and now takes 2 or 3
seconds.
david
  1. 
      
  2. /trunk/reviewboard/cmdline/rbsite.py (Diff revision 1)
     
     
    Perhaps this should be a separate change?
    1. Yeah. It sorta snuck in.
  3. /trunk/reviewboard/reviews/datagrids.py (Diff revision 1)
     
     
    Can you add a comment for this?
  4. Awesome. Thank you so much for tackling this.