I created indices for Reminder and Reminder_Invitee which increased the speed by 30%. Also, see further explanation related to the addNotifications() function in the linked forum post.
I tried to replicate this issue (created 500 reminders with different invitees) and could not see any problems.
The 2 lines which were mentioned in the forum:
$popupReminders = BeanFactory::getBean(‘Reminders’)->get_full_list(’’, “reminders.popup = 1”);
and
$invitees = BeanFactory::getBean(‘Reminders_Invitees’)->get_full_list(’’, “reminders_invitees.reminder_id = ‘{$popupReminder->id}’ AND reminders_invitees.related_invitee_module_id = ‘{$current_user->id}’”);
belong to a function called addNotifications. This function is called only occasionally (in order to create the necessary data) and the mentioned lines are needed there (see below):
#####################################################
— ### addNotifications is called only once: ### —
#####################################################
/var/www/html/585/SuiteCRM/modules/Reminders/Reminder.php - 262 goes through every reminder
public static function addNotifications(jsAlerts $alert, $checkDecline = true) {
…
$popupReminders = BeanFactory::getBean(‘Reminders’)->get_full_list(’’, “reminders.popup = 1”);
…
/var/www/html/585/SuiteCRM/include/MVC/View/SugarView.php - 150 calls new jsAlerts(); and $jsAlerts->getScript();
// We have to update jsAlerts as soon as possible
if (
!isset($_SESSION[‘isMobile’])
&&
(
$this instanceof ViewList
|| $this instanceof ViewDetail
|| $this instanceof ViewEdit
)
) {
$jsAlerts = new jsAlerts();
echo $jsAlerts->getScript();
}
/var/www/html/585/SuiteCRM/jssource/src_files/include/javascript/sugar_3.js - 256 checkAlerts() is called from getScript()
function checkAlerts() {
…
alertsTimeoutId = setTimeout(“checkAlerts()”, 1000); - calls itself every second
…
Conclusion: addNotifications is called only once;
checkAlerts() is called every second and uses the data which was created by addNotifications()
another suggestion was is to fix this line by adding a where clause with filtering by dates:
$popupReminders = BeanFactory::getBean(‘Reminders’)->get_full_list(’’, “reminders.popup = 1”);
but this function really needs to get all the reminders in order to check relatedEvent first.
if($popupReminders) {
foreach($popupReminders as $popupReminder) {
$relatedEvent = BeanFactory::getBean($popupReminder->related_event_module, $popupReminder->related_event_module_id);
#Original:
//$jsAlerts = new jsAlerts();
//echo $jsAlerts->getScript();
#Modified:
if (isset($_SESSION['theaxe_modification_alertsoutput']) && isset($_SESSION['theaxe_modification_alertsoutput_timestamp']) &&
$_SESSION['theaxe_modification_alertsoutput_timestamp'] >= (date('U')-60)
)
echo $_SESSION['theaxe_modification_alertsoutput'];
else
{
$jsAlerts = new jsAlerts();
ob_start();
echo $jsAlerts->getScript();
$jsAlertsOutput = ob_get_clean();
//save to session so we dont have to load this every time
$_SESSION['theaxe_modification_alertsoutput'] = $jsAlertsOutput;
$_SESSION['theaxe_modification_alertsoutput_timestamp'] = date('U');
echo $jsAlertsOutput;
}
Export alerts code every minute, save that js code to session and output it, refresh every 60 seconds (increase num seconds if you wish).
It took us a week to find why the CPU is having problems with our Suite with average database. All solutions I googled didn’t help. But then I found your workaround and it worked as charm.
Do you know if it can be fixed permanently somehow?
I have a reminder created after every phone call is finished and the solution provided here helps only partially. With 20 users making 50+ calls a day… It takes 36 sec to open a page when logged in as admin user, and it gets longer every month.
Hello, yea i increased js generation to 10 min because it was up to 45 seconds load time , it seems fetching reminders from database is fast but php processing of that data is slow from unknown reason. Will look into it soon and find elegant solution… maybee tomorrow. Because this will bite my company in the ass soon. Stay tuned
Description of the problem:
All undeleted reminders are loaded from database, this is fast. Those reminders go into loop and each reminder loads related module and
event data from that module and this is the problem fetching data of related module is around 0.03 seconds, multiply that with
1000, 2000 etc and we have a performace issue. So we need to avoid loading all those reminders at once, but we are missing reminder
execution datetime that is extracted from related module, this information on first fetch we will save in reminders table.
My solution:
Add new column to database in table ‘reminders’ in that column it will be timestamp of when reminder execution is set to
(extracted from module in loop). Added mysql filter so we exclude reminders with saved custom timestamp that is in past
and that is too much in future. Now insetead of eg 1000 reminders in loop we have around 30-40.
On reminder update we nullify custom timestamp with reminder and next data fetch it will autogenerate.
Drawbacks:
TODO: on event time update (eg Call) with existing reminder reminder is not changed and timestamp will be old timestamp,
this might result in reminder not showing within 2-3 hour timeframe if, somehow we should nullify custom timestamp on
event update, maybee add some logic hook? I will leave this to someone else. When updating reminder itself in eg Call
reminder will nullify itself.
Notes:
I added blocker so when fetching eg 1000 reminders it won’t update all 1000 at once, it will update timestamps of 40 reminders, and
next run it will do another 40 until nothing needs to be updated anymore. So after this implementation you need to wait some time
for performace get better. So… every pageload it will update 40 reminders. After all reminders have their timestamps script will
run smooth, and will update only new ones every page load.
FILES CHANGED (2):
custom/modules/Reminders/Reminder.php - modified few methods etc… you can compare with original: modules/Reminders/Reminder.php to see changes
custom/modules/Reminders/vardefs.php - added new field definition for custom column. you can compare with original: modules/Reminders/vardefs.php to see changes
HOW TO INSTALL:
ADD NEW COLUMN TO DATABASE
Open database editor add new column to reminders table.
column name: data_willexecute
column type: int
column length: 60
column nullable: YES
column default value: NULL
UPLOAD CUSTOM MODULE
Copy code in this ZIP file and upload to custom/, so we dont have to mess with original Reminders module that might get updated with
next release.
Location: custom/modules/Reminders/*
APPLY VARDEF CHANGES
In administration run Quick Repair and Rebuild so new field ‘data_willexecute’ will get recognized from new Reminders/vardefs.php file.
Thats it, you can remove blocker in SugarView.php that I suggested before. Reload main page of crm, open database table ‘reminders’ and see if
data is filling in.
You can check how many reminders is left to update by running query:
select count(*) from reminders where deleted = 0 and popup = 1 AND date_willexecute IS NULL
Result of this modification:
We load only relative reminders within now and few hours in future, not all reminders like before.
Firstly, I’d like to thank you for taking the trouble to post an update to your previous solution. However I am having some trouble getting this to work and I wondered if you might be able to suggest why this is.
The steps we have taken are as follows:
Previously, we made the mod to the ‘sugarview.php’ file as suggested above and it dramatically increased the speed of our application. However of late, the system has become increasingly slower. Pages are loading very slowly indeed.
I have today followed the 4 steps in your post but it appears the code is not executing as the additional column in the table ‘date_willexecute’ is not populating, every entry has NULL in this column. (There are currently 1749 reminders in the table).
You mention removing the ‘blocker’. By this do you mean removing the previous modification to the sugarview.php file and returning this to the original code or have I missed something else?
Any assistance you can provide is greatly appreciated.
Hello, first go to administration, run quick repair and rebuild, second login with user account that have reminders assigned to it. This script will update obly reminders that are related to that account, check error logs (you can find this in administration), please post your findings
Php error might be a clue to a problem. Recheck step 3 and make sure original line is changed. Error suggest that you have both custom and original script active. Only custom one should be included.
This line:
$beanFiles = ‘modules/Reminders/Reminder.php’;
Modify to this:
$beanFiles = ‘custom/modules/Reminders/Reminder.php’;
Sadly, it is still not working. I have checked that I only have the one script running and I have followed every instruction to the letter.
However I have also just seen that when you log in as a ‘non-admin’ user an error message appears at the bottom of the home screen as follows. (there are no errors showing when an admnistrator is logged in).
Error: Query limit of 1000 reached for Home module.
Also, when you go to any other module such as accounts or contacts it brings up an ajax error and will not display the page with a similar error message, Error: Query limit of 1000 reached for Contacts module.
I am beginning to think that this must be because I am running SuiteCRM v. 7.7.9. Over the weekend I will upload an earlier version of the code and see what happens.
The log file shows the following: - Fri Jan 13 16:42:24 2017 [19219][a84ab3eb-1ce2-e881-7709-572ca7b320c6][FATAL] Error: Query limit of 1000 reached for Home module.
What version of SuiteCRM are you running?
I am also wondering why this issue with speed and reminders has not been a permanent fix by Sales Agility in later code…
I appreciate the time you are putting i to helping me solve this.
Im using 7.7.1, home module error is not related to this modification. Would be easier if i look over your site can you drop a link to your crm and maybee i could remote to you so then can we resolve this
Thanks for your reply and offer of help, I really appreciate it. I will get access set up. is there a way to privately message you with the remote access details?
