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
}}}

= Genshi
As far as I know, the plugin is still based on Genshi (see #13283). 
However, experiments indicate that this may not be the cause of the problem.

= SQL

For me it looks like the performance bottleneck is the SQL query not the Genshi template processing.

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

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"".  I am neither a database nor TRAC expert. But this SQL query looks rather simple. I cannot imagine that this takes 30 seconds. The `ticket_revision` table is short and has only 1000 rows on my repo. 

Please help, my TracTicketChangelogPlugin is still very slow. It takes over 30 seconds for some tickets. If I cannot find a solution I must disable this wonderful plugin.
 "	defect	new	normal	TracTicketChangelogPlugin	normal				1.4