Page 1 of 1
Lag on lookup forms
Posted: Tue Oct 16, 2018 3:41 pm
by marcvander
Hello,
when I open a browse and edit form from the homepage with a button that runs the form, it loads and opens in less than 1 second (it has 8000 records).
When I open the same form, but just the browse with a lookup object on an edit form, it takes more than 3 seconds to load and show the 8000 records.
The SQL code is the exact same for both browse and edit and only browse forms. Here it is:
Code: Select all
SELECT *
FROM entreprise E
LEFT OUTER JOIN entreprise_statut ES
ON E.entreprise_statut = ES.statut_id
LEFT OUTER JOIN entreprise_etat EE
ON E.entreprise_etat = EE.etat_id
WHERE ((entreprise_fr = (SELECT employe_fr FROM employe WHERE employe_user='#USER_ID#') OR entreprise_ch = (SELECT employe_ch FROM employe WHERE employe_user='#USER_ID#')) OR (entreprise_fr=0 AND entreprise_ch=0))
Do you know where this lag comes from ?
Thanks
Re: Lag on lookup forms
Posted: Tue Oct 16, 2018 11:29 pm
by admin
marcvander,
Do you have indexes on the fields in the WHERE clause?
Steven
Re: Lag on lookup forms
Posted: Wed Oct 17, 2018 12:48 pm
by marcvander
Steven,
I just added indexes on all the fields in the WHERE clause:
Capture d’écran 2018-10-17 à 12.44.37.png
Capture d’écran 2018-10-17 à 12.44.56.png
But I still have the same long loading time when I click on the search icon on my lookup object.
It's really weird that with the same SQL query, it only happens on the lookup object, and not on the normal form.
Re: Lag on lookup forms
Posted: Wed Oct 17, 2018 12:58 pm
by marcvander
I went through the mysql and apache logs. Nothing found in mysql logs, in apache I found that which appears a lot:
[Wed Oct 17 12:02:25.196486 2018] [:error] [pid 29851] [client 188.61.23.20:50420] PHP Warning: in_array() expects parameter 2 to be array, null given in /var/www/erpversus/nudata.php on line 161, referer:
http://erp.versus-alternative.ch/index. ... e&iframe=1
[Wed Oct 17 11:54:08.358071 2018] [:error] [pid 29850] [client 188.61.23.20:50349] PHP Warning: in_array() expects parameter 2 to be array, null given in /var/www/erpversus/nudata.php on line 161, referer:
http://erp.versus-alternative.ch/index.php
Could it be this ?
Re: Lag on lookup forms
Posted: Wed Oct 17, 2018 1:07 pm
by marcvander
Also, I just tried to run the SQL query directly in phpmyadmin to check the execution time.
I ran this:
Code: Select all
SELECT *
FROM entreprise E
LEFT OUTER JOIN entreprise_statut ES
ON E.entreprise_statut = ES.statut_id
LEFT OUTER JOIN entreprise_etat EE
ON E.entreprise_etat = EE.etat_id
WHERE ((entreprise_fr = (SELECT employe_fr FROM employe WHERE employe_user='5a7ab72c4958f03') OR entreprise_ch = (SELECT employe_ch FROM employe WHERE employe_user='5a7ab72c4958f03')) OR (entreprise_fr=0 AND entreprise_ch=0))
As you can see I just replaced the #USER_ID# by an actual user id, and the execution time is 0,0047 sec. So it seems like the problem comes from the frontend. Steven did any other user already experienced a long loading time for a lookup object in the past?
Re: Lag on lookup forms
Posted: Wed Oct 17, 2018 11:43 pm
by admin
marcvander,
You can run this in the browser's console to see the SQL that is being run to create the Browse/Lookup.
Steven
Re: Lag on lookup forms
Posted: Thu Oct 18, 2018 12:58 pm
by marcvander
Steven,
I did so, and here is the SQL shown in the console:
Code: Select all
SELECT entreprise_id,entreprise_nom,entreprise_adresse,entreprise_cp,entreprise_ville,entreprise_pays,statut_nom,etat_nom
FROM entreprise E LEFT OUTER JOIN entreprise_statut ES ON E.entreprise_statut = ES.statut_id LEFT OUTER JOIN entreprise_etat EE ON E.entreprise_etat = EE.etat_id
WHERE ((entreprise_fr = (SELECT employe_fr FROM employe WHERE employe_user='5a7ab72c4958f03') OR entreprise_ch = (SELECT employe_ch FROM employe WHERE employe_user='5a7ab72c4958f03')) OR (entreprise_fr=0 AND entreprise_ch=0))
So it is the exact same SQL query that I ran for test in phpmyadmin. In phpmyadmin, It ran in 0,0047 sec. In nubuilder, it took more than 6 seconds to open after I clicked on the loop on the lookup object. I really don't know where it can come from.
Re: Lag on lookup forms
Posted: Fri Oct 19, 2018 3:54 pm
by marcvander
I have more info. A user told me that he has no lag when opening the form through the lookup object. I tried to log in with his account (he gave me his credentials), and I still had the lag. So I tried with another browser (Chrome and Safari), same lag on both. I assume that the problem comes from my laptop then. Now I'm trying in another place (with another wifi), and there it is faster. So it would be the internet connection. But the weird thing is that my internet connection when the lag appeared was not that slow, I downloaded effectively at 500Ko-1Mo/s. I checked the table size, it is 160Ko. So I still don't understand, but the problem doesn't come from nuBuilder ...
Anyway thanks for the help !
Re: Lag on lookup forms
Posted: Fri Oct 19, 2018 11:07 pm
by admin
marcvander,
That's nice to know - thanks for telling us.
Steven