admin/ticket/milestones and Roadmap pages slow

[Jeffrey.Ratcliffe@…]

The admin/ticket/milestones and Roadmap pages take several minutes to load, as we have ~16000 tickets and a couple of hundred milestones.

Is it possible to customise those two pages such that it does not search through all tickets, just to edit or delete a milestone?

[Jun Omae]

What database are you using?

[Jun Omae]

Okay. Trac executes SELECT COUNT(*) ... query for each milestone.

Could you try the following patch?

trac/ticket/admin.py

@@ -325 +325 @@
                         req.redirect(req.href.admin(cat, page))
 
             # Get ticket count
-            milestones = [
-                (milestone, self.env.db_query("""
-                    SELECT COUNT(*) FROM ticket WHERE milestone=%s
-                    """, (milestone.name,))[0][0])
-                for milestone in model.Milestone.select(self.env)]
+            counts = dict(self.env.db_query("""
+                    SELECT milestone, COUNT(milestone) FROM ticket
+                    WHERE milestone != ''
+                    GROUP BY milestone
+                """))
+            milestones = [(milestone, counts.get(milestone.name, 0))
+                          for milestone in model.Milestone.select(self.env)]
 
             data = {'view': 'list',
                     'milestones': milestones,

[Ryan J Ollos]

That change looks good. I was thinking about #11018, and the possibility that the milestone field could contain either NULL or the empty string, for installations created prior to 1.0.3 (values are now stored as NULL rather than the empty string, but we didn't attempt to fixup instances of the empty string). Based on evaluation with SQLite though, it looks like WHERE milestone != '' will filter out NULL and empty string entries in the database, which I think is the desired behavior. Can it be expected that any SQL comparison x != '' will evaluate to true if x is NULL or an empty string?

[Jun Omae]

Replying to Ryan J Ollos:

Can it be expected that any SQL comparison x != '' will evaluate to true if x is NULL or an empty string?

The x != '' on any databases must not evaluate to true if x is NULL or an empty string. This behavior is based on SQL standard.

x's value	result of x != ''
'blah'	True
'' (an empty string)	False
NULL	Unknown

See also:

• ​https://en.wikipedia.org/wiki/Three-valued_logic#Application_in_SQL
• ​https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29

[Jeffrey.Ratcliffe@…]

The patch makes things MUCH better, i.e. ~5min → ~15sec

Can you do something similar for the roadmap page?

[Jeffrey.Ratcliffe@…]

Thanks for the patch, btw.

I forgot to add that we are using SQLite.

[Jun Omae]

Replying to Jeffrey.Ratcliffe@…:

The patch makes things MUCH better, i.e. ~5min → ~15sec

Thanks for the feedback.

Can you do something similar for the roadmap page?

I noticed the roadmap page has a similar issue. I'm creating a patch for it.

[Jun Omae]

Could you please try a changes in [a61097044/jomae.git?format=diff]?

[Jeffrey.Ratcliffe@…]

The patch for the roadmap also makes this much better - but only if I disable the simplemultiprojectplugin.

[Jun Omae]

The patch for the roadmap also makes this much better

Thanks. I'm going to apply the changes to 1.0-stable branch.

but only if I disable the simplemultiprojectplugin.

That is an issue of the plugin. Please report to maintainers of the plugin on trac-hacks.org.

[Ryan J Ollos]

Replying to Jun Omae:

The x != '' on any databases must not evaluate to true if x is NULL or an empty string. This behavior is based on SQL standard.

Thank you for the explanation and references.

[Jun Omae]

Committed in [15192] and merged in [15193,15194].

get_num_tickets_for_milestone method is introduced in [15078] (1.2dev) however it leads the same issue. Use of the method has been removed in [15193].