Lag on lookup forms

[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

[admin]

marcvander,

Do you have indexes on the fields in the WHERE clause?

Steven

[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.

[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 ?

[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?

[admin]

marcvander,

You can run this in the browser's console to see the SQL that is being run to create the Browse/Lookup.

Code: Select all

nuSERVERRESPONSE.browse_sql

Steven

[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.

[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 !

[admin]

marcvander,

That's nice to know - thanks for telling us.


Steven