Hello dear list, I’m trying to export a list of tickets directly from the OTRS DB - I’m computing 20 columns, there are 7 joins… the request is not that small…
And it takes approx. 50 seconds to generate 5’000 rows I need. Knowing that I
will eventually need to export much more than 5’000 rows, this is not
acceptable.
If I remove *only* the 4 columns where I compute the value of some “Dropdown”
dynamic fields linked to tickets, the times drops down to 3 seconds…
So I must be doing something wrong in the way I compute the value of dynamic
fields…. or maybe the design of the DB itself is suboptimal?
The best solution I’ve found so far is a poor man’s parser on the YAML content
of dynamic_field.content:
SELECT id,
(SELECT regexp_replace(config, '.*' || dynamic_field_value.value_text || ':
([^\n]+)\n.*', '\1')
FROM dynamic_field_value
JOIN dynamic_field ON dynamic_field.id = dynamic_field_value.field_id
WHERE dynamic_field_value.object_id = ticket.id
AND object_type = 'Ticket'
AND dynamic_field.name = 'Category') AS "Category"
FROM ticket
WHERE ticket.tn = '10479715'
Does anyone have a better idea?
Thanks,
Olivier
P.S. I’m using PostgreSQL - it would have been nicer to store the data in JSON
format, which is natively supported, it could have save some precious CPU
cycles but well...
signature.asc
Description: Message signed with OpenPGP using GPGMail
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
