List (< 300 rows, 13 elements, no join) is extremely slow

[michael.schwimmbeck]

Dear Sirs,

we are encoutnering massive performance problems with lists.
We have a single MySQL Table which we display as a fabrik list.
Both, joomla and fabrik are the latest versions.

We tried everything (prefiltering, filtering, removing grouping and ordering etc.) to increase the performance but nothing works.

When we run the query through joomla database api, the runtime is less then 3 seconds.
When we run the same query through fabrik, the runtime is around 12 - 15 seconds.

The mentioned list is used permanently and frequently by dozens of users.
Therefore 12 seconds are way to long.

Unfortunately I can't post as we are talking about an intranet.

Please help us to increase the lists performance.

Best regards,

Micahel

 

[troester]

Which element types are you using?
Are the 13 elements all your elements in this form or are 13 in list view (+ additional elements in the form)?
Can you post the list query?

 

[michael.schwimmbeck]

Thank you for your quick response,

we're using internalid, field, date and textarea elements.
We've already tried to disable the textarea element but it doesn't improve performance.

It seems that the poor performance is related to javascript as it runs 50% quicker on a more powerful machine.

I've found two statements which are both running very fast directly on MySQL :

SELECT DISTINCT `mawis_prod_orders`.`id` AS __pk_val0 FROM `mawis_prod_orders` ORDER BY `mawis_prod_orders`.`prod_start` ASC, `mawis_prod_orders`.`prod_end` ASC,`mawis_prod_orders`.`auftrag` ASC,`mawis_prod_orders`.`prod_end` ASC

SELECT DISTINCT `mawis_prod_orders`.`works_order_id` AS `mawis_prod_orders___works_order_id`, `mawis_prod_orders`.`works_order_id` AS `mawis_prod_orders___works_order_id_raw`, `mawis_prod_orders`.`project` AS `mawis_prod_orders___project`, `mawis_prod_orders`.`project` AS `mawis_prod_orders___project_raw`, `mawis_prod_orders`.`customer_id` AS `mawis_prod_orders___customer_id`, `mawis_prod_orders`.`customer_id` AS `mawis_prod_orders___customer_id_raw`, `mawis_prod_orders`.`art_nr` AS `mawis_prod_orders___art_nr`, `mawis_prod_orders`.`art_nr` AS `mawis_prod_orders___art_nr_raw`, `mawis_prod_orders`.`art_desc` AS `mawis_prod_orders___art_desc`, `mawis_prod_orders`.`art_desc` AS `mawis_prod_orders___art_desc_raw`, `mawis_prod_orders`.`date_time` AS `mawis_prod_orders___date_time`, `mawis_prod_orders`.`date_time` AS `mawis_prod_orders___date_time_raw`, `mawis_prod_orders`.`id` AS `mawis_prod_orders___id`, `mawis_prod_orders`.`id` AS `mawis_prod_orders___id_raw`, `mawis_prod_orders`.`duedate` AS `mawis_prod_orders___duedate`, `mawis_prod_orders`.`duedate` AS `mawis_prod_orders___duedate_raw`, `mawis_prod_orders`.`open_items` AS `mawis_prod_orders___open_items`, `mawis_prod_orders`.`open_items` AS `mawis_prod_orders___open_items_raw`, `mawis_prod_orders`.`amount` AS `mawis_prod_orders___amount`, `mawis_prod_orders`.`amount` AS `mawis_prod_orders___amount_raw`, `mawis_prod_orders`.`auftrag` AS `mawis_prod_orders___auftrag`, `mawis_prod_orders`.`auftrag` AS `mawis_prod_orders___auftrag_raw`, `mawis_prod_orders`.`prod_start` AS `mawis_prod_orders___prod_start`, `mawis_prod_orders`.`prod_start` AS `mawis_prod_orders___prod_start_raw`, `mawis_prod_orders`.`prod_end` AS `mawis_prod_orders___prod_end`, `mawis_prod_orders`.`prod_end` AS `mawis_prod_orders___prod_end_raw`, `mawis_prod_orders`.`id` AS slug , `mawis_prod_orders`.`id` AS `__pk_val` FROM `mawis_prod_orders` WHERE `mawis_prod_orders`.`id` IN ('916','992','993','1056','1058','1090','803','802','826','848','825','832','824','823','834','806','800','805','804','812','813','815','847','852','801','864','862','863','855','829') ORDER BY `mawis_prod_orders`.`prod_start` ASC, `mawis_prod_orders`.`prod_end` ASC,`mawis_prod_orders`.`auftrag` ASC,`mawis_prod_orders`.`prod_end` ASC

 

[cheesegrits]

Not much I can do to help without being able to see the page.

If you enable J! system debugging, you'll be able to see the profiling, which may give us a clue as to where the time is going.

-- hugh

 

[michael.schwimmbeck]

Dear Cheesegrits,

thank you four this hint. Please find attached a screenshot from the debug output (runtime).
Additionally I found that template loaded, end fabrik display, after render require more than 100 ms time.

Generally it seems to be the sum of all tasks which causes the long runtime of more than 12 seconds.
Please find attached the full list from runtime debugging.

 

[cheesegrits]

How many rows are you displaying?


Sent from my HTC One using Tapatalk

 

[michael.schwimmbeck]

At least 100.
Unfortunately we can't reduce the amount of rows as we need the overview.

 

[troester]

You can try to optimize:
there are a lot of onCanEdit, I assume some of your elements have "link to details" enabled?

 

[cheesegrits]

I'd have to get on the site to come up with any solutions.

Displaying 100 rows is going to be time consuming. That's just the nature of the beast, as we are having to render from "first principles", using generic metadata, basically building the application from scratch on each page load. I typically don't recommend rendering more than 20 or so rows at a time.

That said, it may be possible to speed things up.

-- hugh

 

[michael.schwimmbeck]

Thank you for yur proposal, but there is none element with "link to details" enabled.

What I see is that elements renderListData consumes very much time. I have reduced functionality of the table to a minimum.

 

[michael.schwimmbeck]

I could open a teamviewer session whenever you have time.

 

[cheesegrits]

Meh. No way to open up access for me?

-- hugh

 

[zzzz54]

Dear Sirs,

We tried everything (prefiltering, filtering, removing grouping and ordering etc.) to increase the performance but nothing works.

for those cases where I need to show many rows, I use plotalot (table view) wich render the list very fast within the content plugin (paid version 12 us$)... but you will don?t have all the functionality that comes with the fabrik list, like links, pagination, etc.

for the calculations (like sums at the bottom) you could create another plotalot table view without the columns labels.

another tool that you might need is sorttable.

note: I don?t have any relationship with those companys.

 

[michael.schwimmbeck]

Dear Cheesegrits,
sorry, i can only offer teamviewer.

I think we'll close this thread as i've started to program an own (kanban like) view in order to replace the table, so we have no solution but a workaround.

Thanks for your help guy's.