locked
MS Dynamics CRM 4.0 R12 - Filtered View Performance RRS feed

  • Question

  • I have a report written using Reporting Services looking at Activities between 2 dates. I have used the filtered views as these contain all of the security I need to make sure the right people see the right data. My only problem is that the performance is dreadful.

    So, in Test I replaced the FilteredActivityPointer view with ActivityPointer view and the report ran in a few seconds.

    I have analysed the Filtered view and it contains lots of functions and joins and does not seem very optimal for reporting across larges sets of data (we have 500,000+ activities).

    I cannot change the filtered views so my only weapon is indexing and after looking at the query plan I can’t see how this will help significantly.

    So, I was about to start creating my own “Reporting” views that are a trimmed down and optimised “Filtered” view that is more suited to reporting.

    I would be interested in other ideas on using filtered views in reporting and the different ways people have improved the speed of reporting (I have already considered caching and snapshot, neither are appropriate).

    Tuesday, October 5, 2010 9:02 AM

Answers

All replies

  • Hi,

    I have the same problem with a post-R12 CRM environment running a custom report on the FilteredCustomerRelationship table. This table contains abount 130.000 records, but causes the report to run dramatically. When running the query within SQL Manager, it also takes up to 2 minutes to run. Using the default CustomerRelationship, it only takes 5 seconds for it to run.

    However, the standard views are blocked from CRM because only the filtered views can be accessed by either the CRMReaderRole and ReportingUserGroup roles. If I were to use the CustomerRelationship instead of FilteredCustomerRelationship (because of poor performance), this would mean customizing security on database level.

    Do you have any ideas for this?
    Its ok if users were the access the full CustomerRelationship, not using any CRM security roles. I can do this by using FilteredAccount and/or FilteredContact and join these with CustomerRelationship, which is open to the entire organization.

    Daniel

    Friday, December 10, 2010 1:38 PM
  • Friday, December 10, 2010 1:56 PM
    Moderator
  • Thanks for the update, although I have my thoughts on creating additional attributes in the base tables in order to increase performance. Editing the SQL database directly isn't supported by MS and I wonder how rollups or updating to CRM 2011 will react to this.

    In addition, I found the following to be a welcome workaround:

    - Update report query to use CustomerRelationship view instead of FilteredCustomerRelationship
    - Upload report in CRM and set to personal instead of organization
    - Share report with users who need to use the report using CRM sharing
    - Run the report as a SQL administrator (set datasource in Report Manager)

    Users running the report will now see all the data from my table (ignoring CRM's role based security which is set by Filtered Views), but this is ok since its an administrator report anyway.

    Friday, December 10, 2010 2:06 PM
  • i just wanted to raise the same issue again, in my case the report is created for the supervisor role, which having two separate regions ....

    so i cant use your workaround, i still need to use the filtered view.

     

    so if somebody has found a proper standard solution, please share it.

     


    e-life
    Thursday, January 20, 2011 5:16 AM
  • Try using query hints with the query.

    Here is the link on how to use them.

    http://msdn.microsoft.com/en-us/library/ms181714.aspx

    Thursday, January 20, 2011 6:28 AM
  • i didnt find that helpful, because the query optimization is based on the indexes, instead i created some indexes and things are very good now.

    thanks 


    e-life
    Thursday, January 20, 2011 6:38 AM
  • to whomever is facing the same problem in performance in dynamics crm report based on custom entities or user defined views:

    try this , it works , my report was taking 5 minutes to come, but now it takes 30 ~ 60 seconds ! 

     

    the key of the solution is that: crm doesn't create enough indexes for the custom entities,

    -because simply it doesn't know the business logic behind it-

    so i created the indexes on my custom entities, thanks for this post:

    http://www.powerobjects.com/blog/2010/11/03/optimizing-complex-report-performance-in-microsoft-crm-4-0/

     


    e-life
    Thursday, January 20, 2011 6:44 AM
  • just try to do index on the cutom attributes you are using in your report.

    but be care full while doing it.


    yes.sudhanshu

    http://bproud2banindian.blogspot.com
    http://ms-crm-2011-beta.blogspot.com
    Thursday, January 20, 2011 7:38 AM
  • Mouhanad I don't understand what you mean with supervision role and different regions.
    If some of the tables within your query require CRM business logic, you are able to use the filtered views for these tables, and just use the default views for non-required tables.

    The following example would have you see all activities (default view), but because I use the FilteredAccount table, I will only get those activities for the accounts which I am allowed to see in CRM. Thus not using the FilteredActivityPointer and increasing performance, but still having FilteredAccount to let CRM block out records.

    SELECT  act.activityid, acc.accountid
    FROM  FilteredAccount acc
    INNER JOIN ActivityPointer act ON act.regardingobjectid = acc.accountid 

    Thursday, January 20, 2011 8:18 AM
  • yes Daniel you are right, 

    but in my scenario the whole story is based on custom entities , XRM solution , which i want to make sure i am selecting the data with the permission....

    anyway the indexes was a very useful solution.

     

    thanks


    e-life
    Thursday, January 20, 2011 9:51 AM