The detail view takes a good few seconds in the order 15-20 seconds to load when the cases subpanel is enables. When we hide the subpanel under Admin -> Display Modules and Subpanels, the detail view now loads near instantly.
We have a large number of cases and notes associated.
Investigating the log files by isolating to just one use and one record it appears that when the cases subpanel is enabled there are a very large number of queries in the form
Query:SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'
Query Execution Time:0.0037789344787598
Query:SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '10d508d0-58dc-dfb6-64dc-5ad0b87de780' AND notes.deleted=0 LIMIT 0,1
Query Execution Time:0.00026106834411621
Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'
Query Execution Time:0.00019383430480957
There are more queries and they go to a very large number, none of these queries are executed when the subpanel is hidden.
Whats going on here, how is notes associated to the view of the subpanel for cases??
Edit 1:
It appears that the following pattern of query is repeated for possibly every note we have define
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '77590914-e371-3b6b-be92-5ad0b78a2c1c' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00022315979003906
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='ed6b0688-54bf-3769-bc06-5ad0aab95e73'
[INFO] Query Execution Time:0.00015878677368164
[INFO] Query:SELECT ea.email_address FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND ear.deleted = 0 AND ea.invalid_email = 0 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:0.0002601146697998
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c WHERE contacts.id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026893615722656
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name from contacts left join accounts_contacts a_c on a_c.contact_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' and a_c.deleted=0 left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id where contacts.id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:0.00038790702819824
[INFO] Query:SELECT user_id id FROM contacts_users WHERE contacts_users.contact_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND contacts_users.deleted=0
[INFO] Query Execution Time:0.00016093254089355
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id FROM cm1_department_contacts_1_c WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:0.00016093254089355
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:0.00025796890258789
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='6b8d8c3d-f257-d14a-ce5d-5ad0a28a4f69'
[INFO] Query Execution Time:4.0054321289062E-5
You probably could use some database maintenance, and clearing up unused data.
A few questions:
Which version of SuiteCRM is this?
Do you have the Prune database Scheduler job enabled? When did it “last ran successfully”?
Which columns do you have in the subpanel, exactly? You seem to be querying 3 other modules (Contacts, Accounts, Email addresses, and a custom cm1_department_contacts_1_c).
What are your results when running the query at the top of this post?
We migrated from an in house setup, we unfortunately need to maintain 15+ years of data. There are only a few deleted records as the ability to delete is significantly restricted.
We are now at 7.10.10
No, this is disabled.
The cases subpanel displays the following information.
| Case Num. | Subject | Account Name | Status | Date Created | Assigned User |
The first returns a list of note ids that contains 1057 rows.
The second return a single note for the first result from (1)
The third returns the contact information of the note’s contact
This is followed by email address information of the contact
This is followed by contact account information
This is followed by contact account user id (i am assuming portal id) as this is always an Empty set
This is followed by contact’s department (this is a custom module) relation
If department is not empty, retrieve departments related account information
If department is not empty, retrieve related account email address
Retrieve notes modified used information in a series of queries (form users and email table)
Repeat the process for the next note id that was obtained from the first querry
Please note the above description was obtained by trying out each query from two types of patterns. One that, what appears to be, a dependency on the notes’s contacts association to a department.
Example pattern if a contact has a department:
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '10d508d0-58dc-dfb6-64dc-5ad0b87de780' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026106834411621
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'
[INFO] Query Execution Time:0.00019383430480957
[INFO] Query:SELECT ea.email_address FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND ear.deleted = 0 AND ea.invalid_email = 0 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:0.00029706954956055
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c WHERE contacts.id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026416778564453
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name from contacts left join accounts_contacts a_c on a_c.contact_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' and a_c.deleted=0 left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id where contacts.id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:0.0004119873046875
[INFO] Query:SELECT user_id id FROM contacts_users WHERE contacts_users.contact_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND contacts_users.deleted=0
[INFO] Query Execution Time:0.00015807151794434
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id FROM cm1_department_contacts_1_c WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:0.00019407272338867
[INFO] Query:SELECT cm1_department.*,cm1_department_cstm.* FROM cm1_department LEFT JOIN cm1_department_cstm ON cm1_department.id = cm1_department_cstm.id_c WHERE cm1_department.id = '91d1ff8c-acbf-9f55-a41f-5ad0a73b4600' AND cm1_department.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.2928924560547E-5
[INFO] Query:SELECT accounts_cm1_department_1accounts_ida id FROM accounts_cm1_department_1_c WHERE accounts_cm1_department_1_c.accounts_cm1_department_1cm1_department_idb = '91d1ff8c-acbf-9f55-a41f-5ad0a73b4600' AND accounts_cm1_department_1_c.deleted=0
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT accounts.*,accounts_cstm.* FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE accounts.id = '363922dd-8835-e368-4a71-5ad0a32f5229' AND accounts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:6.413459777832E-5
[INFO] Query:SELECT a1.name from accounts a1, accounts a2 where a1.id = a2.parent_id and a2.id = '363922dd-8835-e368-4a71-5ad0a32f5229' and a1.deleted=0
[INFO] Query Execution Time:4.1961669921875E-5
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Accounts' AND ear.bean_id = '363922dd-8835-e368-4a71-5ad0a32f5229' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT id ,accounts.name as accounts_cm1_department_1_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id='363922dd-8835-e368-4a71-5ad0a32f5229'
[INFO] Query Execution Time:5.6028366088867E-5
[INFO] Query:SELECT id ,cm1_department.name as cm1_department_contacts_1_name , cm1_department.assigned_user_id owner FROM cm1_department WHERE deleted=0 AND id='91d1ff8c-acbf-9f55-a41f-5ad0a73b4600'
[INFO] Query Execution Time:3.814697265625E-5
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:0.00030398368835449
[INFO] Query:SELECT users.*,users_cstm.* FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c' AND users.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.9843063354492E-5
[INFO] Query:SELECT u1.first_name, u1.last_name from users u1, users u2 where u1.id = u2.reports_to_id AND u2.id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c' and u1.deleted=0
[INFO] Query Execution Time:4.1007995605469E-5
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Users' AND ear.bean_id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.887580871582E-5
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='74d46d87-fa51-33d2-f1b0-5ad0a276eb5c'
[INFO] Query Execution Time:4.1961669921875E-5
Example where notes’ contact does not have a department associated
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '11350d4f-ab53-2267-c8a3-5ad0b81a2c66' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00018906593322754
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='d789902d-292f-a0eb-77cc-5ad0aa992276'
[INFO] Query Execution Time:4.4822692871094E-5
[INFO] Query:SELECT ea.email_address FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND ear.deleted = 0 AND ea.invalid_email = 0 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:3.7908554077148E-5
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c WHERE contacts.id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.6028366088867E-5
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name from contacts left join accounts_contacts a_c on a_c.contact_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' and a_c.deleted=0 left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id where contacts.id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:5.3882598876953E-5
[INFO] Query:SELECT user_id id FROM contacts_users WHERE contacts_users.contact_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND contacts_users.deleted=0
[INFO] Query Execution Time:4.0054321289062E-5
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id FROM cm1_department_contacts_1_c WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:6.8902969360352E-5
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Contacts' AND ear.bean_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.4107437133789E-5
[INFO] Query:SELECT users.*,users_cstm.* FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63' AND users.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT u1.first_name, u1.last_name from users u1, users u2 where u1.id = u2.reports_to_id AND u2.id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63' and u1.deleted=0
[INFO] Query Execution Time:3.8862228393555E-5
[INFO] Query:SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.confirm_opt_in, ea.date_created, ea.date_modified, ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id WHERE ear.bean_module = 'Users' AND ear.bean_id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63' AND ear.deleted = 0 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.6014785766602E-5
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='75031e6d-aefe-067c-8e1e-5ad0a212fe63'
[INFO] Query Execution Time:3.7908554077148E-5
I can confirm that the pattern is repeated for all 1057 notes. None of that information is actually needed.
I tried editing the Cases module subpanel/default.php to only show case number thinking that the assigned to and account is somehow triggering all those queries. Tt does not have any affect and these, for this purposes is unnecessary, queries are being executed.
The history panel does not seem to be affected in anyway and the notes entries there are not affected irrespective of the having that Cases subpanel enabled or hidden.
You were supposed to go get an SQL query from that link and execute it, I don’t think you saw that
If you don’t “prune” regularly and automatically, you should prune occasionally and manually. You can start by running queries on all your larger tables like this:
SELECT YEAR(date_modified), COUNT(*) FROM `leads`
WHERE deleted=1
GROUP BY YEAR(date_modified)
You can then turn those into DELETE queries, deleting everything, or everything except the current month, for example.
Then you should examine orphaned records. For example, when you delete a Contact, there could still be an entry for it in contacts_cstm, or security_groups_records, for example.
Then clear up tracking tables like job_queue or aow_processed. These cleanups require some care - delete only finished jobs, and delete only AOW information that is not relevant to any Workflows you are using which have “Repeated runs” unchecked (these need to keep track of past actions in order to function well).
Here’s some more SQL scraps I had stored here somewhere, these will save you some time
Delete orphaned records from contacts_cstm:
SELECT * -- DELETE ChildTable
FROM contacts_cstm ChildTable
LEFT JOIN contacts ParentTable
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL
Delete orphaned records from securitygroups_records:
SELECT record_id, module, s.deleted, c.last_name, c.deleted -- DELETE
FROM securitygroups_records s
LEFT JOIN contacts c
ON s.record_id = c.id
WHERE c.id IS NULL
AND s.module='Contacts'
These are phrased as SELECTS, but if you delete the SELECT keyword and use the DELETE that is after “–” (which marks an SQL comment), you will delete rows.
*** --> USE CAREFULLY, AT YOUR OWN RISK, AND BACKUP ENTIRE DATABASE FIRST <-- ***
