Performance issues for regular users

Hi Mike Pescuma

Definitely queries related to regular users are the most complex ones due to constrains applied by Teams and Roles, but it doesn't explain such a performance degradation.

Where is your instance hosted in? OnSite or OnDemand?

Go to Admin -> System Settings and enable "Log slow queries". Additionally you can update seeting "Slow query time threshold" in order to only display queries slower than a specific time in msec.

Let us know the slower queries so we can help you accordingly.

Kind regards

Thanks for replying!  We're hosting it locally.  Windows, Apache, MySQL and PHP.

I've just checked off the Log slow Queries option and changed the query threshold to 1000ms.

I'll get someone to test and report back.

OK, this is what I get in the Sugar log:

Fri Feb 12 13:08:26 2021 [472][395cc4c6-b4fc-11ea-ba15-005056bebb38][ALERT] Slow Query (time: 48.856 s): SELECT accounts.id, accounts.date_modified accounts__date_modified FROM accounts INNER JOIN (SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_membershipsaccounts ON (team_membershipsaccounts.team_id = tst.team_id) AND (team_membershipsaccounts.user_id = ?) AND (team_membershipsaccounts.deleted = 0) GROUP BY tst.team_set_id) accounts_tf ON accounts_tf.team_set_id = accounts.team_set_id LEFT JOIN accounts_cstm accounts_cstm ON accounts_cstm.id_c = accounts.id WHERE accounts.deleted = ? ORDER BY accounts.date_modified DESC, accounts.id DESC LIMIT 21\r\nParams: {"1":"395cc4c6-b4fc-11ea-ba15-005056bebb38","2":0}\r\nTypes: {"1":2,"2":5}
Fri Feb 12 13:08:28 2021 [472][395cc4c6-b4fc-11ea-ba15-005056bebb38][ALERT] Slow Query (time: 1.982 s): SELECT accounts.account_type, accounts.industry, case when jt1_following_link.id IS NOT NULL then 1 else 0 end following, case when jt4_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite, accounts.name, accounts.billing_address_city, accounts.billing_address_state, accounts.phone_office, jt2_assigned_user_link.first_name rel_assigned_user_name_first_name, jt2_assigned_user_link.last_name rel_assigned_user_name_last_name, jt2_assigned_user_link.created_by assigned_user_name_owner, accounts_cstm.student_count_c, accounts.date_entered, accounts.date_modified, accounts_cstm.school_count_c, accounts_cstm.ehr_c, accounts_cstm.billing_groups_c, accounts_cstm.license_count_c, accounts_cstm.hosted_c, accounts_cstm.agency_type_c, accounts_cstm.billing_company_c, accounts_cstm.legacy_customer_c, accounts_cstm.serial_number_c, jt3_email_addresses_primary.email_address email, accounts_cstm.fixed_pricing_c, accounts_cstm.critical_customer_c, accounts_cstm.manual_billing_c, accounts_cstm.sis_c, accounts.website, accounts_cstm.vip_account_c, accounts.billing_address_country, accounts_cstm.expire_date_c, accounts_cstm.qbname_psni_c, accounts.assigned_user_id, accounts.id, accounts.created_by, jt3_email_addresses_primary_erased.data email_addresses_primary_erased_fields FROM accounts LEFT JOIN sugarfavorites sf_accounts ON (sf_accounts.module = ?) AND (sf_accounts.record_id = accounts.id) AND (sf_accounts.assigned_user_id = ?) AND (sf_accounts.deleted = ?) LEFT JOIN subscriptions accounts_following ON (accounts.id = accounts_following.parent_id) AND (accounts_following.deleted = ?) AND (accounts_following.parent_type = ?) AND (accounts_following.created_by = ?) LEFT JOIN users jt1_following_link ON (jt1_following_link.id = accounts_following.created_by) AND (jt1_following_link.deleted = ?) LEFT JOIN users jt2_assigned_user_link ON (accounts.assigned_user_id = jt2_assigned_user_link.id) AND (jt2_assigned_user_link.deleted = ?) LEFT JOIN accounts_cstm accounts_cstm ON accounts_cstm.id_c = accounts.id LEFT JOIN email_addr_bean_rel accounts_email_addresses_primary ON (accounts.id = accounts_email_addresses_primary.bean_id) AND (accounts_email_addresses_primary.deleted = ?) AND (accounts_email_addresses_primary.bean_module = ?) AND (accounts_email_addresses_primary.primary_address = ?) LEFT JOIN email_addresses jt3_email_addresses_primary ON (jt3_email_addresses_primary.id = accounts_email_addresses_primary.email_address_id) AND (jt3_email_addresses_primary.deleted = ?) LEFT JOIN erased_fields jt3_email_addresses_primary_erased ON jt3_email_addresses_primary_erased.bean_id = jt3_email_addresses_primary.id AND jt3_email_addresses_primary_erased.table_name = ? LEFT JOIN sugarfavorites accounts_favorite ON (accounts.id = accounts_favorite.record_id) AND (accounts_favorite.deleted = ?) AND (accounts_favorite.module = ?) AND (accounts_favorite.created_by = ?) LEFT JOIN users jt4_favorite_link ON (jt4_favorite_link.id = accounts_favorite.modified_user_id) AND (jt4_favorite_link.deleted = ?) WHERE (accounts.id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND (accounts.deleted = ?) ORDER BY accounts.date_modified DESC, accounts.id DESC\r\nParams: {"1":"Accounts","2":"395cc4c6-b4fc-11ea-ba15-005056bebb38","3":0,"4":"0","5":"Accounts","6":"395cc4c6-b4fc-11ea-ba15-005056bebb38","7":0,"8":0,"9":0,"10":"Accounts","11":1,"12":0,"13":"email_addresses","14":"0","15":"Accounts","16":"395cc4c6-b4fc-11ea-ba15-005056bebb38","17":0,"18":"13bb32be-6566-11eb-9101-005056bebb38","19":"cb1b9d5e-6566-11eb-a594-005056bebb38","20":"a1119fe4-66a7-11eb-bb4e-005056bebb38","21":"1f060e8a-6568-11eb-a8d3-005056bebb38","22":"50f14e7e-6567-11eb-8892-005056bebb38","23":"8d5038e0-6566-11eb-abc1-005056bebb38","24":"e9f1881e-6567-11eb-a241-005056bebb38","25":"86c24224-6567-11eb-87bc-005056bebb38","26":"74b8b742-6568-11eb-b097-005056bebb38","27":"94031724-6567-11eb-8fa3-005056bebb38","28":"0e2fe402-6566-11eb-bf02-005056bebb38","29":"0e083cae-6566-11eb-bff8-005056bebb38","30":"8b216fa8-6566-11eb-bbd4-005056bebb38","31":"01673486-6567-11eb-a3fc-005056bebb38","32":"c79574c4-6567-11eb-81bd-005056bebb38","33":"5fb50874-6567-11eb-a898-005056bebb38","34":"c739679c-6567-11eb-8511-005056bebb38","35":"4613c42c-6568-11eb-9003-005056bebb38","36":"0136cc42-6567-11eb-8a47-005056bebb38","37":"8afaa5a8-6566-11eb-b57c-005056bebb38","38":"3a6f15f0-6567-11eb-a63f-005056bebb38","39":0}\r\nTypes: {"1":2,"2":2,"3":2,"4":2,"5":2,"6":2,"7":5,"8":5,"9":5,"10":2,"11":5,"12":5,"13":2,"14":2,"15":2,"16":2,"17":5,"18":2,"19":2,"20":2,"21":2,"22":2,"23":2,"24":2,"25":2,"26":2,"27":2,"28":2,"29":2,"30":2,"31":2,"32":2,"33":2,"34":2,"35":2,"36":2,"37":2,"38":2,"39":5}
Fri Feb 12 13:08:37 2021 [472][395cc4c6-b4fc-11ea-ba15-005056bebb38][ALERT] Slow Query (time: 52.087 s): SELECT accounts.id, accounts.date_modified accounts__date_modified FROM accounts INNER JOIN (SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_membershipsaccounts ON (team_membershipsaccounts.team_id = tst.team_id) AND (team_membershipsaccounts.user_id = ?) AND (team_membershipsaccounts.deleted = 0) GROUP BY tst.team_set_id) accounts_tf ON accounts_tf.team_set_id = accounts.team_set_id LEFT JOIN accounts_cstm accounts_cstm ON accounts_cstm.id_c = accounts.id WHERE accounts.deleted = ? ORDER BY accounts.date_modified DESC, accounts.id DESC LIMIT 21\r\nParams: {"1":"395cc4c6-b4fc-11ea-ba15-005056bebb38","2":0}\r\nTypes: {"1":2,"2":5}
Fri Feb 12 13:08:38 2021 [472][395cc4c6-b4fc-11ea-ba15-005056bebb38][ALERT] Slow Query (time: 1.370 s): SELECT accounts.account_type, accounts.industry, case when jt1_following_link.id IS NOT NULL then 1 else 0 end following, case when jt4_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite, accounts.name, accounts.billing_address_city, accounts.billing_address_state, accounts.phone_office, jt2_assigned_user_link.first_name rel_assigned_user_name_first_name, jt2_assigned_user_link.last_name rel_assigned_user_name_last_name, jt2_assigned_user_link.created_by assigned_user_name_owner, accounts_cstm.student_count_c, accounts.date_entered, accounts.date_modified, accounts_cstm.school_count_c, accounts_cstm.ehr_c, accounts_cstm.billing_groups_c, accounts_cstm.license_count_c, accounts_cstm.hosted_c, accounts_cstm.agency_type_c, accounts_cstm.billing_company_c, accounts_cstm.legacy_customer_c, accounts_cstm.serial_number_c, jt3_email_addresses_primary.email_address email, accounts_cstm.fixed_pricing_c, accounts_cstm.critical_customer_c, accounts_cstm.manual_billing_c, accounts_cstm.sis_c, accounts.website, accounts_cstm.vip_account_c, accounts.billing_address_country, accounts_cstm.expire_date_c, accounts_cstm.qbname_psni_c, accounts.assigned_user_id, accounts.id, accounts.created_by, jt3_email_addresses_primary_erased.data email_addresses_primary_erased_fields FROM accounts LEFT JOIN sugarfavorites sf_accounts ON (sf_accounts.module = ?) AND (sf_accounts.record_id = accounts.id) AND (sf_accounts.assigned_user_id = ?) AND (sf_accounts.deleted = ?) LEFT JOIN subscriptions accounts_following ON (accounts.id = accounts_following.parent_id) AND (accounts_following.deleted = ?) AND (accounts_following.parent_type = ?) AND (accounts_following.created_by = ?) LEFT JOIN users jt1_following_link ON (jt1_following_link.id = accounts_following.created_by) AND (jt1_following_link.deleted = ?) LEFT JOIN users jt2_assigned_user_link ON (accounts.assigned_user_id = jt2_assigned_user_link.id) AND (jt2_assigned_user_link.deleted = ?) LEFT JOIN accounts_cstm accounts_cstm ON accounts_cstm.id_c = accounts.id LEFT JOIN email_addr_bean_rel accounts_email_addresses_primary ON (accounts.id = accounts_email_addresses_primary.bean_id) AND (accounts_email_addresses_primary.deleted = ?) AND (accounts_email_addresses_primary.bean_module = ?) AND (accounts_email_addresses_primary.primary_address = ?) LEFT JOIN email_addresses jt3_email_addresses_primary ON (jt3_email_addresses_primary.id = accounts_email_addresses_primary.email_address_id) AND (jt3_email_addresses_primary.deleted = ?) LEFT JOIN erased_fields jt3_email_addresses_primary_erased ON jt3_email_addresses_primary_erased.bean_id = jt3_email_addresses_primary.id AND jt3_email_addresses_primary_erased.table_name = ? LEFT JOIN sugarfavorites accounts_favorite ON (accounts.id = accounts_favorite.record_id) AND (accounts_favorite.deleted = ?) AND (accounts_favorite.module = ?) AND (accounts_favorite.created_by = ?) LEFT JOIN users jt4_favorite_link ON (jt4_favorite_link.id = accounts_favorite.modified_user_id) AND (jt4_favorite_link.deleted = ?) WHERE (accounts.id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND (accounts.deleted = ?) ORDER BY accounts.date_modified DESC, accounts.id DESC\r\nParams: {"1":"Accounts","2":"395cc4c6-b4fc-11ea-ba15-005056bebb38","3":0,"4":"0","5":"Accounts","6":"395cc4c6-b4fc-11ea-ba15-005056bebb38","7":0,"8":0,"9":0,"10":"Accounts","11":1,"12":0,"13":"email_addresses","14":"0","15":"Accounts","16":"395cc4c6-b4fc-11ea-ba15-005056bebb38","17":0,"18":"13bb32be-6566-11eb-9101-005056bebb38","19":"cb1b9d5e-6566-11eb-a594-005056bebb38","20":"a1119fe4-66a7-11eb-bb4e-005056bebb38","21":"1f060e8a-6568-11eb-a8d3-005056bebb38","22":"50f14e7e-6567-11eb-8892-005056bebb38","23":"8d5038e0-6566-11eb-abc1-005056bebb38","24":"e9f1881e-6567-11eb-a241-005056bebb38","25":"86c24224-6567-11eb-87bc-005056bebb38","26":"74b8b742-6568-11eb-b097-005056bebb38","27":"94031724-6567-11eb-8fa3-005056bebb38","28":"0e2fe402-6566-11eb-bf02-005056bebb38","29":"0e083cae-6566-11eb-bff8-005056bebb38","30":"8b216fa8-6566-11eb-bbd4-005056bebb38","31":"01673486-6567-11eb-a3fc-005056bebb38","32":"c79574c4-6567-11eb-81bd-005056bebb38","33":"5fb50874-6567-11eb-a898-005056bebb38","34":"c739679c-6567-11eb-8511-005056bebb38","35":"4613c42c-6568-11eb-9003-005056bebb38","36":"0136cc42-6567-11eb-8a47-005056bebb38","37":"8afaa5a8-6566-11eb-b57c-005056bebb38","38":"3a6f15f0-6567-11eb-a63f-005056bebb38","39":0}\r\nTypes: {"1":2,"2":2,"3":2,"4":2,"5":2,"6":2,"7":5,"8":5,"9":5,"10":2,"11":5,"12":5,"13":2,"14":2,"15":2,"16":2,"17":5,"18":2,"19":2,"20":2,"21":2,"22":2,"23":2,"24":2,"25":2,"26":2,"27":2,"28":2,"29":2,"30":2,"31":2,"32":2,"33":2,"34":2,"35":2,"36":2,"37":2,"38":2,"39":5}

I hope that makes sense.

Do you have lots of Teams? An admin user account does not include checking record level ACL. Regular Users use record level ACL; which includes adding team information into the SQL query.

As a test, you can create a Role https://<sugarcrm>/#bwc/index.php?module=ACLRoles&action=EditView. In the role, you can call it anything you like. 

In the Access Type (set it to Admin). This will remove the check for teams for members of the role.

Save the role

Add a User to the role.

Does this improve the performance?

Do you have lots of Teams? An admin user account does not include checking record level ACL. Regular Users use record level ACL; which includes adding team information into the SQL query.

We actually only have 1 Team right now.  We're still in the process of setting everything up.  Just got to training people and they reported the slowness... and neither I nor the other admin had experienced it.  We also only have two roles right now the "Admin" role and the "Regular User" role.  Is it worth doing your test?

These queries frequently are related to performance degradation in case several teams are configured and related to users, which is not your case, so I believe your issue is related to MySQL configuration.

Some tips:

Which engine is configured on MySQL? MyISAM or INNODB?

Perhaps some table may be corrupted, double check that with mysqlcheck.

Perhaps MySQL configuration is not properly tunned for better performance.

I'll check out the mysqlcheck, but I just looked it up and we are using INNODB.

If MySQL configuration wasn't properly tuned wouldn't it still be slow for admins as well?

mysqlcheck comes back clean...

We did some testing and found out...
WIthout the INNER JOIN, it is less than 1 second.
Without the GROUP BY it's 1.6 seconds.

It seems like a query issue... any ideas on how we can deal with this?  Or are we on the wrong track?

Just curious, what version is this? Definitely agree with others that it is most likely related to Teams and Roles. 

One thing to note is that the latest on premise version only supports a full Windows stack - Windows, IIS, MSSQL and PHP. See https://support.sugarcrm.com/Resources/Supported_Platforms/Sugar_10.0.x_Supported_Platforms/ for more details.

With MySQL, you can also use the EXPLAIN function to see why the query is slow.

Finally, have a read of this great post - https://sugarclub.sugarcrm.com/dev-club/b/dev-blog/posts/performance-improvement-through-teams-denormalisation

Good luck.