Mass Update is too slow

I’m trying to assign records through mass update and it’s taking almost forever to update.

100 records are taking 5 mins to update

using SuiteCRM Version 7.12.5

I’ve checked my schedulers are not effecting by turning them off.
Log slow queries also tried nothing found
No logic hook added to module

Please help!

Try running the first query in this post

this is to look for overgrown tables, a common cause of problems. Or insufficient indexing.

1 Like

Hey @pgr , Thanks for replying

Here’s is the query response, Please guide what should i do now

Which module was this happening with?

You have several problems there in the database that you need to solve. Not all of them are solved in the same way, there are variations.

Do you have the “Prune database” job set up to run periodically?

this is happening in leads, outbounds and listing module

No I’ve not setup the prune database job set because in my logic hooks i need the total count of data(deleted ones as well).

You might benefit from having additional memory devoted to your PH and your database. That would depend on your current settings (not just hardware, also php.ini settings).

But I am quite certain that you need a database clean-up. You should clear up all finished, non-recent jobs; clean up all orphaned records from security_groups and relationship tables (especially if related to leads).

You should also clean up deleted records. If you need statistic data of the past, you can gather it and then throw the records away, just keep the counts.

Thanks @pgr ,

Can you please elaborate this point , meanwhile i’ll do other points.

How much RAM memory does your server / VM / container have?

What is the value of your memory_limit in PHP? Please check this from within SuiteCRM, Admin / Diagnostic / phpinfo, to see the effective value (and the php.ini path, in case you want to change it).

Hey @pgr , Please Check

Plus: job_queue clear
should i clear my secuirtygroups_records table? will it impact on anything?

Memory seems ok.

You shouldn’t clear anything from the database without a lot of care, having full backups, and a full understanding of what the data means.

You can clear records with “deleted=1” that you know you won’t need.

You can also clear orphaned records, which are records related to other records that no longer exist (or exist, but have “deleted=1”). For example, a security_groups record of a Lead that you have deleted. Or a call related to a deleted Lead.

This is very common in SuiteCRM. To catch those, you need to devise clever SQL queries.

This might help:

And I have a few queries here in my notes, use at your own risk:

Delete orphaned records from contacts_cstm (you can make similar ones for other tables’ custom fields):

SELECT * -- DELETE ChildTable
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c =
SELECT record_id, module, s.deleted, c.last_name, c.deleted -- DELETE s
FROM securitygroups_records s    
LEFT JOIN contacts c 
ON s.record_id =
AND s.module='Contacts'  

(make also a simpler delete of rows with s.deleted=1, where the relationship itself was deleted, but the record_id still exists)

My queries above are SELECT queries, so you can try running them just to see which rows they select. Then you can edit the first line, which has the DELETE command after the comment marker “–”. So SELECT * -- DELETE ChildTable becomes DELETE ChildTable to really make it delete the rows.

Did I mention you have to have full backups? :slight_smile:

Thank You Man @pgr , I’ll do these & yes i do backup on daily basis.
Will let you know!