Very long list loading times.

[pundip]

I have a list that is currently 17 records long and takes in excess of 8 seconds to load. The table has around 30 elements with six visible in list view. I will admit that most of them are database joins and cascading drop downs. J! debug is showing a query time of around 8ms so it does not look like a database issue.

The Profile Information debug section is attached but it looks like the culprit is this row:

Time: 8173.62 ms / 8281.40 ms Memory: 0.171 MB / 5.31 MB Application: runPlugins: start: onLoadedData

Obviously I have done something wrong but I am not sure what. I have always noticed that using database join elements and cascading dropdown will slow even a small list down so it is not related to just this install. I have however never seen such a slowdown with only 17 rows.

This system is currently behind company firewall so I am not able to grant you access but if needed I willing to replication this in a different site so you can look at it.

Thanks in advance.

 

[pundip]

Well this is awkward. I have managed to shave Application: runPlugins: start: onLoadedData down to 490ms by indexing the fields I was using for two database join elements. The issue was the fields I was using to populate the autofill box were not primary keys and were not indexed. One element was being populated from a table with 355 rows. Changing the join field used to data type INT and indexing it reduced the Application: runPlugins: start: onLoadedData time to 1600 ms from 8600.


While this solves my immediate problem I am interested in what else can be done to keep list load times as low as possible.

 

[cheesegrits]

Can you paste a screenshot of your join element configs? I'm wondering why we didn't automatically create indexes. We should create indexes on anything "built in" that needs them.

-- hugh

 

[troester]

Indexes and int FKs are essential.
I just run into it:
A list with 4 joins suddenly loading for about one minute (I think "suddenly" because of additional rows).
I found the parent_id of one repeat group varchar instead of int; it had an index assigned...; setting it to int solved the problem.

 

[pundip]

This was the main culprit. The billing_codes table has 355 rows with 5 fields. The ProjectCode field contained INT values but was stored as varchar(255) and was not indexed. The table was created as a list in fabrik and then populated via a csv file. I changing this to INT and indexing made a massive difference

 

[pundip]

This other culprit was the dropdowns table. This table contains values for a lot of the database join elements. The elements are still using the drop_down_value field which is a varchar(255) field. Indexing did shave around 1000ms of the load and this table populates around 10 database join elements. I am now changing the elements to use the indexed primary key which is INT.

 

[Sophist]

I found the parent_id of one repeat group varchar instead of int; it had an index assigned...; setting it to int solved the problem.

PR#1945 should fix this.

However there is a similar problem with dbJoin setting varchar for which I have not written a fix (yet). But it is in my Code Contributions Wish List (which is a sort of to-do list for PRs).