CRM Reporting Tips and Tricks for Faster Query Performance #1 – UTC Date Fields

Anyone writing CRM reports will be familiar with CRM’s handy filtered views.   The requirements these views were designed to meet were quite complex.  They needed to filter the data returned based on CRM’s record level security and they needed to localise the data returned based on the user’s time zone and language preferences.   They achieve all this, but at a price, performance.   You need to lift your game with CRM and put some serious analysis into your queries and into what SQL is being asked to do to deliver the responses to your queries.

If you are suffering from slow running reports and your reports are selecting their data based on dates then try switching your queries to use CRM’s native UTC fields.  You probably followed your instinct and used CRM’s handy localised date fields rather than the native UTC fields – e.g. you wanted to return Opportunities created between two dates, so you wrote something like the below (with start and end date variables temporarily added, to be replaced later by report parameters):  

DECLARE @STARTDATE as Date
DECLARE @ENDDATE as Date
SET @STARTDATE = ‘2010-04-07’
SET @ENDDATE = ‘2010-04-11’

SELECT *
FROM FILTEREDOpportunity
WHERE CONVERT(DATE, CREATEDON) BETWEEN @STARTDATE AND @ENDDATE

In order to return your result set SQL Server has to check each Opportunity record in the database in turn, taking the value in it’s OpportunityBase.CreatedOn field (which is the creation date in UTC time) and run that through a function that converts that UTC value into your time zone so that it can determine whether that value falls in the last 30 days.   There’s a fair bit of processing required there and you are stifling SQL’s ability to use its indexes.  

Here’s a much more efficient query: 

DECLARE @STARTDATE as DateTime
DECLARE @ENDDATE as DateTime
SET @STARTDATE = ‘2010-04-07’
SET @ENDDATE = ‘2010-04-11’

DECLARE @STARTDATEUTC as DateTime
DECLARE @ENDDATEUTC as DateTime
SET @STARTDATEUTC = dbo.fn_LocalTimeToUTC(@STARTDATE)
SET @ENDDATEUTC = dbo.fn_LocalTimeToUTC(CONVERT(DATE, (@ENDDATE + 1)))

SELECT *
FROM FILTEREDOpportunity
WHERE CREATEDONUTC BETWEEN @STARTDATEUTC AND @ENDDATEUTC

This query takes the initial start and end dates that are to be used and determines the equivalent UTC datetime values.  It then compares CRM’s native UTC createdon field to this UTC date range to find the records.  Where as before SQL Server had to translate the createdon date of each Opportunity record it can now use the native value and the only conversion is required is the initial one-off translation of the parameter fields. 

I have found this type of approach to generate significant improvements.   But, you need to be very careful with your logic to make sure you don’t miss any records created either early or late in the day.  I suggest you have your query initially return your datetime fields in both UTC and localised formats and make you test thoroughly.

Hope this helps!

Regards,

Gareth

Advertisements

3 thoughts on “CRM Reporting Tips and Tricks for Faster Query Performance #1 – UTC Date Fields

  1. Shaunna Shelton

    Hi Gareth,

    Awesomely helpful blog! I just stumbled across this older post regarding using UTC dates and had a quick question for you. There is a caveat I’m running into regarding this issue.

    Our Microsoft CRM implementation partner has indicated stored procedures are not supported, so all our custom SSRS reports are built using embedded SQL. When embedding SQL in SSRS you cannot use the DECLARE statement, which would leave me having to place the dbo.fn_LocalTimeToUTC(@STARTDATE) in the WHERE clause which would still impact performance.

    I’m wondering if there is any way to get around that issue. Had we been able to utilize stored procs it would be no problem to use the above code solution.

    Thanks,

    Shaunna

    Reply
  2. Shaunna Shelton

    Just a follow-up to my last post…

    I created a dataset called UTCDateRange with the following query that takes the user selected StartDate and EndDate param values, and then passes them through the function:

    SELECT dbo.fn_LocalTimeToUTC(@StartDate) AS StartDateUTC, dbo.fn_LocalTimeToUTC(@EndDate) AS EndDateUTC

    Then I have two report parameters called StartDateUTC and EndDateUTC that are hidden which I set to the new UTC dates from the UTCDateRange dataset. I can then use those in the query I run on against the data.

    Thanks for the awesome post! I’m jazzed to finally be able to speed these CRM queries up and utilize the indexes on the UTC date columns.

    Thanks again,

    Shaunna

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s