Performance related data in a big list

[paolo.tateo]

Hello Rob and Hugh, I'm back
thanks to Fabrik (Joomla 2.5.x - Fabrik 3.0.5.2) I built a large system (the biggest of my life), with many lists (more than 100), and many records to list (some tables have more than 6 million of records). The system is multi-user (each user sees only their own records through the prefilter).

The products list now contains 530,000 records, and some users should be able to see about 50,000 records.
This list is connected to 4 other lists (prices, attributes, alternatives, categories) that I can see in the list as related data.

When the products are not many no problem, but when they become many (if only for 500) the loading time increases much because it is queried (analyzed with fabrikdebug), which first extracts all the ids of rows affected (500,. but sometimes .. 20,000), and then put these into a state id WHERE id IN (list) in a second query (list GetData: Products).

WHERE `app_products`.`id` IN ('827306','832040','832052','832059','832060','832061','832062','832143','832144','832145','832146','833091','833092','833093','833094','833095','833112','833113','833114','833115','833117','833118','833119','833120','833196','833197','833198','833199','833200','833201','833202','833203','833204','833205','833206','833207','833208','833209','838653','838654','840289','840290','840291','840292','840293','840355','840356','840357','842755','842756','842757','843519','843520','843521','843522','843523','843524','868548','868549','868550','868551','868552','868553','868554','868555','868556','868557','868558','868559','868560','868561','868562','868563','868564','868808','868809','868810','868811','868812','868813','868814','868815','868816','868817','868818','868819','868820','868821','868822','868823','868824','868825','868826','868827','868828') AND `app_products`.`id` IN ('827306','832040','832052','832059','832060','832061','832062','832143','832144','832145') ORDER BY `app_products`.`id` ASC

Can I configure any differently Fabrik to avoid this? After Fabrik 3.0.5.2 (which I have not updated because the system is huge and I have to plan and test this with calm migration) there are improvements on this?

Thanks

Paolo

 

[cheesegrits]

Hey Paolo! Welcome back.

Typically that query is where we are handling merged row data, when you have repeat joins, set to merge for display in the list, rather than having spearate rows for each repeat (i.e. multiple left joined rows).

Can you confirm this by turning off the merge feature (at the bottom of the "joins" section, under the lists "data" tab).

Although this feature should be entirely safe to turn on and off, as usual you should probably do any experimenting on a sandbox (like an Akeeba clone of the site), rather than the live site.

We have made quite a few improvements to database efficiency and speed since 3.0.5, but I'm not sure that this particular feature would be any quicker. We still have to first collect all the PK values of the main table (well, not actually "all", but a lot!) then restrict the joined row selection to just those, in order to handle pagination of merged joined data.

-- hugh

 

[paolo.tateo]

Hello Hugh, I solved it.
For some strange reason a field that it had to be a numeric field was a varchar.

Now I'm going to make a new hard question!

 

[cheesegrits]

Glad you sorted it out.

-- hugh