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

#744 - Queries running slowly

Closed Bug? created by mike1 5 years ago

Is there any way to optimize the queries from Security Suite? I have a user associated to 8 separate security groups with only one being the primary. In one module this user has access to 316 records and it takes the server 7.15 seconds to load the list view page. Each time I associate this user to another security group, it slows down by almost a full second. I am currently using Sugar CE 6.5.16

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    If you can limit the groups that the user belongs to that would definitely help. Otherwise, better database indexes to cover your usage would be helpful. Here is a very nice tool to run on your database. It'll give you some recommendations for MySQL configurations as well as some indexes that will help in your case:

    https://github.com/rackerhacker/MySQLTuner-perl

    Another thing you can do is to clean up the database of duplicates or deleted records that are no longer needed. See: https://www.sugaroutfitters.com/support/securitysuite/496

    Pulling the database off onto its own server separate from the application server would also help.

  2. mike1 member avatar

    mike1

    5 years ago

    Thanks, Unfortunately this is a HIPAA compliant server so I'm really restricted as to what I have access to on the server.
    I have already removed all duplicates and deleted records and actually just had the hosting company upgrade the server's memory.
    I was just hoping their might be a way to more efficiently run the queries.

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 years ago

      Having worked with HIPAA before, I'm not sure why you wouldn't be able to optimize your database. HIPAA compliance laws shouldn't have any impact on that.

      No silver bullet for making queries more efficient. If there is slowness on an install the answer is to always optimize the database based on usage or to upgrade hardware. Only other answer is to restructure how groups are being used/organized.

  3. mike1 member avatar

    mike1

    5 years ago

    I have access to the db but I dont have ftp access so uploading files would be the issue. Now its just a matter of figuring out what I need to optimize or what indexes to create.
    Maybe theres a better way to structure the groups. I've got a 3 tiered structure.
    The outline below shows the structure. One sales user can be assigned to multiple physicians that each have their own records assigned to them. Each sales person could have 15-20 physicians under them and needs access to all of the records associated to those physician security groups.

    Sales User
            Sales User Security Group(used for custom layout)
        Physician A - Security Group
            Record 1
            Record 2
        Physician B - Security Group
            Record 3
            Record 4

  4. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    Hello Mike,

    Were you able to tune this up? Oddly enough adding a PHP opcode cache may also help out here such as APC or Memcache. Unrelated to the database, but I've seen that help when Apache and MySQL are on the same server.

  5. mike1 member avatar

    mike1

    5 years ago

    Yes as it turns out when I had been doing imports and I deleted data, there ended up being about 9 million records in the securitygroups_records table. I cleaned it up and got it down to 300K records and its running much faster.

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      5 years ago

      Holy cow! Well glad you got that cleaned up.

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
Rating
  • "The features are exactly what is needed to turn a SugarCRM CE into a Teams based CRM."

    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.