-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 30.04.2014 11:27, Steffen Hoffmann wrote:
> OTOH I hope we could do better in core. A flexible db table view comes
> to my mind, that could refit automagically depending on custom fields
> configuration. But I'm not so much an db expert to decide, if that would
> really matter for performance.
The answer is No. :-(
I've been so curious about it, that I configured two reports on my
performance test SQLite Trac db with 30.000+ tickets for listing some
rows including two custom ticket fields.
report A (join)
SELECT
id AS ticket, summary, priority, owner, time as created,
tc0.value as due_date,
tc1.value as parent
FROM ticket t
LEFT JOIN ticket_custom tc0 ON t.id=tc0.ticket AND tc0.name='due_date'
LEFT JOIN ticket_custom tc1 ON t.id=tc1.ticket AND tc1.name='parent'
ORDER BY due_date
report B (view)
SELECT
id AS ticket, summary, priority, owner, time as created,
due_date,
parent
FROM ticket_view
ORDER BY due_date
The corresponding ticket_view db table VIEW had all ticket columns plus
all ticket_custom fields as columns readily available:
CREATE VIEW ticket_view AS
SELECT
id,type,time,changetime,component,severity,priority,owner,reporter,cc,
version,milestone,status,resolution,summary,description,keywords,
tc0.value as due_date,
tc1.value as parent
FROM ticket t
LEFT JOIN ticket_custom tc0 ON t.id=tc0.ticket
AND tc0.name='due_date'
LEFT JOIN ticket_custom tc1 ON t.id=tc1.ticket
AND tc1.name='parent'
Results of two consecutive profiling runs [1] indicate no difference in
processing time for requesting the report based on joined table versus
requesting
Test results (heavily truncated)
$> python ~/trac-req_report-join.py | grep sqlite
19 0.005 0.000 1.829 0.096
./trac/db/sqlite_backend.py:77(execute)
19 0.005 0.000 1.821 0.096
./trac/db/sqlite_backend.py:77(execute)
$> python ~/trac-req_report-view.py | grep sqlite
19 0.005 0.000 1.760 0.093
./trac/db/sqlite_backend.py:77(execute)
19 0.005 0.000 1.825 0.096
./trac/db/sqlite_backend.py:77(execute)
So the db backend took approximately 1.8 s to complete regardless of
using the view or both ticket related tables directly.
However comparing SQL queries there is certainly a benefit of the db
abstraction in making report writing easier on-top of ticket_view IMO.
Steffen Hoffmann
[1] https://www.coderesort.com/u/simon/blog/2011/09/06/trac_profiling
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Icedove - http://www.enigmail.net/
iEYEARECAAYFAlNg1bAACgkQ31DJeiZFuHflZACfSkW9AHRflYmhMhUxnv0oUuRt
BDkAnj+Oa+JjkS/8c6pQ4Xi+J6SNnwIU
=xwMr
-----END PGP SIGNATURE-----
--
You received this message because you are subscribed to the Google Groups "Trac
Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/trac-dev.
For more options, visit https://groups.google.com/d/optout.
