improve speed of TicketQuery for format=table

[Christian Boos]

The format=table was added in 0.11 merely as a proof-of-concept to see how Genshi templates could be reused in different contexts, in this case within a WikiMacro.

That proved to work well, except for the performance side of things (see e.g. #10879).

A query like [[TicketQuery(status=closed,format=list)]] on this site (corresponding to 11320 tickets) would take several seconds to complete successfully, while [[TicketQuery(status=closed,format=table)]] fails after several (more) seconds with:

Error: Macro TicketQuery(status=closed,format=table) failed

and in the log we see:

[pid 9964 139956728231680] 2013-05-13 18:46:25,397 Trac[formatter] ERROR: Macro TicketQuery(status=closed,format=table) failed:
Traceback (most recent call last):
  File "/usr/local/virtualenv/1.1dev/lib/python2.6/site-packages/Trac-1.1.2dev_r11794-py2.6.egg/trac/wiki/formatter.py", line 765, in _macro_formatter
    return macro.ensure_inline(macro.process(args))
  File "/usr/local/virtualenv/1.1dev/lib/python2.6/site-packages/Trac-1.1.2dev_r11794-py2.6.egg/trac/wiki/formatter.py", line 380, in ensure_inline
    text = _markup_to_unicode(text)
  File "/usr/local/virtualenv/1.1dev/lib/python2.6/site-packages/Trac-1.1.2dev_r11794-py2.6.egg/trac/wiki/formatter.py", line 107, in _markup_to_unicode
    markup = stream.render('xhtml', encoding=None, strip_whitespace=False)
  File "/usr/local/virtualenv/1.1dev/lib/python2.6/site-packages/Genshi-0.7dev_r1134-py2.6.egg/genshi/core.py", line 183, in render
    return encode(generator, method=method, encoding=encoding, out=out)
  File "/usr/local/virtualenv/1.1dev/lib/python2.6/site-packages/Genshi-0.7dev_r1134-py2.6.egg/genshi/output.py", line 57, in encode
    return _encode(''.join(list(iterator)))
MemoryError:

I think we could approach this particular issue as a way to start a performance enhancement process which will lead to eliminate the Genshi bottleneck, by only generating the relevant data in JSON format (here result rows content), and have some client-side Javascript code for doing the actual rendering (here building the HTML table).

This presents a lot of implementation possibilities, but in a first step I believe simple jQuery based building should be good enough, there's probably no need of client-side templating or additional framework. I think that solving #10373 will also be a prerequisite.

Once it works for this use case, we could think about how to make the normal /query pages benefit from the same performance boost (getting rid of the query_results.html template).

Then, once we have a robust solution in place, gradually extend that to other "Genshi-intensive" places (diffs, source browser, timeline, etc.).

[Christian Boos]

The generalization step could actually correspond to #10635.

[Christian Boos]

The macro would return something like <div id="tq13579" class="trac-ticketquery-placeholder"/>, add javascript data under the same id, and add a javascript script which will replace all the div.trac-ticketquery-placeholder with HTML generated on the fly from the data. I think that using CoffeeScript for this task could be particularly convenient (multiline strings with Ruby-style embedded expressions).

[Christian Boos]

Well, without even changing the dynamics of how the macro would work, simply switching to Jinja2 would already help a lot.

[Christian Boos]

Today, before the upgrade: [[TicketQuery(status=closed,format=list)]] took 2.9s.

[Christian Boos]

After the upgrade, [[TicketQuery(status=closed,format=list)]] still seems to take about 2.35s. Not that good…

[Christian Boos]

Replying to Christian Boos:

After the upgrade, [[TicketQuery(status=closed,format=list)]] still seems to take about 2.35s. Not that good…

Oh, of course, format=list is not the point here and shouldn't be affected by the switch to Jinja2. The question was about format=table.

With [[TicketQuery(status=closed,max=5000,format=table)]], we have 3.21s, so this is the same time as with the list format for 10000 elements.

Unfortunately, trying with no max parameter, we get an out of memory exception:

Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/wiki/formatter.py", line 795, in _macro_formatter
    return macro.ensure_inline(macro.process(args))
  File "build/bdist.linux-x86_64/egg/trac/wiki/formatter.py", line 371, in process
    text = self.processor(text)
  File "build/bdist.linux-x86_64/egg/trac/wiki/formatter.py", line 343, in _macro_processor
    text)
  File "build/bdist.linux-x86_64/egg/trac/ticket/query.py", line 1383, in expand_macro
    data)
  File "build/bdist.linux-x86_64/egg/trac/web/chrome.py", line 1509, in render_fragment
    return self.render_template_string(template, data, text)
  File "build/bdist.linux-x86_64/egg/trac/web/chrome.py", line 1595, in render_template_string
    string = template.render(data)
  File "/usr/local/virtualenv/1.3dev/lib/python2.7/site-packages/jinja2/environment.py", line 1008, in render
    return self.environment.handle_exception(exc_info, True)
  File "/usr/local/virtualenv/1.3dev/lib/python2.7/site-packages/jinja2/environment.py", line 774, in handle_exception
    traceback = _make_traceback(exc_info, source_hint)
  File "/usr/local/virtualenv/1.3dev/lib/python2.7/site-packages/jinja2/debug.py", line 140, in make_traceback
    return translate_exception(exc_info, initial_skip)
  File "/usr/local/virtualenv/1.3dev/lib/python2.7/site-packages/jinja2/debug.py", line 193, in translate_exception
    reraise(exc_info[0], exc_info[1], exc_info[2])
  File "/usr/local/virtualenv/1.3dev/lib/python2.7/site-packages/jinja2/environment.py", line 1005, in render
    return concat(self.root_render_func(self.new_context(vars)))
MemoryError:

So no miracle with Jinja2 here, we have the same effect we had with Genshi. Probably less memory is used, but still too much.

I'm not sure we can do something about this, other than to allow for more memory usage on the server-side.

[Christian Boos]

Replying to Christian Boos:

I think we could approach this particular issue as a way to start a performance enhancement process which will lead to eliminate the Genshi bottleneck, by only generating the relevant data in JSON format (here result rows content), and have some client-side Javascript code for doing the actual rendering (here building the HTML table).

Or that ;-)

I'm leaving this ticket open for that approach.