launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/bug-735998 into lp:launchpad.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #735998 in Launchpad itself: "ScopedCollection:CollectionResource:#message-page-resource timeouts"
  https://bugs.launchpad.net/launchpad/+bug/735998

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/bug-735998/+merge/79905

This branch fixes timeouts like OOPS-2118A36 (bug #735998). I rewrote the problematic query to use nested joins rather than a subquery, avoiding a seq scan on bugmessage. Stormification was also involved.

Old: https://pastebin.canonical.com/54636/
New: https://pastebin.canonical.com/54637/
-- 
https://code.launchpad.net/~wgrant/launchpad/bug-735998/+merge/79905
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/bug-735998 into lp:launchpad.

=== modified file 'lib/lp/bugs/model/bug.py'
--- lib/lp/bugs/model/bug.py	2011-10-19 20:19:27 +0000
+++ lib/lp/bugs/model/bug.py	2011-10-20 04:05:30 +0000
@@ -53,6 +53,7 @@
     And,
     Desc,
     In,
+    Join,
     LeftJoin,
     Max,
     Not,
@@ -553,22 +554,26 @@
                 # permit use.
                 message_by_id[message.id] = result
             return result
-        # There is possibly some nicer way to do this in storm, but
-        # this is a lot easier to figure out.
         if include_parents:
-            ParentMessage = ClassAlias(Message, name="parent_message")
-            tables = SQL("""
-Message left outer join
-message as parent_message on (
-    message.parent=parent_message.id and
-    parent_message.id in (
-        select bugmessage.message from bugmessage where bugmessage.bug=%s)),
-BugMessage""" % sqlvalues(self.id))
-            lookup = Message, ParentMessage, BugMessage
-            results = store.using(tables).find(
-                lookup,
+            ParentMessage = ClassAlias(Message)
+            ParentBugMessage = ClassAlias(BugMessage)
+            tables = [
+                Message,
+                Join(
+                    BugMessage,
+                    BugMessage.messageID == Message.id),
+                LeftJoin(
+                    Join(
+                        ParentMessage,
+                        ParentBugMessage,
+                        ParentMessage.id == ParentBugMessage.messageID),
+                    And(
+                        Message.parent == ParentMessage.id,
+                        ParentBugMessage.bugID == self.id)),
+                ]
+            results = store.using(*tables).find(
+                (Message, ParentMessage, BugMessage),
                 BugMessage.bugID == self.id,
-                BugMessage.messageID == Message.id,
                 )
         else:
             lookup = Message, BugMessage