Tips for better performing Microsoft Dynamics CRM SQL reports in SSRS
For Dynamics CRM On-Premise, Microsoft SSRS-based reports can be developed either as SQL or FetchXML based reports. SQL is the more common (old-school) style of report development and is probably more familiar with CRM report developers.
There are a handful of common mistakes or traps that anyone writing CRM reports can fall into - and we've all had to tackle 'fixing' a report that runs so heavy it dims the lights when you try to load it.
Fortunately, these best practice are pretty straightforward and will help you develop reports that run quickly and effortlessly on your server.
Let's get started.
Streamline the report requirements
We’ve all seen SSRS reports that attempted to behave like an application unto themselves. – Lots of scripting, dynamic formatting, and enough fields that the report had to be printed in landscape on legal at barely legible font size or on two pages taped together! Seriously, streamline that beast – make two reports that serve the purpose and add drill-down to a second report for details.
SSRS subreports are also in allowing you to re-structure the data in a way that decomplicates the query.
The fundamental idea is that while powerful, a SSRS report can collapse under its own weight if too much is asked of it. – Re-think / restructure to ensure the report meets its need and determine if a few purpose-driven reports would meet the needs better than a single report that compromises performance to try and meet all needs.
Limit the number of Filtered Views
Your SQL in the report can be one of the biggest pinch points. I recently saw a report with 10 different joins to different CRM filtered views on non-indexed fields. The performance was quite poor (actually it mostly just timed out).
Every join on a filtered view creates additional required effort in order to filter the data out according to the security of the user. If you have more than 3 or 4 filtered views joined together in a query, the performance will degrade quickly. Keep the query simple and don’t try to boil the ocean with one query.
Make CRM/SQL do the heavy lifting
An all-too-common mistake is to return ALL the data and then apply a filter in the report. Although SSRS filtering is nice to have – by the time the filter is applied, a vast amount of data has been retrieved and supplied to the report only to be discarded. This thrashing db CPU cycles, DB memory and tempdb space generally makes the report slower to display. It’s far more efficient to have the source query filter out the data you don’t need and provide the report only the most useful data.
CRM’s pre-filtering does an nice job of allowing you to limit the data retrieved to just the data in a specific context.
Parameters for Performance
Much like using CRM’s Pre-Filtering, SSRS Parameters in the report can be passed to the source query and re-request the specific data needed. This is far more efficient than returning everything to the report and only then applying a filter to what is displayed.
Make sure the default query returns only the most common requirements and then only a minimum. The default could be similar to the “My Contacts” view or “Last 3 days of Transactions” as opposed to the default report being “All Contacts” or “All Transaction.” In so doing, the report will run in ‘minimum’ mode by default, and then the user can adjust the parameters to open up the report for more data.
Sharpen the Saw
I’ve re-written several of my own reports over the years and inevitably I find artifacts of requirements that were added and later removed.
In hind-sight some T-SQL queries look like weird science projects. Maybe they made sense given the requirements at the time they were written, but now is the time to restructure them to leverage improvements in SQL 2008 or SQL 2012. Features like Common Table Expressions (CTEs) are quite powerful when working with CRM hierarchical data– and CTEs weren’t available back in the dark ages of SQL 2005 – if you haven’t looked into how they can help roll-up CRM data recursively, you should.
Work with your DBA to ensure the CRM database is finely tuned and that your query is joining / sorting on indexed fields wherever possible. Anytime you can take advantage of indexed field in a join to avoid a tablescan, an angel gets its wings.
SQL Server is a powerful tool, but sometimes the queries buried in a report are so poorly constructed that SQL can’t open up and deliver the goods like you’d want it to. Take the time to refactor and upgrade the Source Query in SQL-base Dynamics CRM reports to get maximum performance.
Did one of these help you out – or do you have a great tip I missed? – Please leave a comment – it's always great to pick up a new tip from a fellow traveller!