Gateway timeout error - need some guidance please

[jeanl]

Hello

I have a Fabrik application that has been running well for quite some time. A seperate view was created that I use to create some reports. However, when I click on the menu link to go the view, I get this "Gateway timeout error" and after I refresh, I eventually get to the page.

I realise that the error is due to the entire DB is loading, before I apply any filters to generate my reports, so I need some help/guidance on how I can actually get to the view, but not pre-load all the data. I hope this makes sense? The data in the list goes back to 2015, and although the db is not massive, it does overload the mysql system as soon as I click on the menu link to open the view.

Thanks in advance.

Jean

 

[lousyfool]

There are several threads here on the topic of "making lists load faster" -- which may sound different but in fact the same recommendations there apply to your case as well, so no point in repeating them here.

If you need filtering anyway: have you tried setting "Require filtering" for your list to "yes"?

 

[jeanl]

There are several threads here on the topic of "making lists load faster" -- which may sound different but in fact the same recommendations there apply to your case as well, so no point in repeating them here.

If you need filtering anyway: have you tried setting "Require filtering" for your list to "yes"?

Thank you very much for the reply. I appreciate it. The "list" is actually a view created in MySQL so when you click on the menu link, it loads all the data before you have the opportunity to filter, or, this is how I understand it. If I select "Require Filtering" to "yes", it makes no difference.

 

[lousyfool]

Unless you have the Fabrik list set to display all rows and columns in a single page, it does not load "all the data". And with "Require filtering" set to yes, a Fabrik list is loading no data until after you selected a filter.

The MySQL view is generated on the fly, and from what you're saying it seems that this alone is eating server resources, at least sometimes more or for longer than possible, and actually already "before" Fabrik gets involved. Try loading the view in phpMyAdmin. I guess that's not going to go fast, either.

Unless you always need all the data from past 5 years at once in Fabrik, create multiple MySQL views instead of a single one, e.g. one per year or so.

 

[jeanl]

Unless you have the Fabrik list set to display all rows and columns in a single page, it does not load "all the data". And with "Require filtering" set to yes, a Fabrik list is loading no data until after you selected a filter.

The MySQL view is generated on the fly, and from what you're saying it seems that this alone is eating server resources, at least sometimes more or for longer than possible, and actually already "before" Fabrik gets involved. Try loading the view in phpMyAdmin. I guess that's not going to go fast, either.

Unless you always need all the data from past 5 years at once in Fabrik, create multiple MySQL views instead of a single one, e.g. one per year or so.

Is there any way to change the current SQL view to require filtering before simply showing all info?

 

[lousyfool]

As mentioned:

If you need filtering anyway: have you tried setting "Require filtering" for your list to "yes"?

 

[Nagtegaal]

What helped me was to use for filter on the element not a dropdown, but a field. Otherwise Fabrik is still loading all the data to create these filters. And indeed if you set the filter to required, the list immediately pops up without data.

Further, indexes on foreign keys (among others) can get your query up to speed.

 

[troester]

I think to display a dropdown filter for many, many value doesn't make much sense anyway.

But:
The "Filter list max" option in Fabrik Options/List should prevent exactly such unconstrained loading. Did you modify this setting (default is 100)?

 

[Nagtegaal]

Thanks Troester, well in my case, the dropdown list had only 5 records. Still it was loading a long time. My guess is that it loads the entire SQL query/View to determine the filter. With filter set to field it does not create a dropdown list and therefor much quicker

 

[jeanl]

Thank you for all the feedback. I managed to get it working, sort of, with the setting "Require Filter". However, I have a menu link to the View, and when you click on the menu link, it takes a while to get the page loaded that contains the view. But it is ok.

I do have another question regarding the view. If I want to remove one or two elements, I know I need to do it in phpMyadmin. Can someone just explain to me how I remove one or two elements without breaking anything?

 

[lousyfool]

Google "mysql edit view" and you'll get ways how to edit/alter the view.

Careful though... if I were you, I'd eventually create a new view based on the query for the existing one.

And -- needless to say -- if it's only about removing elements in the Fabrik list: you can disable them being displayed, and you can even exclude them from the list query or unpublish them.