My site is ad supported.

Increasing the Performance of Microsoft Dynamics CRM Reports/Queries Which Utilize Date Filters or Sorts


Description

This article describes how to gain a significant performance boost when querying a Microsoft Dynamics CRM database via SQL Server. This method with work for both SQL Reporting Services (SSRS) Reports backed by SQL queries or via ad-hoc SQL queries.

Conventional Method

Let's assume you want to pull all activities created in a date range sorted by the date created. You might have something like this:

SELECT createdon
    ,[subject]
    ,createdbyname
FROM FilteredActivityPointer
WHERE createdon BETWEEN @StartDate AND @EndDate
ORDER BY createdon ASC

This query hits the FilteredActivityPointer view which binds together several sub tables for convenience in resolving linked record names and honors record security. Usually this type of query will perform quite well; however if you have a date condition or sort (either in your WHERE, JOIN, or ORDER BY statements) included in your query then you may see the query take orders of magnitude longer than without them. For example, if your CRM ActivityPointerBase table is extremely large (hundreds of thousands to millions of records) then this query could take serveral minutes to complete.

Enhanced Performance Method

A trick you can use when date fields are included in your source query in a filter and/or sort condition is to apply the action on the CRM base tables instead of the CRM filtered views. For example, rewriting the above query like so would return the exact same results significantly faster:

SELECT FilteredActivityPointer.createdon
    ,FilteredActivityPointer.[subject]
    ,FilteredActivityPointer.createdbyname

FROM ActivityPointerBase
INNER JOIN FilteredActivityPointer
    ON ActivityPointerBase.ActivityId = FilteredActivityPointer.activityid

WHERE ActivityPointerBase.CreatedOn BETWEEN 
    -- Need to convert the dates to UTC when querying the base base tables.
    DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), @StartDate)
    AND 
    DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), @EndDate)

-- Ordering should also use the date value of the base table.
ORDER BY ActivityPointerBase.CreatedOn ASC

By INNER JOINing the FilteredActivity view we get all the benefits of the record security enforcement but by simply moving the date conditions to the CRM base table then we get the performance boost.

Important Notices

This method is not without a catch however. It is important to remember that CRM base tables store date values in UTC, so when filtering by a date field in the base table this must be taken into account. This is the reason for the DATEADD manipulation of the @StartDate and @EndDate parameters. For each of the parameters, we are calculating the difference in minutes on the server between the system time and UTC time and adjusting the parameter value accordingly.

As long as you remember to convert the date filter values to UTC you should be fine for the most part; however there could be minor issues with:

  • Users accross different time zones. Keep in mind, the UTC conversion executes on the server, so there is potential for users in different time zones to get slightly different results (for cases where minutes and hours may matter).
  • Daylight savings. I haven't run into these but I can see how this opens the door to potential quirks.


My site is ad supported.

Full Test Query - Try It Yourself

Here is script you can copy-paste to try it yourself.

/* CRM Dynamics Performance Enhanced Query Test
   by Jason Faulkner

   A full description of this method is available on my website.
   http://jasonfaulkner.com

   This demonstrates how utilizing base tables to filter or sort on date fields
   yields a significant performance boost.

   Uncomment the query you want to run and compare the results.
*/

DECLARE @StartDate datetime = DATEADD(day, -7, GETDATE())
	,@EndDate datetime = GETDATE();

/*
-- Traditional query using only the filtered view.
SELECT createdon
    ,[subject]
    ,createdbyname
FROM FilteredActivityPointer
WHERE createdon BETWEEN @StartDate AND @EndDate
ORDER BY createdon ASC
*/

/*
-- Performance enhanced query utilizing date filter and sort on the base table.
SELECT FilteredActivityPointer.createdon
    ,FilteredActivityPointer.[subject]
    ,FilteredActivityPointer.createdbyname

FROM ActivityPointerBase
INNER JOIN FilteredActivityPointer
    ON ActivityPointerBase.ActivityId = FilteredActivityPointer.activityid

WHERE ActivityPointerBase.CreatedOn BETWEEN 
    -- Need to convert the dates to UTC when querying the base base tables.
    DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), @StartDate)
    AND 
    DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), @EndDate)

-- Ordering should also use the date value of the base table.
ORDER BY ActivityPointerBase.CreatedOn ASC
*/