Error. Query limit reached

fab · 28 June 2022 10:04

Hello everyone, as per the subject, the problem is the query limit. the solution I have always found by reading on the net is to increase the limit in the config, but this is already the second time I have increased it and it does not seem normal. Of course, going forward the records increase and sooner or later the new limit will also be touched. Is there no definitive solution? I think that setting the limit to 0 only serves to circumvent the problem, because the more records are stored, the more the system will slow down.
I have also set slow queries to be shown in the log and there are a couple that appear, but I don’t know where they are and how I can reach them.
Can anyone give me any advice?

How can I resolve the query limit error (if there are any other than the one I have indicated)
How can I improve slow queries to speed up entries? How do I track them down? Thank you

pgr · 28 June 2022 10:06

Run the first query in this post, that’s usually the quick way to spot the problems in your database:

fab · 28 June 2022 10:09

Yes, I had already read this post, I do not have direct access to the DB in question, but I tried it locally with phpMyadmin and in any case I would not know how to interpret the results

pgr · 28 June 2022 10:17

Yes, normally I do it from phpMyAdmin also. You can post the results here. But the general idea is that sometimes a couple of tables stand out as really overgrown - you sense that their size does not reflect anything useful they’re doing on your behalf

And post your slow queries here also, I can try to help you find them

fab · 28 June 2022 10:54

First of all I will tell you that the environment is as follows:
Version 7.10.20
Sugar Version 6.5.25 (Version 344)

This is the result of the query, but bear in mind that it only concerns a part of the data that is actually on the DB… this is the ‘snapshot’ of the local DB:

suitecrm8.job_queue	0.114M	0.109G	0.040G	0.149G	0.370
suitecrm.job_queue	0.108M	0.104G	0.040G	0.144G	0.387
suitecrm.cons_giorn_audit	0.133M	0.018G	0.018G	0.036G	1.003
suitecrm.ptn_qr_dif	0.097M	0.026G	0.000G	0.026G	0.000
pt_db.aow_processed	0.027M	0.007G	0.011G	0.019G	1.538
suitecrm.aow_processed	0.027M	0.007G	0.011G	0.019G	1.538
suitecrm.aow_processed_aow_actions	0.027M	0.004G	0.012G	0.016G	2.692
pt_db.aow_processed_aow_actions	0.026M	0.004G	0.011G	0.015G	2.467
suitecrm.emails_text	0.000M	0.013G	0.000G	0.013G	0.001
suitecrm8.emails_text	0.000M	0.013G	0.000G	0.013G	0.001

Whereas this is a query that is slow:

SELECT  p_att_giorn.* ,
		p_att_giorn_cstm.p_att_ver_c,
		p_att_giorn_cstm.p_can_id_c,
		p_att_giorn_cstm.p_cod_tec_c,
		p_att_giorn_cstm.p_cogn_tec_c,
		p_att_giorn_cstm.p_com_id_c,
		p_att_giorn_cstm.p_dis_c,
		p_att_giorn_cstm.p_nom_tec_c,
		p_att_giorn_cstm.p_ora_f_c,
		p_att_giorn_cstm.p_ora_i_c,
		p_att_giorn_cstm.p_check_c,
		p_att_giorn_cstm.p_no_c,
		p_att_giorn_cstm.p_ok_c,
		p_att_giorn_cstm.p_sito_c,
		p_att_giorn_cstm.p_tipo_c,
		p_att_giorn_cstm.dif_ore_cont_c,
		p_att_giorn_cstm.dif_pz_cont_c,
		p_att_giorn_cstm.d_ptn_c,
		p_att_giorn_cstm.dom_d_c,
		p_att_giorn_cstm.dom_n_c,
		p_att_giorn_cstm.dom_c,
		p_att_giorn_cstm.fest_d_c,
		p_att_giorn_cstm.fest_n_c,
		p_att_giorn_cstm.fest_c,
		p_att_giorn_cstm.giorno_c,
		p_att_giorn_cstm.magg_c,
		p_att_giorn_cstm.nott_c,
		p_att_giorn_cstm.n_settimana_c,
		p_att_giorn_cstm.ore_g_c,
		p_att_giorn_cstm.ore_s_c,
		p_att_giorn_cstm.publ_port_c,
		p_att_giorn_cstm.t_ore_con_c,
		p_att_giorn_cstm.t_ore_p_c,
		p_att_giorn_cstm.tip_com_c,
		p_att_giorn_cstm.tip_fat_c,
		p_att_giorn_cstm.t_cic_c,
		p_att_giorn_cstm.t_cic_con_c,
		p_att_giorn_cstm.user_id1_c,
		p_att_giorn_cstm.user_id_c , 
		jt0.user_name modified_by_name , 
		jt0.created_by modified_by_name_owner  , 
		'Users' modified_by_name_mod , 
		jt1.user_name created_by_name , 
		jt1.created_by created_by_name_owner  , 
		'Users' created_by_name_mod , 
		jt2.user_name assigned_user_name , 
		jt2.created_by assigned_user_name_owner  , 
		'Users' assigned_user_name_mod, '                                                                                                                                                                                                                                   
																																											
		' accounts_p_att_giorn_1_name , ' 
	    ' accounts_p_att_giorn_1accounts_ida , ' 																																																											  
		' p_com_p_att_giorn_1_name , '                                   
		' p_com_p_att_giorn_1p_com_ida , '                                                                                                                                                                                                                                                              
		' p_com_p_att_giorn_2_name , '                                   
		' p_com_p_att_giorn_2p_com_ida , '                                                                                                                                                                                                                                                              
		' p_cons_giorn_p_att_giorn_1_name , '                                    
		' p_cons9d43rn_ida , '                                                                                                                                                                                                                                                              
		' p_lav_p_att_giorn_1_name , '                             
		' p_lav_p_att_giorn_1p_lav_ida , '                                                                                                                                                                                                                                                              
		' p_rap_gior_p_att_giorn_1_name , '   
		' p_rap4e0rn_ida , '                                                                                                                                                                                                                                                              
		' p_ide_p_att_giorn_1_name , '                              
		' p_ide8227vi_ida , '                                                                                                                                                                                                                                                              
		' p_plant_p_att_giorn_1_name , '                                   
		' p_plant_p_att_giorn_1p_plant_ida , '                                                                                                                                                                                                                                                              
		' p_plant_y_p_att_giorn_1_name , '                                   
		' p_plant_y_p_att_giorn_1p_plant_y_ida , 
LTRIM(RTRIM(CONCAT(IFNULL(jt12.first_name,''),' ',IFNULL(jt12.last_name,'')))) com_rif_c , jt13.name com_prec_c , 
LTRIM(RTRIM(CONCAT(IFNULL(jt14.first_name,''),' ',IFNULL(jt14.last_name,'')))) tec_rif_c  

FROM p_att_giorn  

LEFT JOIN p_att_giorn_cstm ON p_att_giorn.id = p_att_giorn_cstm.id_c   
LEFT JOIN  users jt0 ON p_att_giorn.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON p_att_giorn.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON p_att_giorn.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0 LEFT JOIN users jt12 ON p_att_giorn_cstm.user_id_c = jt12.id AND jt12.deleted=0  
 LEFT JOIN p_com jt13 ON p_att_giorn_cstm.p_com_id_c = jt13.id AND jt13.deleted=0  
 LEFT JOIN users jt14 ON p_att_giorn_cstm.user_id1_c = jt14.id AND jt14.deleted=0  
 
 where (p_att_giorn.deleted = 0 AND 

						p_att_giorn_cstm.p_can_id_c='15095') AND 
						p_att_giorn.deleted=0 ORDER BY p_att_giorn.id

pgr · 28 June 2022 11:06

The phpMyAdmin query selects the 10 largest tables in the DB. Your results seem fairly normal to me.

About your own slow query, you could try creating some index that would help. I’m not too good with that…

You could also try moving all the cstm data into the same table. No point in having two tables if everything is custom (custom module + custom fields in the custom module…). This can be a bit trickier but it might be worth a try. You’d have to manipulate vardefs and metadata files.