id,summary,reporter,owner,description,type,status,priority,component,severity,resolution,keywords,cc,release
13965,performance issue with extreme long waiting times,clemens,Ryan J Ollos,"The ""Changelog"" Plugin seem to be very slow with >100 changesets.

>>   This ticket was created for the corresponding [https://groups.google.com/g/trac-users/c/Q4XknMlc33c/m/xckYoClYAQAJ mailing list thread]

We recently installed the TracTicketChangelogPlugin on our system (TRAC 1.4.2, Linux, SQLite). It is very welcome because it gives a nice overview of all changesets related to a ticket.

However, my users complain about slow loading of tickets. Some tickets with 50-150 changesets have loading times >30 seconds. If I disable the TracTicketChangelogPlugin, then loading time is only 2 seconds. Even with endabled TracTicketChangelogPlugin, some tickets are quite fast (typically those with few changesets).

= Setup

Some specs about my setup:
- TRAC 1.4.2
- TracTicketChangelogPlugin 1.2 (r17251 by rjollos on 2018-07-30)
- Linux with TRAC running via apache Mod-WSQI
- SQLite database
- 500 tickets
- SVN with 5000 commits
- I have a powerful server with plenty of RAM, SSD and otherwise fast TRAC

= Config
I tried to shorten the message length, but this did not change the loading times:
{{{
[ticketlog]
log_message_maxlength = 50
}}}

= SQL

First I suspected that SQL database query might be the performance bottleneck. 

However, it seems SQL is not the problem (as far as I could find).

=== SQL experiment 1

I made an experiment to limit the number of change-log to 10. As expected I got only 10 change-logs in the output. Unfortunately this did not change the extreme long wait time (still >30 seconds for 150 changesets).

The SQL query is made in function `_get_ticket_revisions` in
https://trac-hacks.org/browser/tracticketchangelogplugin/1.2/ticketlog/web_ui.py#L187

{{{#!sql
    SELECT p.value, r.rev, r.author, r.time, r.message
    FROM ticket_revision AS tr
     LEFT JOIN revision AS r
      ON r.repos=tr.repos AND r.rev=tr.rev
     LEFT JOIN repository AS p
      ON p.id=tr.repos AND p.name='name'
    WHERE tr.ticket=%s
    LIMIT 10
}}}
Note the `LIMIT 10` in the last SQL line which I inserted in my experiment. The total wait time is not influenced by this ""LIMIT 10"".  

=== SQL experiment 2

Next I simply removed the SQL query from code. 
Instead I simply assigned fixed values to the variables. 

Result: It did not change the loading time.

= Genshi

As far as I know, the plugin is still based on Genshi (see #13283). 

Can this be a performance problem?

In my log I can see the following warning:
{{{
 Trac[chrome] WARNING: Component TicketLogModule relies on deprecated Genshi stream filtering
}}}

I experimented with the Python code and could find out that the following line 150 from `filter_stream` function may be responsible: 
source:/tracticketchangelogplugin/1.2/ticketlog/web_ui.py#L150
{{{#!python
stream |= Transformer('//div[@id=""ticket""]').after(template)
}}}

If I disable this line, then as expected the change-log will not appear in the HTML output, but the loading time will be quick.

= Tickets

What is confusing is that the loading time problem seems not proportional with the number of change-sets. I have tickets with 300 related SVN change-sets which load faster than other tickets with 150 related SVN change-sets. Also note that our ticket descriptions are often very 

I could not find the pattern, but it seems to be a combination of:
 - number of related SVN change-sets
 - number of ticket changes (my users often change ticket description)
 - length or complexity of ticket description (my users love long description texts with many tables and pictures)

Just to make this sure, __all__ tickets (even the longest ones) load quickly as soon as I disable the TracTicketChangelogPlugin.",defect,new,normal,TracTicketChangelogPlugin,normal,,,,1.4