Lookup fields getting very slow ...

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Lookup fields getting very slow ...

Post by onoehring » 2021-02-10 13:58

Hi,

I have a problem and hope someone can suggest a solution.
In an AG application, I have a details view with several registers below to child-records (see screenshot below).
The details view has several lookup fields.
When I open the details view and click immediately on a lookup field ... it takes up to 13 seconds (!) for the list to show up (not local, but on a real webserver ... so let's say it takes ages).

Playing around I noticed, that the problem seem to be the child registers: Once they have been loaded (which I know because I am using Jans AG Helper to show the record count of these registers) the lookup fields are blazing fast again.
AG Helper code in header-extras.php:

Code: Select all

AppGiniHelper.Defaults.dv_childrenTabs_badges = true;
Short:
DV load -> access to lookup = slow
DV load, all child-pages (registers) are loaded -> access to lookup = fast

Does anyone have an idea how I can make the lookups of the detail view a priority (loading them before all child registers are loaded).

I remember that I once had disabled caching (by changing something in the AG files ... pbötcher suggested this). Thas "disable" is no more - but I am willing to try this. How can I disable caching ... for one lookup / for all lookups on that page? (EDIT: Found it: viewtopic.php?f=6&t=3495&p=12576&hilit= ... ing#p12576 )

I did try to disable the AG Helper code to show the badge count ... it did not help (in my test).
2021-02-10 14.48.37 localhost 36dfe3a06d6d.png
2021-02-10 14.48.37 localhost 36dfe3a06d6d.png (58.9 KiB) Viewed 3952 times
Thanks for all help
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup fields getting very slow ...

Post by onoehring » 2021-02-10 15:28

Hi,

I created a screencast to visualize the slowness. Please pay attention to the loading icon in the lookup I am opening. It does not even appear directly.
The gif was too large (560 KB) to be embedded, so I uploaded the screencast here (mp4, 290 KB): https://dl.olaf-noehring.de/?t=6d7b5c1d ... 239de317ef



Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Lookup fields getting very slow ...

Post by pbottcher » 2021-02-10 15:41

Hi Olaf,

did you check the network tab to see what is going on?
Are you using any calculated fields?
Is the dropdown fast after the Child table has finished loading?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup fields getting very slow ...

Post by onoehring » 2021-02-10 16:34

Hi pbötcher,

ad 1)
no, I did not check the network tab (and would have overlooked that without your input, thank you). I am attaching an image of that ... looks horrible but unfortunately I am not so sure what I can do about this. It just hardens my suggestion, that it has something to do with the child-records.

I also uploaded a loading screencast (testing locally, cleared cache before, mp4, 1,1 MB) here where you can see the network tab build up: https://dl.olaf-noehring.de/?t=cb23ea97 ... e48d95030e

All STATUS is 200, cut out the waterfall and squeezed columns:
l2.png
l2.png (67.62 KiB) Viewed 3927 times
ad 2)
no, I am not using any calculated fields.

ad 3)
yes, once the children are fully loaded there is almost no delay. I would say, the lookup then is blazing fast when you for example enter some value to narrow the list down

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup fields getting very slow ...

Post by onoehring » 2021-02-15 10:02

Hi,

I am just wondering, it it is possible to postpone loading of the child-tabs until they are being clicked on. This might help with the loading time of the regular elements.
So, can it be done, that the children are loaded only, when they are clicked on?
Well, I know, that the nice badges which show the record count of each child are not very useful then :-)

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

SOLVED (kind of): Lookup fields getting very slow ...

Post by onoehring » 2021-02-23 09:22

Hi,

as unfortunately noone seems to have an idea, I am posting the "intermediate" solution:
The problem seemed to be a register which displayed changes to the current record (Audit Log, viewtopic.php?f=4&t=1369&p=10407 ). As in the audit table are currently 250k records, it takes some time to grab the matching ones: I created a view that filters from the auditor table for the table we need and this view was further filtered by AG showing only matching records. In my first image, you can see the register with 347 records.

I removed the Audit from the child-registers and placed a button to the Audit above the details page.
Now, all lookups seem to be fast again.

Look at the current waterfall ... now we are talking (again):
Zwischenablage01.png
Zwischenablage01.png (142.35 KiB) Viewed 3736 times
Olaf

A Bindi
Veteran Member
Posts: 51
Joined: 2018-01-04 18:45

Re: Lookup fields getting very slow ...

Post by A Bindi » 2021-04-30 12:43

I also have this problem, all lookup fields became slow, extremely slow !
But in this theread honestly I didn not understand a bit... :(

ALex.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup fields getting very slow ...

Post by onoehring » 2021-05-01 07:54

Hi Alex,

are you using AG Helper to show the count of the children?
The thread is about things getting slow and why. For me the problem was, that in one child table there were about 250,000 (twohundred and fifty thousand) records that needed to be filtered to the parent record. My guess was, that counting those .. in the frontend (? by AG Helper .. @Jan, can you confirm this please) the calculatioin of that number was making the difference between fast and slow.
My solution was to remove that specific table from the child tabs (see image). Set the checkbox in the upper area, but remove the childtab from registers (set to No in lower area)
slow01.png
slow01.png (17.08 KiB) Viewed 3383 times
Maybe this works for as well? Let us know.
Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup fields getting very slow ...

Post by jsetzer » 2021-05-01 08:00

Sorry, @Olaf, I can not confirm this.

That part of AG Helper Javascript Library reiles on the values shown below the table which come from AppGini itself.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup fields getting very slow ...

Post by onoehring » 2021-05-01 10:14

Hi Jan,

thank you for the fast replay, but I probably did not make clear what I meant.
How does AG helper gets the record count? Does it intercept the number shown at the bottom of the child-tab (see image) or does it create it's own SQL query to get the data (select count ...)?
a1.png
a1.png (5.01 KiB) Viewed 3369 times
Or - is it just AppGini which has problems with speed, when there are many records? Do you know?

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup fields getting very slow ...

Post by jsetzer » 2021-05-01 10:45

As mentioned before: it just takes the numbers AppGini provides.

Olaf, a JavaScript function cannot fetch data using SQL from a serverside mySQL data without any interface.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply