Edgewall Software
Modify

Opened 2 years ago

Closed 2 years ago

#12609 closed defect (fixed)

admin/ticket/milestones and Roadmap pages slow

Reported by: Jeffrey.Ratcliffe@… Owned by: Jun Omae
Priority: normal Milestone: 1.0.14
Component: admin/web Version: 1.0.13
Severity: normal Keywords: performance milestone
Cc:
Release Notes:

Improved performance of roadmap and milestone admin pages when many milestones and tickets exist.

API Changes:

Description

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?

Attachments (0)

Change History (13)

comment:1 Changed 2 years ago by Jun Omae

What database are you using?

comment:2 Changed 2 years ago by Jun Omae

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

Could you try the following patch?

  • trac/ticket/admin.py

    diff --git a/trac/ticket/admin.py b/trac/ticket/admin.py
    index acd9d4ec5..5072c6196 100644
    a b class MilestoneAdminPanel(TicketAdminPanel):  
    325325                        req.redirect(req.href.admin(cat, page))
    326326
    327327            # Get ticket count
    328             milestones = [
    329                 (milestone, self.env.db_query("""
    330                     SELECT COUNT(*) FROM ticket WHERE milestone=%s
    331                     """, (milestone.name,))[0][0])
    332                 for milestone in model.Milestone.select(self.env)]
     328            counts = dict(self.env.db_query("""
     329                    SELECT milestone, COUNT(milestone) FROM ticket
     330                    WHERE milestone != ''
     331                    GROUP BY milestone
     332                """))
     333            milestones = [(milestone, counts.get(milestone.name, 0))
     334                          for milestone in model.Milestone.select(self.env)]
    333335
    334336            data = {'view': 'list',
    335337                    'milestones': milestones,

comment:3 Changed 2 years ago by Jun Omae

Component: roadmapadmin/web
Keywords: performance milestone added
Milestone: 1.0.14

comment:4 Changed 2 years ago by 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?

comment:5 in reply to:  4 ; Changed 2 years ago by 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:

comment:6 Changed 2 years ago by Jeffrey.Ratcliffe@…

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

Can you do something similar for the roadmap page?

comment:7 Changed 2 years ago by Jeffrey.Ratcliffe@…

Thanks for the patch, btw.

I forgot to add that we are using SQLite.

comment:8 in reply to:  6 Changed 2 years ago by 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.

comment:9 Changed 2 years ago by Jun Omae

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

comment:10 Changed 2 years ago by Jeffrey.Ratcliffe@…

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

comment:11 in reply to:  10 Changed 2 years ago by Jun Omae

Owner: set to Jun Omae
Release Notes: modified (diff)
Status: newassigned

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.

Last edited 2 years ago by Jun Omae (previous) (diff)

comment:12 in reply to:  5 Changed 2 years ago by 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.

comment:13 Changed 2 years ago by Jun Omae

Resolution: fixed
Status: assignedclosed

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].

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.