Merge lp:~deryck/launchpad/max-heat-timeout-again-618403 into lp:launchpad

[Deryck Hodge]

Find a compromise query which works for debian and ubuntu heat calculations with tolerable performance.

Round and round we go. Is it spelled MAX(Bug.heat) or ORDER BY
Bug.heat DESC LIMIT 1?

Drum roll......

ORDER BY/LIMIT 1 wins in a taste test challenge (with an occassional
FOO IS NOT NULL thrown in).

lifeless will know what I mean and stands ready to approve.

Cheers,
deryck

[Robert Collins (lifeless)]

\o/

may want to factor out the common bits of those queries in future.

review: Approve

=== modified file 'lib/lp/bugs/model/bugtarget.py'
--- lib/lp/bugs/model/bugtarget.py	2010-08-22 18:31:30 +0000
+++ lib/lp/bugs/model/bugtarget.py	2010-09-02 22:12:51 +0000
@@ -271,32 +271,44 @@
             # recalculating max_heat.
             return
 
+        # XXX: deryck The queries here are a source of pain and have
+        # been changed a couple times looking for the best
+        # performaning version.  Use caution and have EXPLAIN ANALYZE
+        # data ready when changing these.
         if IDistribution.providedBy(self):
-            sql = ["""SELECT MAX(Bug.heat)
+            sql = ["""SELECT Bug.heat
                       FROM Bug, Bugtask
                       WHERE Bugtask.bug = Bug.id
-                      AND Bugtask.distribution = %s""" % sqlvalues(self),
-                   """SELECT MAX(Bug.heat)
+                      AND Bugtask.distribution = %s
+                      ORDER BY Bug.heat DESC LIMIT 1""" % sqlvalues(self),
+                   """SELECT Bug.heat
                       FROM Bug, Bugtask, DistroSeries
                       WHERE Bugtask.bug = Bug.id
                       AND Bugtask.distroseries = DistroSeries.id
-                      AND DistroSeries.distribution = %s""" % sqlvalues(self)]
+                      AND Bugtask.distroseries IS NOT NULL
+                      AND DistroSeries.distribution = %s
+                      ORDER BY Bug.heat DESC LIMIT 1""" % sqlvalues(self)]
         elif IProduct.providedBy(self):
-            sql = ["""SELECT MAX(Bug.heat)
+            sql = ["""SELECT Bug.heat
                       FROM Bug, Bugtask
                       WHERE Bugtask.bug = Bug.id
-                      AND Bugtask.product = %s""" % sqlvalues(self),
-                   """SELECT MAX(Bug.heat)
+                      AND Bugtask.product = %s
+                      ORDER BY Bug.heat DESC LIMIT 1""" % sqlvalues(self),
+                   """SELECT Bug.heat
                       FROM Bug, Bugtask, ProductSeries
                       WHERE Bugtask.bug = Bug.id
+                      AND Bugtask.productseries IS NOT NULL
                       AND Bugtask.productseries = ProductSeries.id
-                      AND ProductSeries.product = %s""" % sqlvalues(self)]
+                      AND ProductSeries.product = %s
+                      ORDER BY Bug.heat DESC LIMIT 1""" % sqlvalues(self)]
         elif IProjectGroup.providedBy(self):
-            sql = ["""SELECT MAX(heat)
+            sql = ["""SELECT heat
                       FROM Bug, Bugtask, Product
-                      WHERE Bugtask.bug = Bug.id AND
-                      Bugtask.product = Product.id AND
-                      Product.project =  %s""" % sqlvalues(self)]
+                      WHERE Bugtask.bug = Bug.id
+                      AND Bugtask.product = Product.id
+                      AND Product.project IS NOT NULL
+                      AND Product.project =  %s
+                      ORDER BY Bug.heat DESC LIMIT 1""" % sqlvalues(self)]
         else:
             raise NotImplementedError