launchpad-dev team mailing list archive

[Jeroen Vermeulen <jtv@xxxxxxxxxxxxx>]

Hi folks,

This could be an easy big win for someone who feels like fixing a 
performance problem.

One of our slowest requests is 
LanguageSet:CollectionResource:#languages, an API request.

An example of the oops is here: 
https://lp-oops.canonical.com/oops.py/?oopsid=1891B10

What does it do?  Why is it so slow?  As near as makes no difference, 
all of the request's time is spent counting contributors for each 
individual language out of the karma cache!

Like so:

SELECT COUNT(*)
FROM KarmaCache,
     KarmaCategory,
     Person,
     PersonLanguage
WHERE PersonLanguage.person = Person.id
  AND PersonLanguage.LANGUAGE = <language_id>
  AND KarmaCache.person = Person.id
  AND KarmaCache.product IS NULL
  AND KarmaCache.project IS NULL
  AND KarmaCache.sourcepackagename IS NULL
  AND KarmaCache.distribution IS NULL
  AND KarmaCache.category = KarmaCategory.id
  AND KarmaCategory.name = 'translations'
  AND (1=1)

This looks like it could be sped up very very easily:

SELECT PersonLanguage.language, COUNT(*)
FROM KarmaCache,
     KarmaCategory,
     Person,
     PersonLanguage
WHERE PersonLanguage.person = Person.id
  AND KarmaCache.person = Person.id
  AND KarmaCache.product IS NULL
  AND KarmaCache.project IS NULL
  AND KarmaCache.sourcepackagename IS NULL
  AND KarmaCache.distribution IS NULL
  AND KarmaCache.category = KarmaCategory.id
  AND KarmaCategory.name = 'translations'
  AND (1=1)
GROUP BY PersonLanguage.language

Seriously, it would save about 13 seconds on that particular timeout and 
who knows how many seconds beyond the timeout.  Be the day's hero!

It's bug 728174.


Jeroen