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.
#744 - Queries running slowly
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
-
"It is not working. Below error is coming. "Specified directory '' for zip file 'upload://6.5.24_SecuritySuite_v3.1.12.zip' extraction does not exist."" - MaxMobility
Read More Reviews
- Most Recent Love from Users
kacsoristvan- damien8105
- anri
- Administrador
- Stefano1
- einkauf2
10 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.
10 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.
10 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.
10 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
10 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.
10 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.
10 years ago
Holy cow! Well glad you got that cleaned up.