i experience Performance Problems using a multibox object. whenever i click on a filter it takes very Long to open (30-90 seconds) Inside the multibox, there are 20 different fields from a customer table (Dimension table with 90 Million data rows). All the values for the fields are strings and are not indexed and joined to other dimensional tables. For the sorting i used match to sort as i needed
load field1,
field2, // string
field3, // string
match(field3,'A','B') as field3_sort, // then i use this field to sort in multibox. These value fields appear almost 90 Million times, even though i have less than 10 values per field
etc
from table;
is it better to create an index for every field i Need in the customer table and then create an inline table for each field with the values and the sorting index, so that the multibox reads from a much smaller table? or do you know what is the reason for this huge hit in Performance in the multibox?
Firstly you should look if you really needs all 20 fields, then are there high-cardinality fields in this table, for example a row-id or that date-entries are time-stamps or names/adresses which could be splitted in parts - this will increase the number of fields but the amount of neccessary RAM will be significant reduced.
As next step I would remove the sorting and look at this effect and if the difference is huge then searching for alternatively approaches, sorting per (other) expressions in gui, linked-sorting-table, sorting within the script - so that you could use load-order or .....
Further you could try to use more as one multibox or combinations from multiboxes and listboxes perhaps in a containerbox or with conditions on visibilities or ....
I have cross checked with the user, apparently they Need all the fields, what i doubt. In the list box there are no timestamps, and the values have low cardinality.
I took off the sorting and it was a Little better and start trying with sorting tables. this will be an Option and on the other side, i suggested to take off some fields and test the Performance.
Is it possible that low Performance objects in the same tab have somehow an effect on the multibox filter? I have very low Performance text box objects and funnels, with alternate states in parameterized set expressions. Is this any causality on this? i am working an improving the Performance of These objects
