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...