locked
CRM 2011 Reports Poor performance, timing out on production RRS feed

  • Question

  • We have a report which is running fine on the Development and UAT environments but basically times out in production. So I ran a trace on the report in production and found out that because there are joins on 8 different filtered views it is repeatedly calling on the fn_xxxxxx functions that figure out the security on each record. I figure since there isn't as much data in the Dev and UAT enviroments, it performs ok there. So then I began trying to modify the report to use some of the regular views instead of the filtered since the results would be effectively the same with my changes. I got errors whenever I used a regular view. So my question is, what is the best way to improve performance drastically on this report? I've seen other places where they mentioned putting indexes on the custom entities but I'm not sure if that would help or not since it wouldn't change the fact that is running the fn_xxxxxx function on each record. I've been on teams before where we created a linked database and created our custom views which consolidated a filtered view and joined on some unfiltered views but this would be more complicated and not a quick fix.

    Saturday, July 27, 2013 5:41 PM

Answers

  • With that many joins on filtered views, it is likely that the largest impact on the performance is the implementation of security, and adding any other indexes won't make much difference.

    So, assuming you still need to join 8 views, you are only likely to get a drastic performance improvement by bypassing the filtered views. You've 3 main options for this:

    1. Directly access the non-filtered views. You'll need to grant users SQL permissions on these views, which is a security hole. This is also unsupported
    2. As you describe above, create custom views that reference the non-filtered views. You can keep more control of the security (if you can enable cross-database ownership chaining), but it's still unsupported
    3. Export the data periodically to another database to act as a data warehouse, and query this. This is supported (if you export via the filtered views), but is the greatest development overhead, and your data may not be up-to-date

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Bill Blancett Monday, July 29, 2013 5:17 PM
    Monday, July 29, 2013 3:09 PM
    Moderator

All replies

  • Have you created indexes on the linking columns?

    Monday, July 29, 2013 11:52 AM
  • Run the same query in SSMS, and include actual execution plan. Execution plan will tell you if you need to add any indexes to improve the performance.
    Monday, July 29, 2013 12:15 PM
  • With that many joins on filtered views, it is likely that the largest impact on the performance is the implementation of security, and adding any other indexes won't make much difference.

    So, assuming you still need to join 8 views, you are only likely to get a drastic performance improvement by bypassing the filtered views. You've 3 main options for this:

    1. Directly access the non-filtered views. You'll need to grant users SQL permissions on these views, which is a security hole. This is also unsupported
    2. As you describe above, create custom views that reference the non-filtered views. You can keep more control of the security (if you can enable cross-database ownership chaining), but it's still unsupported
    3. Export the data periodically to another database to act as a data warehouse, and query this. This is supported (if you export via the filtered views), but is the greatest development overhead, and your data may not be up-to-date

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Bill Blancett Monday, July 29, 2013 5:17 PM
    Monday, July 29, 2013 3:09 PM
    Moderator
  • Thanks guys, I ended up running the execution plan and it confirmed the issue was with the filteredviews issue. I also looked at the indexes and it looked like we already had indexes on the joined columns and other pertinent fields. I went ahead and tried out solution 1 of David's idea and changed the permission of the regular views and the report definitely runs faster. I now just have to test and make sure the results I get back are exactly as they should be. Preferably I'd like to implement solution 2 but that may be later down the line. Thanks for your help guys!

    Bill


    Thanks in Advance

    Monday, July 29, 2013 5:22 PM