by eggsurplus

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.

Free 30 day trial
Try it Now

#522 - SQL Server 2008 R2 slow query

Closed Bug? created by Stefan Verified Purchase 5 years ago

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.

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 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

  2. staganyi member avatar

    Stefan Verified Purchase

    5 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

    I don't have issues with deleted or duplicate records. The table only contains 44K rows.

    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

    How would I apply this to SQL Server since it's a MySQL tool?

    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

    I do not have any SchedulersJobs records in the securitygroups_records table

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 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)?

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 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?

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 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.

  3. staganyi member avatar

    Stefan Verified Purchase

    5 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.

  4. staganyi member avatar

    Stefan Verified Purchase

    5 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.

  5. staganyi member avatar

    Stefan Verified Purchase

    5 years ago

    Moving the data to a fresh installation allowed the query to run within 2 seconds. Not ideal but at least it runs.

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 years ago

      Thanks, I will look into that.

  6. staganyi member avatar

    Stefan Verified Purchase

    5 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.

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

    • staganyi member avatar

      Stefan Verified Purchase

      5 years ago

      Toggling that feature on and off didn't make much of a change. The server it's running on was recently upgraded.

  7. staganyi member avatar

    Stefan Verified Purchase

    5 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.

  8. staganyi member avatar

    Stefan Verified Purchase

    5 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. staganyi member avatar

    Stefan Verified Purchase

    5 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?

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 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.

  10. staganyi member avatar

    Stefan Verified Purchase

    5 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);

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 years ago

      Very interesting! Thanks for sharing that.

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
Rating
  • "No brainer. I have purchased it for two production installations and not regretted it."

    Read More Reviews

Keep up to date on the latest additions

We'll send you an email every month with handpicked add-ons, reviews, tricks and tips. Don't worry, we hate spam as much as you do.