List View taking very long time to fetch the data

šŸ’¬ General Discussion
1

Hi Everyone,

I am using SuiteCRM 6.5.23,in my Cases module i have approx 4,50000 records and for list view it will take 20 seconds for fetching data.

Our main module is Cases only and it will take longer timeā€¦so how i will resolve it.

List View Query : ā€œSELECT cases.id , cases.assigned_user_id , cases.case_name , jt0.namenm_order_cases_1_name, jtl0.nm_order_cases_1nm_order_ida nm_order_cases_1nm_order_ida,cases.name , cases.state , cases.account_email , cases.account_mob , cases.is_escalation , cases.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , ā€˜Usersā€™ assigned_user_name_mod, cases.assign_group , cases.created_by FROM cases LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c LEFT JOIN nm_order_cases_1_c jtl0 ONcases.id=jtl0.nm_order_cases_1cases_idb AND jtl0.deleted=0 LEFT JOIN nm_order jt0 ONjt0.id=jtl0.nm_order_cases_1nm_order_ida AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON cases.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 where cases.deleted=0 ORDER BY cases.date_entered DESCā€;

Thanks is advancedā€¦

2

Do you mean 450,000?

And you say SuiteCRM 6.5.23, but thatā€™s not a SuiteCRM version number (starts with 7.0). That looks like a SugarCRM version number.

If youā€™re running SugarCRM, youā€™re in the wrong forums.

But while youā€™re here, let me recommend that you upgrade to SuiteCRM! :slight_smile:

3

Sorry,I am using SuiteCRM version 7.6.5,and i have data in my cases module upto 5 lacs.

4

I had to look up that ā€œlacsā€ thing but I think you mean 5 hundred thousand.

Ok. The best favour you could do to yourself is consider an upgrade to PHP 7.0, which will probably require a SuiteCRM upgrade to some newer version like 7.8 LTS. This will make significant difference in terms of performance.

Then you can check online for generic performance tips, like these:

http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Architecture/Performance_Tuning/Sugar_Performance/

Most of that applies to SuiteCRM, but not all.

Then you can look for MySQL tweaks focusing on the Cases table and itā€™s related tables, namely improved indexes. You can also try moving custom fields onto the main tables.

Finally, you can split your server in two (Apache on one, MySQL on the other) and use faster hardware, especially M.2 SSD disks and plenty of RAM.