Hi,
I would do it that way:
* Create a new table wingo_df_values (field_id, field_key, field_value)
with unique (field_id, field_key)
* change the backend module for Dropdown fields where the possible
values are stored to the new table (every time the dynamic field is changed)
* use that new table in the SQL query
- Renée
Am 10.06.2016 um 14:13 schrieb Olivier Macchioni:
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...
---------------------------------------------------------------------
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
--
Perl / OTRS development: http://perl-services.de
OTRS AddOn repository: http://opar.perl-services.de
---------------------------------------------------------------------
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
