person:+activereviews times out

I can see two areas that can be improved here:
a) this page issues a huge query (4.5s)
and
b) 1000+ small queries.

a) tried using CTE to see if the query could be improved inplace but no luck so far (https://pastebin.canonical.com/55779/, https://pastebin.canonical.com/55780/).

b) this can probably be improved by caching related objects up front.

b) is addressed by lp:~rvb/launchpad/activereviews-bug-867941.

jtv found that using a temporary table with an index in lieu et place of the CTE improves things quite significantly:
https://pastebin.canonical.com/55838/. The temp tables takes ~1s to setup and the main query now takes ~200ms (https://pastebin.canonical.com/55836/).

This is pretty amazing but it means that we would have to use the master store to be able to create the temporary table.

It seems that my run of the CTE version was simply unlucky (locking…). a new run of the exact same query by stub show that it improves things significantly: https://pastebin.canonical.com/55946/.

I will also see if it's possible/easy to avoid materializing the big list of all public and private-but-visable branches.

FWIW: you don't need the master store to use temp tables.

Right, stub also said so this morning too.

I've a branch ready that refactors ./lib/lp/code/model/branchcollection.py a little to issue a CTE version.

r14330 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/14330) is part of this bug's fix.

Looks like eager loading related objects did good (number of queries has been / 2) but there is still room for improvement.

r14355 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/14355) is part of this bug's fix.

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

I've reverted the changes made by: https://code.launchpad.net/~rvb/launchpad/activereviews-bug-867941-hugequery

r14374 is the rollback to r14355.

Only one repeated statement is left: the one to get the chain of stacked_on branches. This is a tricky one because the information here is recursive but we should be able to prefetch that data too.

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

Total number of queries is down to 46 (qastaging). Marking this as qa-ok.

Timed out the first time with OOPS-b8497e045278f93da7c4acce455fdf47, but succeeded with 46 queries/external actions issued in 6.00 seconds a second time.

@wgrant: this is really weird because the oops you pasted has some repeated statements (SourcePackageName) that I know for a fact are pre-fetched now.

Ah, looking at the OOPS, I must have hit the appservers when not all of them were updated. It is from the old revno, so nevermind.