449305 - SQL queries are inefficiently done

Status: RESOLVED FIXED

(Webtools Graveyard :: Verbatim, defect)

Description

[Dan Schafer [:dschafer]]

In the naive conversion from flat files to SQL queries, for more queries are used than are needed; for example, we should make sure that one query is used to get all the languages and other information on the main page, rather than one to get the languages, and one per language for the other information.

The largest offenders are the current rights draft implementation (see bug 449132) and quickstats.

Comment 1

[Dan Schafer [:dschafer]]

As of revision 7993, there are 122 queries on the main page for an install with Terminology, Pootle and a simple, three-language demo project.

The breakdown of queries is:
69 "SELECT quickstats"
47 "SELECT submissions"
4 "SELECT users"
1 "SELECT languages"
1 "SELECT projects"

I suspect the quickstats and the submissions are all being done for the generation of the main table completion percentage and last-updated, but this is clearly an inefficient way of going about it.

Comment 2

[Dan Schafer [:dschafer]]

it looks like the major issue is in getlanguages() and getprojects() in indexpage.py.  Because quickstats isn't ORMed and relationed like all the other tables, I had to do one query for each lang/project combo.  ORMing quickstats should turn those functions into one-query a-piece, which should remove all the quickstats queries (since they will be included in the language/project query).

Comment 3

[Dan Schafer [:dschafer]]

Attachment: Patch to remove submissions query

This patch adds an extra languages and project query to remove all of the submissions queries; this reduces the total number of queries on the main page to 76: 69 quickstats, 3 users, 2 projects, and 2 languages.

Comment 4

[Wil Clouser [:clouserw]]

Comment on attachment 332457 [details] [diff] [review]
Patch to remove submissions query

Thanks Dan.  r8029