Pages what require AJAX loads at different speeds

johnwreford · 16 October 2023 19:29

If a user is an Admin the Ajax pages load 2-3 times faster than a non-Admin user.
This applies to all Admin accounts, they are faster than any other user.
It isn’t a logic hook as I don’t have any admin-specific ones.

https://XXXX.com/index.php?module=XXXz&action=index&return_module=XXXX&return_action=DetailView&ajax_load=1

According to Chrome Network Inspector, this page will load in 2.5 seconds or less. Whereas if I am not an Admin it will load in upwards of 8-22 seconds. This would be on all the same computer/browser/internet connection.

What would be doing this and how can I fix this or even start to investigate this?

pgr · 17 October 2023 08:31

I would start by suspecting the database (overgrowth problems).

One of the typical culprits of database slow-downs is security_groups_record. But this check will be quickly bypassed for admins.

Try running the first query in this post

johnwreford · 31 October 2023 16:17

Thank you pgr.

After spending many hours testing I discovered it is favorites that slows things down.

As an Admin I have nothing in my favorites.
As a user, some people have 5 things favorited.

If you have favorites it slows things down significantly.
This seems weird. I have removed favorites for the time being. I have no idea how to fix this or troubleshoot this at the moment.
The issue should be easily replicated.

pgr · 31 October 2023 17:16

What is your version of SuiteCRM, and of PHP?

I think that was fixed quite some time ago.

EDIT: see this for some background:

github.com/salesagility/SuiteCRM

Favorites getCurrentUserSidebarFavorites function is too costly

opened 01:52AM - 12 Jul 18 UTC

jshwhitlow

Type:Bug Priority:Important

#### Issue After analyzing my instance to improve performance I have found that… a huge burden on resources is coming from the `getCurrentUserSidebarFavorites` function within modules/Favorites/Favorites.php A breakdown of a 4.6 second load times revealed that 3.5 seconds of that time was spent on `getCurrentUserSidebarFavorites ` and the time spent there was so large due to the person in question having 40 favorites. #### Expected Behavior This function needs to be optimized, retrieving a user with even just 10-15 favorites is very costly. Let alone 40+ favorites. #### Actual Behavior Issue already described above. #### Possible Fix Taking a look at the code in modules/Favorites/Favorites.php on line 103-141: ``` public function getCurrentUserSidebarFavorites($id = null) { global $current_user; $db = DBManagerFactory::getInstance(); $return_array = array(); if ($id) { $query = "SELECT parent_id, parent_type FROM favorites WHERE assigned_user_id = '" . $current_user->id . "' AND parent_id = '" . $id . "' AND deleted = 0 ORDER BY date_entered DESC"; } else { $query = "SELECT parent_id, parent_type FROM favorites WHERE assigned_user_id = '" . $current_user->id . "' AND deleted = 0 ORDER BY date_entered DESC"; } $result = $db->query($query); $i = 0; while ($row = $db->fetchByAssoc($result)) { $bean = BeanFactory::getBean($row['parent_type'], $row['parent_id']); if($bean) { $return_array[$i]['item_summary'] = $bean->name; $return_array[$i]['item_summary_short'] = to_html(getTrackerSubstring($bean->name)); $return_array[$i]['id'] = $row['parent_id']; $return_array[$i]['module_name'] = $row['parent_type']; // Change since 7.7 side bar icons can exist in images/sidebar. $sidebarPath = 'themes/' . SugarThemeRegistry::current() . '/images/sidebar/modules/'; if (file_exists($sidebarPath)) { $return_array[$i]['image'] = SugarThemeRegistry::current()->getImage('sidebar/modules/' . $row['parent_type'], 'border="0" align="absmiddle"', null, null, '.gif', $bean->name); } else { $return_array[$i]['image'] = SugarThemeRegistry::current()->getImage($row['parent_type'], 'border="0" align="absmiddle"', null, null, '.gif', $bean->name); } ++$i; } } return $return_array; } ``` We can see that its retrieving the bean for each and every favorite thats pulled back. This is way too costly considering that its only being used to pull back the `bean->name`. **Several possible solutions:** 1. Create a new function that only retrieves the bean name to avoid the costly work of getting the entire bean just for one field. 2. Store the bean name in the database when the favorite is created and call it with sql instead of php, possible downside includes the bean name being outdated, so you'd have to check and update a favorite if one existed with a logic hook every time someone saved a record. 3. Introduce paging to the Favorites section, perhaps only 5-10 favorites at any one time, this could still be combined with suggestion 1) or 3) in order to reduce load times. #### Steps to Reproduce 1. Add 10-20 favorites 2. Use some sort of analytics tool (I use New Relic, you can use other tools to track total time in php files) 3. Observe that most of the time spent loading is spent in `BeanFactory::getBean` being called over and over again by the `getCurrentUserSidebarFavorites `function. #### Context We are trying to work on performance improvements, but are noticing that most of the custom code is not the issue, its the core SuiteCRM code that has a few areas for improvement. Being that this seems to be the core file that's obviously taking way way too long. #### Your Environment * SuiteCRM Version used: 7.10.6 * Browser name and version: Chrome Version 67.0.3396.99 (Official Build) (64-bit) * Environment name and version (e.g. MySQL, PHP 7): MySQL 5.5.59, PHP 7.1.19 * Operating System and version: macOS 10.12.3

And try this change:

… then tell us how it went.

johnwreford · 31 October 2023 20:02

Thank you pgr. I just removed the ability to add favorites for everyone. As the favorites drop off from the list of Leads and Projects, things will speed up and favorites will be gone. For us it isn’t used enough to put any serious effort into recoding this. Thank you.