Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.
#522 - SQL Server 2008 R2 slow query
If a user is part of a security group and navigates to the accounts section, the following query essentially never completes:
SELECT TOP 21 * FROM ( SELECT accounts.id , accounts.name , accounts.shipping_address_street , accounts.shipping_address_city , accounts.shipping_address_state , accounts.shipping_address_postalcode , accounts.phone_office , LTRIM(RTRIM(ISNULL(jt0.first_name,'')+' '+ISNULL(jt0.last_name,''))) assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, accounts.assigned_user_id , ROW_NUMBER() OVER (ORDER BY accounts.date_entered DESC) AS row_number FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.assigned_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 where ( ( accounts.assigned_user_id ='xxxxxx' or EXISTS (SELECT 1 FROM securitygroups secg INNER JOIN securitygroups_users secu ON secg.id = secu.securitygroup_id AND secu.deleted = 0 AND secu.user_id = 'xxxxx' INNER JOIN securitygroups_records secr ON secg.id = secr.securitygroup_id AND secr.deleted = 0 AND secr.module = 'Accounts' WHERE secr.record_id = accounts.id AND secg.deleted = 0) ) ) AND accounts.deleted=0 ) AS a WHERE row_number > 0
If a user is not part of a security group, then it completes (sometimes it's still logged as a slow query in sugarcrm.log but performance is acceptable):
SELECT TOP 21 * FROM ( SELECT accounts.id , accounts.name , accounts.shipping_address_street , accounts.shipping_address_city , accounts.shipping_address_state , accounts.shipping_address_postalcode , accounts.phone_office , LTRIM(RTRIM(ISNULL(jt0.first_name,'')+' '+ISNULL(jt0.last_name,''))) assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, accounts.assigned_user_id , ROW_NUMBER() OVER (ORDER BY accounts.name ASC) AS row_number FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.assigned_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 where ( ( accounts.assigned_user_id ='xxxxxxxxxxxxxxxxx' or EXISTS (SELECT 1 FROM securitygroups secg INNER JOIN securitygroups_users secu ON secg.id = secu.securitygroup_id AND secu.deleted = 0 AND secu.user_id = 'xxxxxxxxxxxxxxx' INNER JOIN securitygroups_records secr ON secg.id = secr.securitygroup_id AND secr.deleted = 0 AND secr.module = 'Accounts' WHERE secr.record_id = accounts.id AND secg.deleted = 0) ) ) AND accounts.deleted=0 ) AS a WHERE row_number > 0
Any help you can give me would be great because the system is essentially useless at this time.
-
"Absolutely necessary for limiting user visibility."
Read More Reviews
- Most Recent Love from Users
kacsoristvan- damien8105
- anri
- Administrador
- Stefano1
- einkauf2
10 years ago
Check out some of the tips I've posted here for cleaning out some unneeded data that would affect performance: https://www.sugaroutfitters.com/support/securitysuite/496
In addition to that, I'd advise running and applying any recommended indexes and MySQL variables that MySQLTuner comes up with: https://github.com/rackerhacker/MySQLTuner-perl
If you are seeing a lot of SchedulersJobs records in the securitygroups_records table then definitely consider upgrading to the latest 2.7.2 SecuritySuite release for SugarCRM 6.5.16. There was a possibility where SchedulersJobs and a couple of other tables to automatically inherit groups causing the securitygroups_records table to inflate quickly. More info: https://www.sugaroutfitters.com/docs/securitysuite/release-notes
10 years ago
Check out some of the tips I've posted here for cleaning out some unneeded data that would affect performance: www.sugaroutfitters.com/support/securitysuite/496
In addition to that, I'd advise running and applying any recommended indexes and MySQL variables that MySQLTuner comes up with: github.com/rackerhacker/MySQLTuner-perl
If you are seeing a lot of SchedulersJobs records in the securitygroups_records table then definitely consider upgrading to the latest 2.7.2 SecuritySuite release for SugarCRM 6.5.16. There was a possibility where SchedulersJobs and a couple of other tables to automatically inherit groups causing the securitygroups_records table to inflate quickly. More info: www.sugaroutfitters.com/docs/securitysuite/release-notes
10 years ago
Good point. SQL Server does have some profiling tools. I would recommend turning on the profiling tool during normal site usage so that it can log usage. Then run the analyzer on that set of data. It's been a very, very long time since I've done that so I don't recall the steps to do so.
10 years ago
Additional note. I noticed long ago when I primarily ran on SQL Server that it did not perform well with all of the ID fields in the database being varchar fields. Converting those to native guid fields had an incredible affect on performance. The one gotcha to doing that was the admin id = 1 issue. That had to be changed to a guid across all tables before converting worked.
9 years ago
I somehow missed this reply of yours. If I understand correctly, I would need to update every record in the db referring to admin id=1 to some new generated guid. Then modify all the pk's from varchar(36) to uniqueidentifier? Furthermore, I'm just updating the primary keys on tables right, not the foreign ones (i.e. accounts.parent_id)?
9 years ago
Yes, but you would want to do foreign ones as well. You'll probably need to defragment/optimize the tables after. This is an exercise that is best done in a test instance first as it'll be some pretty hefty work.
9 years ago
First issue I've come across is setting a uniqueidentifier column to an empty string. That fails with the error msg "Conversion failed when converting from a character string to uniqueidentifier". For example, this occurs when saving an account and the generated query tries to set parent_id=''. I can't find anything online relating to allowing empty strings in uniqueidentifier fields. Did you ever come across this issue?
9 years ago
Did you try to convert the empty strings to null? I think that works for uniqueidentifier fields. Otherwise you may be able to only do this on primary keys for now.
9 years ago
Yes converting to null works, but sugar sets certain fields to empty strings in the code (i.e. accounts.parent_id=''). I will start with the pk's and see if that helps.
9 years ago
Unfortunately looks like it's going to be a dead end because there are many locations in sugar causing "Conversion failed when converting from a character string to uniqueidentifier" errors. Might be more trouble than it's worth.
10 years ago
I guess I'll give that a shot.
I don't know if you can reach out to any of your customers that are on SQL Server that might be able to provide some insight.
10 years ago
Unfortunately the profiling tools suggested no changes! I'm going to try a fresh install and copy over the accounts and security group records to see if that might fix the issue. Not ideal, but I've got to try something.
10 years ago
Moving the data to a fresh installation allowed the query to run within 2 seconds. Not ideal but at least it runs.
10 years ago
Thanks for reporting back. If it starts to creep up you may want to add a nightly task in MSSQL to defrag and reindex the tables.
10 years ago
Thanks, I will look into that.
9 years ago
Ok, so everything was going pretty good in my new installation (suitecrm v7.1.1) until I logged in as separate users and for a handful of them the calendar (module and dashlet) caused the page to load in ~15 seconds. I've performed a defrag and reindex on the db and that didn't help. There's not even 500 records combined in the database for calls, meetings and tasks, so the number of records can't be the issue either. My previous installation (sugarcrm ce v6.5.13) was slow in certain areas, but it did not have any calendar load time issues. Which leads me to think that maybe changes you made in SecuritySuite might be the cause of my problems. If I look at your release notes, version 2.7.1 received a change to the calendar module. I was running v2.6.5 in my previous installation and now I'm running the latest 2.7.3. How do I get v2.7.0 to test my theory because I can't find it in my download links? Once I get it, do I simply install it over the current installation or must I uninstall the current version first since I'm downgrading? Thanks.
9 years ago
Try toggling the new Shared Calendar - Hide Restricted setting instead: https://www.sugaroutfitters.com/docs/securitysuite/options#hiderestricted
That will do the same thing as rolling back to 2.7.0. You may want to consider upgrading the server that Sugar is running on, too. There should be 0 performance issues with just 500 records.
9 years ago
Toggling that feature on and off didn't make much of a change. The server it's running on was recently upgraded.
9 years ago
I'm starting to think that maybe the calendar slowness is a sugarcrm issue and not security groups. I was on sugar 6.5.13 and upgraded to suitecrm (which is sugar 6.5.16). I will do further tests.
9 years ago
Seems like it is slower on v6.5.16. Is it possible to send me 2.7.0 so I can compare it's performance to 2.7.3?
9 years ago
Looks like the performance is similar for v2.7.0 on v6.5.16 as v2.7.3, which is not good in my case (for the calendar). Since the calendar performance on v2.6.5 & v6.5.13 is good and v2.7.* & v6.5.16 is not, what files would I need to compare to try and troubleshoot the issue?
9 years ago
SugarCRM did change quite a bit in the Calendar module between those versions so that may the issue that you are running into.
9 years ago
Well, after all this pain and suffering, looks like the calendar speed issue was a bug in Sugar 6.5.16 after all. I found the following blog post that describes the problem http://www.sugaruk.co.uk/blog/sugar-6516-calendar-speed-issues (content pasted below in case link goes dead):
The fix is simply a code change in the modules/Calendar/CalendarActivity.php file. Depending which version of Sugar is in use will depend on whereabouts in the file the fix needs to be inserted. As you are using Community edition you need to edit the above file and navigate to line 182 once there, simply change the code from
$where = self::get_occurs_until_where_clause($meeting->table_name, $meeting->rel_users_table, $view_start_time, $view_end_time, 'date_start', $view);
To
$where = self::get_occurs_within_where_clause($meeting->table_name, $meeting->rel_users_table, $view_start_time, $view_end_time, 'date_start', $view);
9 years ago
Very interesting! Thanks for sharing that.