Products List and Stocks very slow

Using my Dolibarr PM modules
1

Products List and Stocks very slow to populate on all browsers. I have been looking on the Dolibarr forum as well as the web for months and have run out of options and am hoping someone can point me in the right direction.

Currently running v.10.0.3, but this problem has been the same on v9.0 prior to the upgrade to v10. When we upgraded from 8 to 9, no issues, but some time during the use of 9 we started having speed issues. We are hosted and installation is through Cpanel on a shared linux server.

Speed for all modules in Dolibarr is great, EXCEPT for when clicking on the left menu (list) or (stocks) under PRODUCTS. I will attach a screenshot, but it always takes about 8 to 12 seconds to display. I have tried turning off the 2 add on mods we have. I have deleted out the few Complementary attributes we have for products, no effect on speed. I have exported products and prices and looked for any illegal characters. I have run repair and optimize on the SQL database.

I have the added the optimize settings in main menu for NOT auto populating fields until a key is hit when searching for products in sales and purchase order entry and have no issue speed on product entry. There is a speed problem with sales orders slowing down in Sales Orders List as well when we have back orders on any orders. I would assume that this is related to grabbing information from the Products List.

I am hoping someone has an idea on what else I can do to fix this problem. On a side note, we currently only have just under 1700 products in our database.

products_slow_issue
products_slow_issue1702×434 35.4 KB

2

Can you provide a snippet of the apache and SQL logs for the duration of the pageload?

Have you tried clearing the web browser cache on the workstations? (and does that have any affect on the next or subsequent page loads)

3

Enable module syslog. Clear file documents/dolibarr.log. Then call the slow page. Then paste content of dolibarr.log file

4

Hi Eldy… Thank you for looking at this and any pointers you can give me to get this resolved would be greatly appreciated. :smiley:

I cleared the log file after enabling it, but the log file is over 600 lines and won’t let me paste here due to length restrictions; so I copied over to a HTML page with a link to download the dolibarr log file as well. I hope this will work, if not, let me know if I need to share it another way.

https://www.trimmill.com/logfile.html

5

With common version of dolibarr, you should have only few lines in this log whe’ you call the list of product. It appears here that you have a very very high number of sub requests coming from an external module done. This moduke seems to be called inventory and looks to not be designed to be have good performance. Can you try to disable it and check speed after ?

6

eldy… Here is the current log with all 3 mods turned off. I had to turn the display to 3 products because this log is very long otherwise. I still can’t seem to find what the problem is even after looking at the logs for the last few days. If you see anything that I can look at, I would sure appreciate any advise you can give me.

2019-11-28 15:59:59 DEBUG 71.47.180.66 Product::load_stock
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT ps.rowid, ps.reel, ps.fk_entrepot FROM db_product_stock as ps, db_entrepot as w WHERE w.entity IN (1) AND w.rowid = ps.fk_entrepot AND ps.fk_product = 844
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_customers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commandedet as cd, db_commande as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 844 AND c.fk_statut in (1,2)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT e.fk_soc) as nb_customers, COUNT(DISTINCT e.rowid) as nb, COUNT(ed.rowid) as nb_rows, SUM(ed.qty) as qty FROM db_expeditiondet as ed, db_commandedet as cd, db_commande as c, db_expedition as e, db_societe as s WHERE e.rowid = ed.fk_expedition AND c.rowid = cd.fk_commande AND e.fk_soc = s.rowid AND e.entity IN (1) AND ed.fk_origin_line = cd.rowid AND cd.fk_product = 844 AND c.fk_statut in (1,2)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_suppliers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commande_fournisseurdet as cd, db_commande_fournisseur as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 844 AND c.fk_statut in (1,2,3,4)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT cf.fk_soc) as nb_customers, COUNT(DISTINCT cf.rowid) as nb, COUNT(fd.rowid) as nb_rows, SUM(fd.qty) as qty FROM db_commande_fournisseur_dispatch as fd, db_commande_fournisseur as cf, db_societe as s WHERE cf.rowid = fd.fk_commande AND cf.fk_soc = s.rowid AND cf.entity IN (1) AND fd.fk_product = 844 AND cf.fk_statut in (4)
2019-11-28 15:59:59 INFO 71.47.180.66 files.lib.php::dol_dir_list path=/home/trimmill/public_html/dolibarr/documents/produit/acs.42874/ types=files recursive=0 filter= excludefilter="(\.meta|_preview.\.png)$"
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT rowid, label, entity, filename, filepath, fullpath_orig, keywords, cover, gen_or_uploaded, extraparams, date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM db_ecm_files WHERE filepath = ‘produit/acs.42874’ AND entity = 1
2019-11-28 15:59:59 DEBUG 71.47.180.66 ProductFournisseur::list_product_fournisseur_price
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT s.nom as supplier_name, s.rowid as fourn_id, pfp.rowid as product_fourn_pri_id, pfp.ref_fourn, pfp.desc_fourn, pfp.fk_product as product_fourn_id, pfp.fk_supplier_price_expression, pfp.price, pfp.quantity, pfp.unitprice, pfp.remise_percent, pfp.remise, pfp.tva_tx, pfp.fk_availability, pfp.charges, pfp.info_bits, pfp.delivery_time_days, pfp.supplier_reputation, pfp.multicurrency_price, pfp.multicurrency_unitprice, pfp.multicurrency_tx, pfp.fk_multicurrency, pfp.multicurrency_code, pfp.datec, pfp.tms, pfp.barcode, pfp.fk_barcode_type FROM db_product_fournisseur_price as pfp, db_societe as s WHERE pfp.entity IN (1) AND pfp.fk_soc = s.rowid AND s.status=1 AND pfp.fk_product = 844 ORDER BY s.nom, pfp.quantity, pfp.price
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT s.rowid, s.nom as name, s.name_alias, s.entity, s.ref_ext, s.ref_int, s.address, s.datec as date_creation, s.prefix_comm, s.status, s.price_level, s.tms as date_modification, s.fk_user_creat, s.fk_user_modif, s.phone, s.fax, s.email, s.skype, s.twitter, s.facebook, s.linkedin, s.url, s.zip, s.town, s.note_private, s.note_public, s.model_pdf, s.client, s.fournisseur, s.siren as idprof1, s.siret as idprof2, s.ape as idprof3, s.idprof4, s.idprof5, s.idprof6, s.capital, s.tva_intra, s.fk_typent as typent_id, s.fk_effectif as effectif_id, s.fk_forme_juridique as forme_juridique_code, s.webservices_url, s.webservices_key, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur, s.parent, s.barcode, s.fk_departement as state_id, s.fk_pays as country_id, s.fk_stcomm, s.remise_supplier, s.mode_reglement, s.cond_reglement, s.fk_account, s.tva_assuj, s.mode_reglement_supplier, s.cond_reglement_supplier, s.localtax1_assuj, s.localtax1_value, s.localtax2_assuj, s.localtax2_value, s.fk_prospectlevel, s.default_lang, s.logo, s.fk_shipping_method, s.outstanding_limit, s.import_key, s.canvas, s.fk_incoterms, s.location_incoterms, s.order_min_amount, s.supplier_order_min_amount, s.fk_multicurrency, s.multicurrency_code, fj.libelle as forme_juridique, e.libelle as effectif, c.code as country_code, c.label as country, d.code_departement as state_code, d.nom as state, st.libelle as stcomm, te.code as typent_code, i.libelle as libelle_incoterms, sr.remise_client FROM db_societe as s LEFT JOIN db_c_effectif as e ON s.fk_effectif = e.id LEFT JOIN db_c_country as c ON s.fk_pays = c.rowid LEFT JOIN db_c_stcomm as st ON s.fk_stcomm = st.id LEFT JOIN db_c_forme_juridique as fj ON s.fk_forme_juridique = fj.code LEFT JOIN db_c_departements as d ON s.fk_departement = d.rowid LEFT JOIN db_c_typent as te ON s.fk_typent = te.id LEFT JOIN db_c_incoterms as i ON s.fk_incoterms = i.rowid LEFT JOIN db_societe_remise as sr ON sr.rowid = (SELECT MAX(rowid) FROM db_societe_remise WHERE fk_soc = s.rowid AND entity = 1) WHERE s.entity IN (1) AND s.rowid = 17
2019-11-28 15:59:59 INFO 71.47.180.66 fetch_name_optionals_label elementtype=societe
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT rowid,name,label,type,size,elementtype,fieldunique,fieldrequired,param,pos,alwayseditable,perms,langs,list,totalizable,fielddefault,fieldcomputed,entity,enabled,help FROM db_extrafields WHERE elementtype = ‘societe’ ORDER BY pos
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT rowid, ffm FROM db_societe_extrafields WHERE fk_object = 17
2019-11-28 15:59:59 DEBUG 71.47.180.66 Product::load_stock
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT ps.rowid, ps.reel, ps.fk_entrepot FROM db_product_stock as ps, db_entrepot as w WHERE w.entity IN (1) AND w.rowid = ps.fk_entrepot AND ps.fk_product = 1049
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_customers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commandedet as cd, db_commande as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1049 AND c.fk_statut in (1,2)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT e.fk_soc) as nb_customers, COUNT(DISTINCT e.rowid) as nb, COUNT(ed.rowid) as nb_rows, SUM(ed.qty) as qty FROM db_expeditiondet as ed, db_commandedet as cd, db_commande as c, db_expedition as e, db_societe as s WHERE e.rowid = ed.fk_expedition AND c.rowid = cd.fk_commande AND e.fk_soc = s.rowid AND e.entity IN (1) AND ed.fk_origin_line = cd.rowid AND cd.fk_product = 1049 AND c.fk_statut in (1,2)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_suppliers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commande_fournisseurdet as cd, db_commande_fournisseur as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1049 AND c.fk_statut in (1,2,3,4)
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT cf.fk_soc) as nb_customers, COUNT(DISTINCT cf.rowid) as nb, COUNT(fd.rowid) as nb_rows, SUM(fd.qty) as qty FROM db_commande_fournisseur_dispatch as fd, db_commande_fournisseur as cf, db_societe as s WHERE cf.rowid = fd.fk_commande AND cf.fk_soc = s.rowid AND cf.entity IN (1) AND fd.fk_product = 1049 AND cf.fk_statut in (4)
2019-11-28 15:59:59 INFO 71.47.180.66 files.lib.php::dol_dir_list path=/home/trimmill/public_html/dolibarr/documents/produit/acs.42927/ types=files recursive=0 filter= excludefilter="(\.meta|_preview.\.png)$"
2019-11-28 15:59:59 DEBUG 71.47.180.66 sql=SELECT rowid, label, entity, filename, filepath, fullpath_orig, keywords, cover, gen_or_uploaded, extraparams, date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM db_ecm_files WHERE filepath = ‘produit/acs.42927’ AND entity = 1
2019-11-28 16:00:00 DEBUG 71.47.180.66 ProductFournisseur::list_product_fournisseur_price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.nom as supplier_name, s.rowid as fourn_id, pfp.rowid as product_fourn_pri_id, pfp.ref_fourn, pfp.desc_fourn, pfp.fk_product as product_fourn_id, pfp.fk_supplier_price_expression, pfp.price, pfp.quantity, pfp.unitprice, pfp.remise_percent, pfp.remise, pfp.tva_tx, pfp.fk_availability, pfp.charges, pfp.info_bits, pfp.delivery_time_days, pfp.supplier_reputation, pfp.multicurrency_price, pfp.multicurrency_unitprice, pfp.multicurrency_tx, pfp.fk_multicurrency, pfp.multicurrency_code, pfp.datec, pfp.tms, pfp.barcode, pfp.fk_barcode_type FROM db_product_fournisseur_price as pfp, db_societe as s WHERE pfp.entity IN (1) AND pfp.fk_soc = s.rowid AND s.status=1 AND pfp.fk_product = 1049 ORDER BY s.nom, pfp.quantity, pfp.price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.rowid, s.nom as name, s.name_alias, s.entity, s.ref_ext, s.ref_int, s.address, s.datec as date_creation, s.prefix_comm, s.status, s.price_level, s.tms as date_modification, s.fk_user_creat, s.fk_user_modif, s.phone, s.fax, s.email, s.skype, s.twitter, s.facebook, s.linkedin, s.url, s.zip, s.town, s.note_private, s.note_public, s.model_pdf, s.client, s.fournisseur, s.siren as idprof1, s.siret as idprof2, s.ape as idprof3, s.idprof4, s.idprof5, s.idprof6, s.capital, s.tva_intra, s.fk_typent as typent_id, s.fk_effectif as effectif_id, s.fk_forme_juridique as forme_juridique_code, s.webservices_url, s.webservices_key, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur, s.parent, s.barcode, s.fk_departement as state_id, s.fk_pays as country_id, s.fk_stcomm, s.remise_supplier, s.mode_reglement, s.cond_reglement, s.fk_account, s.tva_assuj, s.mode_reglement_supplier, s.cond_reglement_supplier, s.localtax1_assuj, s.localtax1_value, s.localtax2_assuj, s.localtax2_value, s.fk_prospectlevel, s.default_lang, s.logo, s.fk_shipping_method, s.outstanding_limit, s.import_key, s.canvas, s.fk_incoterms, s.location_incoterms, s.order_min_amount, s.supplier_order_min_amount, s.fk_multicurrency, s.multicurrency_code, fj.libelle as forme_juridique, e.libelle as effectif, c.code as country_code, c.label as country, d.code_departement as state_code, d.nom as state, st.libelle as stcomm, te.code as typent_code, i.libelle as libelle_incoterms, sr.remise_client FROM db_societe as s LEFT JOIN db_c_effectif as e ON s.fk_effectif = e.id LEFT JOIN db_c_country as c ON s.fk_pays = c.rowid LEFT JOIN db_c_stcomm as st ON s.fk_stcomm = st.id LEFT JOIN db_c_forme_juridique as fj ON s.fk_forme_juridique = fj.code LEFT JOIN db_c_departements as d ON s.fk_departement = d.rowid LEFT JOIN db_c_typent as te ON s.fk_typent = te.id LEFT JOIN db_c_incoterms as i ON s.fk_incoterms = i.rowid LEFT JOIN db_societe_remise as sr ON sr.rowid = (SELECT MAX(rowid) FROM db_societe_remise WHERE fk_soc = s.rowid AND entity = 1) WHERE s.entity IN (1) AND s.rowid = 17
2019-11-28 16:00:00 INFO 71.47.180.66 fetch_name_optionals_label elementtype=societe
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid,name,label,type,size,elementtype,fieldunique,fieldrequired,param,pos,alwayseditable,perms,langs,list,totalizable,fielddefault,fieldcomputed,entity,enabled,help FROM db_extrafields WHERE elementtype = ‘societe’ ORDER BY pos
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, ffm FROM db_societe_extrafields WHERE fk_object = 17
2019-11-28 16:00:00 DEBUG 71.47.180.66 Product::load_stock
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT ps.rowid, ps.reel, ps.fk_entrepot FROM db_product_stock as ps, db_entrepot as w WHERE w.entity IN (1) AND w.rowid = ps.fk_entrepot AND ps.fk_product = 2479
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_customers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commandedet as cd, db_commande as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 2479 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT e.fk_soc) as nb_customers, COUNT(DISTINCT e.rowid) as nb, COUNT(ed.rowid) as nb_rows, SUM(ed.qty) as qty FROM db_expeditiondet as ed, db_commandedet as cd, db_commande as c, db_expedition as e, db_societe as s WHERE e.rowid = ed.fk_expedition AND c.rowid = cd.fk_commande AND e.fk_soc = s.rowid AND e.entity IN (1) AND ed.fk_origin_line = cd.rowid AND cd.fk_product = 2479 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_suppliers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commande_fournisseurdet as cd, db_commande_fournisseur as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 2479 AND c.fk_statut in (1,2,3,4)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT cf.fk_soc) as nb_customers, COUNT(DISTINCT cf.rowid) as nb, COUNT(fd.rowid) as nb_rows, SUM(fd.qty) as qty FROM db_commande_fournisseur_dispatch as fd, db_commande_fournisseur as cf, db_societe as s WHERE cf.rowid = fd.fk_commande AND cf.fk_soc = s.rowid AND cf.entity IN (1) AND fd.fk_product = 2479 AND cf.fk_statut in (4)
2019-11-28 16:00:00 INFO 71.47.180.66 files.lib.php::dol_dir_list path=/home/trimmill/public_html/dolibarr/documents/produit/acs.9TM-1620CW/ types=files recursive=0 filter= excludefilter="(\.meta|_preview.\.png)$"
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, label, entity, filename, filepath, fullpath_orig, keywords, cover, gen_or_uploaded, extraparams, date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM db_ecm_files WHERE filepath = ‘produit/acs.9TM-1620CW’ AND entity = 1
2019-11-28 16:00:00 DEBUG 71.47.180.66 ProductFournisseur::list_product_fournisseur_price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.nom as supplier_name, s.rowid as fourn_id, pfp.rowid as product_fourn_pri_id, pfp.ref_fourn, pfp.desc_fourn, pfp.fk_product as product_fourn_id, pfp.fk_supplier_price_expression, pfp.price, pfp.quantity, pfp.unitprice, pfp.remise_percent, pfp.remise, pfp.tva_tx, pfp.fk_availability, pfp.charges, pfp.info_bits, pfp.delivery_time_days, pfp.supplier_reputation, pfp.multicurrency_price, pfp.multicurrency_unitprice, pfp.multicurrency_tx, pfp.fk_multicurrency, pfp.multicurrency_code, pfp.datec, pfp.tms, pfp.barcode, pfp.fk_barcode_type FROM db_product_fournisseur_price as pfp, db_societe as s WHERE pfp.entity IN (1) AND pfp.fk_soc = s.rowid AND s.status=1 AND pfp.fk_product = 2479 ORDER BY s.nom, pfp.quantity, pfp.price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.rowid, s.nom as name, s.name_alias, s.entity, s.ref_ext, s.ref_int, s.address, s.datec as date_creation, s.prefix_comm, s.status, s.price_level, s.tms as date_modification, s.fk_user_creat, s.fk_user_modif, s.phone, s.fax, s.email, s.skype, s.twitter, s.facebook, s.linkedin, s.url, s.zip, s.town, s.note_private, s.note_public, s.model_pdf, s.client, s.fournisseur, s.siren as idprof1, s.siret as idprof2, s.ape as idprof3, s.idprof4, s.idprof5, s.idprof6, s.capital, s.tva_intra, s.fk_typent as typent_id, s.fk_effectif as effectif_id, s.fk_forme_juridique as forme_juridique_code, s.webservices_url, s.webservices_key, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur, s.parent, s.barcode, s.fk_departement as state_id, s.fk_pays as country_id, s.fk_stcomm, s.remise_supplier, s.mode_reglement, s.cond_reglement, s.fk_account, s.tva_assuj, s.mode_reglement_supplier, s.cond_reglement_supplier, s.localtax1_assuj, s.localtax1_value, s.localtax2_assuj, s.localtax2_value, s.fk_prospectlevel, s.default_lang, s.logo, s.fk_shipping_method, s.outstanding_limit, s.import_key, s.canvas, s.fk_incoterms, s.location_incoterms, s.order_min_amount, s.supplier_order_min_amount, s.fk_multicurrency, s.multicurrency_code, fj.libelle as forme_juridique, e.libelle as effectif, c.code as country_code, c.label as country, d.code_departement as state_code, d.nom as state, st.libelle as stcomm, te.code as typent_code, i.libelle as libelle_incoterms, sr.remise_client FROM db_societe as s LEFT JOIN db_c_effectif as e ON s.fk_effectif = e.id LEFT JOIN db_c_country as c ON s.fk_pays = c.rowid LEFT JOIN db_c_stcomm as st ON s.fk_stcomm = st.id LEFT JOIN db_c_forme_juridique as fj ON s.fk_forme_juridique = fj.code LEFT JOIN db_c_departements as d ON s.fk_departement = d.rowid LEFT JOIN db_c_typent as te ON s.fk_typent = te.id LEFT JOIN db_c_incoterms as i ON s.fk_incoterms = i.rowid LEFT JOIN db_societe_remise as sr ON sr.rowid = (SELECT MAX(rowid) FROM db_societe_remise WHERE fk_soc = s.rowid AND entity = 1) WHERE s.entity IN (1) AND s.rowid = 111
2019-11-28 16:00:00 INFO 71.47.180.66 fetch_name_optionals_label elementtype=societe
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid,name,label,type,size,elementtype,fieldunique,fieldrequired,param,pos,alwayseditable,perms,langs,list,totalizable,fielddefault,fieldcomputed,entity,enabled,help FROM db_extrafields WHERE elementtype = ‘societe’ ORDER BY pos
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, ffm FROM db_societe_extrafields WHERE fk_object = 111
2019-11-28 16:00:00 DEBUG 71.47.180.66 Product::load_stock
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT ps.rowid, ps.reel, ps.fk_entrepot FROM db_product_stock as ps, db_entrepot as w WHERE w.entity IN (1) AND w.rowid = ps.fk_entrepot AND ps.fk_product = 1974
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_customers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commandedet as cd, db_commande as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1974 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT e.fk_soc) as nb_customers, COUNT(DISTINCT e.rowid) as nb, COUNT(ed.rowid) as nb_rows, SUM(ed.qty) as qty FROM db_expeditiondet as ed, db_commandedet as cd, db_commande as c, db_expedition as e, db_societe as s WHERE e.rowid = ed.fk_expedition AND c.rowid = cd.fk_commande AND e.fk_soc = s.rowid AND e.entity IN (1) AND ed.fk_origin_line = cd.rowid AND cd.fk_product = 1974 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_suppliers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commande_fournisseurdet as cd, db_commande_fournisseur as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1974 AND c.fk_statut in (1,2,3,4)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT cf.fk_soc) as nb_customers, COUNT(DISTINCT cf.rowid) as nb, COUNT(fd.rowid) as nb_rows, SUM(fd.qty) as qty FROM db_commande_fournisseur_dispatch as fd, db_commande_fournisseur as cf, db_societe as s WHERE cf.rowid = fd.fk_commande AND cf.fk_soc = s.rowid AND cf.entity IN (1) AND fd.fk_product = 1974 AND cf.fk_statut in (4)
2019-11-28 16:00:00 INFO 71.47.180.66 files.lib.php::dol_dir_list path=/home/trimmill/public_html/dolibarr/documents/produit/acs.ACC-KIT-4/ types=files recursive=0 filter= excludefilter="(\.meta|_preview.\.png)$"
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, label, entity, filename, filepath, fullpath_orig, keywords, cover, gen_or_uploaded, extraparams, date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM db_ecm_files WHERE filepath = ‘produit/acs.ACC-KIT-4’ AND entity = 1
2019-11-28 16:00:00 DEBUG 71.47.180.66 ProductFournisseur::list_product_fournisseur_price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.nom as supplier_name, s.rowid as fourn_id, pfp.rowid as product_fourn_pri_id, pfp.ref_fourn, pfp.desc_fourn, pfp.fk_product as product_fourn_id, pfp.fk_supplier_price_expression, pfp.price, pfp.quantity, pfp.unitprice, pfp.remise_percent, pfp.remise, pfp.tva_tx, pfp.fk_availability, pfp.charges, pfp.info_bits, pfp.delivery_time_days, pfp.supplier_reputation, pfp.multicurrency_price, pfp.multicurrency_unitprice, pfp.multicurrency_tx, pfp.fk_multicurrency, pfp.multicurrency_code, pfp.datec, pfp.tms, pfp.barcode, pfp.fk_barcode_type FROM db_product_fournisseur_price as pfp, db_societe as s WHERE pfp.entity IN (1) AND pfp.fk_soc = s.rowid AND s.status=1 AND pfp.fk_product = 1974 ORDER BY s.nom, pfp.quantity, pfp.price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.rowid, s.nom as name, s.name_alias, s.entity, s.ref_ext, s.ref_int, s.address, s.datec as date_creation, s.prefix_comm, s.status, s.price_level, s.tms as date_modification, s.fk_user_creat, s.fk_user_modif, s.phone, s.fax, s.email, s.skype, s.twitter, s.facebook, s.linkedin, s.url, s.zip, s.town, s.note_private, s.note_public, s.model_pdf, s.client, s.fournisseur, s.siren as idprof1, s.siret as idprof2, s.ape as idprof3, s.idprof4, s.idprof5, s.idprof6, s.capital, s.tva_intra, s.fk_typent as typent_id, s.fk_effectif as effectif_id, s.fk_forme_juridique as forme_juridique_code, s.webservices_url, s.webservices_key, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur, s.parent, s.barcode, s.fk_departement as state_id, s.fk_pays as country_id, s.fk_stcomm, s.remise_supplier, s.mode_reglement, s.cond_reglement, s.fk_account, s.tva_assuj, s.mode_reglement_supplier, s.cond_reglement_supplier, s.localtax1_assuj, s.localtax1_value, s.localtax2_assuj, s.localtax2_value, s.fk_prospectlevel, s.default_lang, s.logo, s.fk_shipping_method, s.outstanding_limit, s.import_key, s.canvas, s.fk_incoterms, s.location_incoterms, s.order_min_amount, s.supplier_order_min_amount, s.fk_multicurrency, s.multicurrency_code, fj.libelle as forme_juridique, e.libelle as effectif, c.code as country_code, c.label as country, d.code_departement as state_code, d.nom as state, st.libelle as stcomm, te.code as typent_code, i.libelle as libelle_incoterms, sr.remise_client FROM db_societe as s LEFT JOIN db_c_effectif as e ON s.fk_effectif = e.id LEFT JOIN db_c_country as c ON s.fk_pays = c.rowid LEFT JOIN db_c_stcomm as st ON s.fk_stcomm = st.id LEFT JOIN db_c_forme_juridique as fj ON s.fk_forme_juridique = fj.code LEFT JOIN db_c_departements as d ON s.fk_departement = d.rowid LEFT JOIN db_c_typent as te ON s.fk_typent = te.id LEFT JOIN db_c_incoterms as i ON s.fk_incoterms = i.rowid LEFT JOIN db_societe_remise as sr ON sr.rowid = (SELECT MAX(rowid) FROM db_societe_remise WHERE fk_soc = s.rowid AND entity = 1) WHERE s.entity IN (1) AND s.rowid = 17
2019-11-28 16:00:00 INFO 71.47.180.66 fetch_name_optionals_label elementtype=societe
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid,name,label,type,size,elementtype,fieldunique,fieldrequired,param,pos,alwayseditable,perms,langs,list,totalizable,fielddefault,fieldcomputed,entity,enabled,help FROM db_extrafields WHERE elementtype = ‘societe’ ORDER BY pos
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, ffm FROM db_societe_extrafields WHERE fk_object = 17
2019-11-28 16:00:00 DEBUG 71.47.180.66 Product::load_stock
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT ps.rowid, ps.reel, ps.fk_entrepot FROM db_product_stock as ps, db_entrepot as w WHERE w.entity IN (1) AND w.rowid = ps.fk_entrepot AND ps.fk_product = 1526
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_customers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commandedet as cd, db_commande as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1526 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT e.fk_soc) as nb_customers, COUNT(DISTINCT e.rowid) as nb, COUNT(ed.rowid) as nb_rows, SUM(ed.qty) as qty FROM db_expeditiondet as ed, db_commandedet as cd, db_commande as c, db_expedition as e, db_societe as s WHERE e.rowid = ed.fk_expedition AND c.rowid = cd.fk_commande AND e.fk_soc = s.rowid AND e.entity IN (1) AND ed.fk_origin_line = cd.rowid AND cd.fk_product = 1526 AND c.fk_statut in (1,2)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT c.fk_soc) as nb_suppliers, COUNT(DISTINCT c.rowid) as nb, COUNT(cd.rowid) as nb_rows, SUM(cd.qty) as qty FROM db_commande_fournisseurdet as cd, db_commande_fournisseur as c, db_societe as s WHERE c.rowid = cd.fk_commande AND c.fk_soc = s.rowid AND c.entity IN (1) AND cd.fk_product = 1526 AND c.fk_statut in (1,2,3,4)
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT COUNT(DISTINCT cf.fk_soc) as nb_customers, COUNT(DISTINCT cf.rowid) as nb, COUNT(fd.rowid) as nb_rows, SUM(fd.qty) as qty FROM db_commande_fournisseur_dispatch as fd, db_commande_fournisseur as cf, db_societe as s WHERE cf.rowid = fd.fk_commande AND cf.fk_soc = s.rowid AND cf.entity IN (1) AND fd.fk_product = 1526 AND cf.fk_statut in (4)
2019-11-28 16:00:00 INFO 71.47.180.66 files.lib.php::dol_dir_list path=/home/trimmill/public_html/dolibarr/documents/produit/acs.FD-1212-UW/ types=files recursive=0 filter= excludefilter="(\.meta|_preview.*\.png)$"
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT rowid, label, entity, filename, filepath, fullpath_orig, keywords, cover, gen_or_uploaded, extraparams, date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM db_ecm_files WHERE filepath = ‘produit/acs.FD-1212-UW’ AND entity = 1
2019-11-28 16:00:00 DEBUG 71.47.180.66 ProductFournisseur::list_product_fournisseur_price
2019-11-28 16:00:00 DEBUG 71.47.180.66 sql=SELECT s.nom as supplier_name, s.rowid as fourn_id, pfp.rowid as product_fourn_pri_id, pfp.ref_fourn, pfp.desc_fourn, pfp.fk_product as product_fourn_id, pfp.fk_supplier_price_expression, pfp.price, pfp.quantity, pfp.unitprice, pfp.remise_percent, pfp.remise, pfp.tva_tx, pfp.fk_availability, pfp.charges, pfp.info_bits, pfp.delivery_time_days, pfp.supplier_reputation, pfp.multicurrency_price, pfp.multicurrency_unitprice, pfp.multicurrency_tx, pfp.fk_multicurrency, pfp.multicurrency_code, pfp.datec, pfp.tms, pfp.barcode, pfp.fk_barcode_type FROM db_product_fournisseur_price as pfp, db_societe as s WHERE pfp.entity IN (1) AND pfp.fk_soc = s.rowid AND s.status=1 AND pfp.fk_product = 1526 ORDER BY s.nom, pfp.quantity, pfp.price
2019-11-28 16:00:00 INFO 71.47.180.66 — End access to /dolibarr/product/list.php

8

Can you upgrade with version 10.0.4 and mesure the time before (10.0.3) and after.
Some optimisation were done on this page and result may are interesting to know.

9

eldy…

I tried the update to 10.0.4, but not enough speed change to make a big difference. I did stumble across something that at least leads in the right direction. I was turning switches on and off for all modules and found out that when I turn OFF the “STOCKS” module, the Product List comes up quickly. There seems to be no change when I adjust switches inside the STOCKS module itself, so I am left to assume at this point that I have a problem somewhere with the data that the Stock module is reading. The changes it makes in the list when Stocks are activated are:
Virtual stock
Desired stock (we have no entries in this field currently)
Stock limit for alert (we have some entries in this field)
Physical stock (we have entries from purchase orders and inventory adjustments)

Not totally sure where I go from here to find out what is causing it. Maybe a bad inventory adjustment perhaps ??? Your thoughts… ???

stock_module
stock_module1673×822 91 KB

10

Can you try to keep the stock module on, with 10.0.4.
Then when you are on list of products, go on the hamburger icon to uncheck/remove the column “virtual stock”.
Is it better without the “virtual stock” column ?

11

That does help a little. Average times are now around 6 to 8 seconds from 8 to 12 prior.
(current Dolibarr version: 10.0.4 with the virtual column turned off in product list)

Do we have something wrong with how the virtual is working in our Dolibarr installation??

12

I don’t think there is something wrong with your setup.

It’s just that Dolibarr need to make a very high number of operation to calculate dynamic stock. However, 8 seconds, if column is not show, is still a lot.

To investigate more and be able to find which optimisation we can do, for your case, is it possible that you send me, in private, a zip of a mysqldump of your base to me (eldy@destailleur.fr) ?

13

Sent. :+1: You should receive via (pcworxstech@gmail.com)

14

I loaded your dump file. So i have the same data, the same setup and same version (tested with 10.0.4).
And the page need 0.15 second to show, even when showing all columns of table. And the log show that action done are corrects.
Can you confirm me what is the limit you have in top right for number of lines when you ask the list of products ? Do you have 25 when you got 8 seconds, as into the following screenshot ?
image

1 Like
15

Yes, I have the same setting of 25 items.

Since you are having no issues with it working on your side, I think I am going to try doing a test into a manual Dolibarr install, import the SQL dump and NOT use the INSTALLATRON Application Installer. Maybe something is wrong with the files being used during install/update on their side.

16

eldy,

The problem was with our hosting provider in the way the hosting is configured. They had a file in which data is moved during processing information from the database called I/O USAGE. This is set to 1MB and was causing a fault when we had to many people accessing data from the Dolibarr database. The fewer people we had logged on, the less often it would fault. They do have other plans that offer a larger I/O USAGE size, but there are limits. I also had upgraded to v11 thinking this may help, but made no difference on this hosting provider. I have attached some screen shots of a 4 hour session log that shows what was happening, which would freeze Dolibarr for everyone at various times.

So I solved our problem by setting up a Ubuntu server here locally in our office and it is running very fast and with no lag even with the Products List. (even with Virtual active on screen). Response time is milliseconds for all users. :slight_smile:

  • Ubuntu 18.04 LTS
  • Dolibarr 11.0.3
  • PHP 7.2
  • MySQL 10.1.44-MariaDB

Thank You always for your help on this issue. Really enjoying the v11 Dolibarr on our own server!

(Old stat files attached below)

I-O_status
I-O_status399×777 23.2 KB

faults_4hour
faults_4hour684×838 76.4 KB

1 Like