web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Dynamics CRM SSRS perf...
Microsoft Dynamics CRM (Archived)

Dynamics CRM SSRS performance

(0) ShareShare
ReportReport
Posted on by renton 205

Hi Everyone

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 ?

best regards 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Michel van den Brink Profile Picture
    Michel van den Brink 4,697 on at

    Hello reimei,

    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 :-)

  • David Jennaway Profile Picture
    David Jennaway 14,065 on at

    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

  • renton Profile Picture
    renton 205 on at

    Michel

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

  • renton Profile Picture
    renton 205 on at

    David

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans