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

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.