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