Too many sql queries issued creating a question from a bug report

[Diogo Matsubara]

As seen in OOPS-915E1644 the +create-question page issues >2000 queries

So far it has not timeout badly, but the page needs optimization nevertheless

[Curtis Hovey (sinzui)]

The the code made too many queries for AnswerContacts and and the languages they speak. I have a branch the moves most the the logic into query.

[Curtis Hovey (sinzui)]

Fix committed in RF 7222;

[Curtis Hovey (sinzui)]

I'm passing this to salgado because he has a comprehensive fix to deal with the underlying issue of person.preferredemail.

[Björn Tillenius (bjornt)]

Salgado, what's the status of this fix?

[Guilherme Salgado (salgado)]

My fix changes QuestionTargetMixin.answer_contacts to return decorated
IPerson objects (with their .languages pre-cached). I also did a few
hacks to make them compare equally to their non-decorated counterparts
and to make storm think they're Person objects.

The problem now is that these decorated objects get security-proxied
when .answer_contacts is called from outside of DB code, and there are
no security declarations for the class which decorates IPerson. Is
there a way to copy all the security declarations from a given class to
another one, in python, so that I don't have to duplicate all that zcml
we have to protect Person?

[Francis J. Lacoste (flacoste)]

Yes, there is:

withing the class definition you can use:

<class class="...">
   <require like_class="..." />
</class>

[Guilherme Salgado (salgado)]

The first approach I tried (with a decorated class caching all of a person's languages) didn't work because there's no way to make a decorated object's identity be the same as its non-decorated counterpart. That means we can't really mix them together, specially when we use them in a set().

My second approach makes Person.languages a cachedproperty, and changes .answer_contacts to pre-fill that cache for all answer contacts, thus issuing a single query.

[Guilherme Salgado (salgado)]

another tentative fix landed on r7381

[Guilherme Salgado (salgado)]

OOPS-1068S156 is from before the last fix landed. It reports a total of 3253 SQL statements, with nearly 2000 being SELECTs on the Language table.

These 2000 are all gone, as we can see on OOPS-1069S404.

However, that page still does around 400 INSERTs in the BugNotificationRecipient table and 200 SELECTs in the EmailAddress table, which account to almost half of its total statements (1354)

[Guilherme Salgado (salgado)]

Also note the non-sql-time reported in the OOPSes: more than 6 minutes!