Hops… answer to myself SELECT id, name, trim(regexp_replace(line, '.*:([^:]*)', '\1')) as value_text, trim(regexp_replace(line, '([^:]*):.*', '\1')) as display_name FROM (SELECT dynamic_field.id, dynamic_field.name, regexp_split_to_table(dynamic_field.config, '\n') AS line FROM dynamic_field WHERE object_type = 'Ticket' AND name = 'Category') AS foo WHERE line LIKE ' %’; -> will generate a table with: dynamic_field.id (to be linked with dynamic_field_value.field_id) dynamic_field.name value_text (to be linked with dynamic_field_value.value_text) display_name is the human-readable format of value_text This will allow to populate a temp. table before doing the main query and speed up the said main query. And now let’s hope that the structure in dynamic_field.config won’t change too often (this code is for OTRS 4)

On 10 Jun 2016, at 14:59, Olivier Macchioni wrote:

Hello Renée,

Thank you for your idea.

This is definitely an option - I could even create this table automatically at the beginning of my SQL query using WITH https://www.postgresql.org/docs/9.1/static/queries-with.html or something similar. Although doing this purely in SQL is not a super-exciting idea…

I’ll post something here if I can make this work.

Olivier

...

On 10 Jun 2016, at 14:32, Renee B mailto:otrs.list@perl-services.de> wrote:

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 http://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/ http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs http://lists.otrs.org/cgi-bin/listinfo/otrs

-- Perl / OTRS development: http://perl-services.de http://perl-services.de/ OTRS AddOn repository: http://opar.perl-services.de http://opar.perl-services.de/--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs http://lists.otrs.org/cgi-bin/listinfo/otrs

---

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