Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2022 Release Wave 2Check out the latest updates and new features of Dynamics 365 released from October 2022 through March 2023
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
my crm is on-premise(include web-server,db-server and report server).
I create a SSRS report and deploy it in the CRM.
I use common table view instead of filterXXXView in the SSRS report.
When I use system admin user to view the Report in CRM,it takes 30 seconds.
But when I use a common role user to view the Report,it takes a long time(120 seconds or even more).
Does anyone know why the performance difference between the system admin and the common user ?
The security model of Dynamics CRM (and Dynamics 365) applies Security Role based restrictions via the FilteredXXXView views in SQL Server.
For a user with the System Administrator role the application can take a shortcut by not needing to look up any specific privileges because a System Administrator is allowed to view all records (there are exceptions when involving Field Security profiles)
For a regular user, much more logic is applied to make a fine-grained assessment of which records are allowed to be viewed, based on all of the applied Security Roles, which causes the system to slow down, relative to an System Administrator.
This difference is usually not as dramatic as you are experiencing though. A usual suspect of slow reports is frequent use of the 'Share' feature to share records between users. Where one specific regular user is privileged for view a record, another is not and the first user chooses to 'Share' the record.
Where the 'Share' feature is frequently used a system table called PrincipalObjectAccess grows enormously, to keep track of all of these sharing rules. The PrincipalObjectAccess table is consumed by the FilreredXXXView views, causing it to slow down.
Hopefully this gives you some insight.
Let me know if you have additional questions and if you found my answer helpful, please mark it as such :-)
When accessing data via SQL, CRM security is only applied via filtered views. If you're bypassing these and accessing the table views, then the difference won't be due to having to apply CRM permissions (unless you explicitly reference any of the other CRM SQL objects that are user-context specific).
I can't think of any user-specific reason for such a performance difference - are the times consistent ? If not, it may instead be down to the state of SQL (e.g. cached query plans).
I'd suggest using SQL impersonation (Execute As) to try running the query under different users to see if the difference is within the SQL query execution or not
Thanks for your reply.
I found I used two filteredView in my report eventually.
So I replace the view ,and the performance have some improvement(a little...)
Thank you for your reply.
There are no DBA in the project,so I have to tuning the DB performance by myself.
The SQL cached query plan might be a good idea to check DB performance.
Business Applications communities