Greatly improve database query speeds in Review Board.
Review Request #884 — Created June 7, 2009 and submitted
|Review Board SVN (deprecated)|
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.