Page 1 of 1
lookup fields performance
Posted: Fri Mar 10, 2023 12:25 am
by Andros
In my app I have many tables which work ok (mysql+IIS)
One table has 208 fields, most of them are listbox or radio with lookup tables.
When I add a new record, it tooks a lot of time to complete the ajax loading, and sometimes some selects are not filled, without any console error.
Any experience or best practice on managing this kind of scenario?
It seems to me that the mysql performance and server performance are ok, maybe I have to manage some IIS or ASP.NET parameters?
Re: app performance problem
Posted: Fri Mar 10, 2023 7:05 am
by MichaelG
You can enable Lookup Cache for the page. For example in the Page_Load server event, add:
UseLookupCache = true;
Re: app performance problem
Posted: Fri Mar 10, 2023 3:55 pm
by Andros
Yet done, it's still very slow
Re: lookup fields performance
Posted: Fri Mar 10, 2023 7:03 pm
by MichaelG
Re: lookup fields performance
Posted: Fri Mar 10, 2023 9:21 pm
by darkdragon
Question, each listbox/radio field has it's own view for the list of values?
Re: lookup fields performance
Posted: Sat Mar 11, 2023 12:29 am
by Andros
Some fields have the same lookup tables.
Re: lookup fields performance
Posted: Sat Mar 11, 2023 6:44 am
by MichaelG
You can enable Lookup Cache for the page. For example in the Page_Load server event, add:
UseLookupCache = true;
Change also the Lookup Cache count if the fields has more than 100 options:
LookupCacheCount = 100; // *** Increase the lookup cache count here
Re: lookup fields performance
Posted: Fri Mar 17, 2023 11:22 pm
by Andros
I investigated further.
The problem is in /api/lookup
Many lookups work well, until one (and the following ones) fails with 500 error code "Unable to connect to any of the specified MySQL hosts"
It seems that MySQL is receiving a lot of calls thru api/lookup and at a certain point it closes the connection.
Checking the browser "network" data, there are 155 requests when I click on the plus icon to add a new record. Most of these are api/lookup and some of them abort.
Any idea to solve this?
Re: lookup fields performance
Posted: Fri Mar 17, 2023 11:32 pm
by MichaelG
Andros wrote:
... when I click on the plus icon to add a new record. Most of these are api/lookup...
Try enable Lookup cache enabled pages for the Add and Edit pages.