TranslationGroup:+index slow 1-2% of requests timing out

[Robert Collins]

TranslationGroup:+index is exceeding our timeout threshold 1-2% of the time according to https://devpad.canonical.com/~stub/ppr/lpnet/latest-daily-timeout-candidates.html

This isn't a terrible issue, but the distribution is pretty wide - we can expect this page to continually give us trouble as we lower timeouts, unless we can make it consistently fast.

[Jeroen T. Vermeulen (jtv)]

I spent some time looking at OOPS-1702F648. Most of the time (though not by a very wide margin) goes into database queries. A good portion of those are avoidable:

 * Icons for projects, project groups, and distributions (24% of queries). Easily prejoined.
 * Product licensing status for the Product URL formatter (15% of queries). We could skip that, or use ProductWithLicenses to prejoin.
 * There's a bit of redundant iterate/is-empty querying on the translators. Should be easy to put the horse before the cart.
 * Team membership counts. Would need some extra helpers (useful in many places I imagine) but should be prejoinable.

Finally, the template checks context/required:launchpad.Edit a lot. At least for the TranslationGroup itself, the view could evaluate that once. I don't know if it'll eliminate a lot of queries but it can save time in Zope. It'd be harder to do for the Edit check on individual translators though.

[Jeroen T. Vermeulen (jtv)]

I just tried it on edge. The page for the Ubuntu translation group went from 977 queries to 303. Consecutive loads completed their server-side rendering in respectively 6.74, 4.05, and 7.27 seconds.

Still a bit slow, but a massive success in eliminating queries!

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11498 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11498>.

[Robert Collins (lifeless)]

Thats great Jeroen, lets see how it goes in the PPR early next cycle,
we may need to revisit it : data will tell.

[Jeroen T. Vermeulen (jtv)]

Actually it seems that, due to additions in the database, the page has gone up to 1055 queries on production, whereas it's still 303 with the new version of the page. Unfortunately the oopses are messed up right now (bug 630612) so it's hard to say what the balance of SQL time versus non-SQL time is now.

[Robert Collins (lifeless)]

While it will be a pain, if you add the start of the query to the
duration, you'll get the right timing.