Tip #347: How to use datetime fields in WHERE clause

Gotta love user Q&A sessions, like the one at Convergence 2015 that I had a privilege to take part in. Questions from users are always refreshing, very often challenging and sometimes they are just the reminder that any small bit of information that one possesses is not necessarily the common knowledge.

On this occasion question was about poor performing reporting queries dealing with the activity records. There were tons of good suggestions but one thing that the user seems to have missed is how to properly use CRM datetime fields in the queries. Let’s say we’d like to filter all activities that started last month. The first query that comes to mind:

select activityid, subject 
from FilteredActivityPointer
where scheduledstart >= '1-feb-2015' 
  and scheduledstart < '1-mar-2015'

Reasonable? Yes. Best performing? Heck, no. The problem is that filtered views, among other things, convert datetime CRM fields into the timezone of the current user. To do that, datetime column names have suffix “utc” added to them and the original names are used to display local time equivalent. For example, every entity in fitered view would have createdon (local time) and createdonutc (the actual value stored in the database).

How this local time is calculated? Using scalar-value T-SQL functions. What chances are of the scalar-value functions in WHERE clause using an index? None. Hello, table scans.

The Spießrutenlaufen Rule of Filtered Views

Whenever CRM datetime field needs to be in the WHERE SQL clause, the utc version of the field should be used.

The above would become:

select activityid, subject 
from FilteredActivityPointer
where scheduledstartutc >= 
            dbo.fn_LocalTimeToUTC('1-feb-2015') 
  and scheduledstartutc < 
            dbo.fn_LocalTimeToUTC('1-mar-2015')

How much do you save? YMMV but on my test server against random CRM organization the execution time of the first query was 2280ms, whilst the second has finished under 80ms.

Like a boss meme96% improvement.

Tweet about this on TwitterShare on Facebook0Share on Google+0

7 thoughts on “Tip #347: How to use datetime fields in WHERE clause

  1. Chetan says:

    Hi CRM Guru,

    I tried to test this @ my end but could not see performance improvement rather it got hampered badly.

    I tired to run following query in my CRM (2013) database (on-premise) using following query

    SELECT accountcategorycode,accountcategorycodename,accountclassificationcodename,accountid,address1_addressid,
    address1_addresstypecode,address1_city,address1_country,name,statecode,statuscode
    ,orc_countryidname,orc_stateidname
    FROM FilteredAccount a
    WHERE a.createdon > ’01-Jan-2011′

    It took 25 sec to execute above query. But when I tired the same query with the UTC modification as per above blog it took a hit and took 1 Min 52 Sec to execute.

    Please note that I cleaned data buffers before executing each query.
    ( DBCC DROPCLEANBUFFERS )

    In the first approach, SQL server has to perform implicit conversion only while for the second query it has to call the function for each record along with implicit conversion thus the bad performance, probably.

    Can you please confirm the behavior ?

    • Can you show the exact query you run with UTC dates? I have never had query based around UTC dates run slower.

      • Chetan says:

        Hi George,

        I used below query

        SELECT accountcategorycode,accountcategorycodename,accountclassificationcodename,accountid,address1_addressid,
        address1_addresstypecode,address1_city,address1_country,name,statecode,statuscode
        FROM FilteredAccount a
        WHERE a.createdonutc > dbo.fn_LocalTimeToUTC(’01-Jan-2011′)

        I used it both ways i.e. with and without clearing buffer. It was slower in both the cases.

        • Hi Chetan,

          looks like your server is a bit temperamental in regards to the functions. Try this:

          declare @mincreate datetime
          select @mincreate = dbo.fn_LocalTimeToUTC(’01-Jan-2011′)

          SELECT accountcategorycode,accountcategorycodename,accountclassificationcodename,accountid,address1_addressid,
          address1_addresstypecode,address1_city,address1_country,name,statecode,statuscode
          FROM FilteredAccount a
          WHERE a.createdonutc > @mincreate

          • Chetan says:

            Hi George,

            I’ve tried this before and it does make sense now because it is somewhat similar to the first query because function is not getting called for each row here.

            Only thing that differs from first one is that SQL server won’t have to perform implicit conversion here as we are comparing with data from DATETIME variable.

            Can you please try variants from the blog entry with DBCC DROPCLEANBUFFERS before each execution and you might see the difference ?

          • The result is roughly the same, i.e. I’m seeing performance improvement.

  2. Chetan says:

    Also I think, the date gets stored in the UTC format in CRM so when the query is executed using filtered view it returns it into users local time zone. Thus ideally we don’t have to use function to convert it into Local time zone.

    Please confirm.

Leave a Reply

Your email address will not be published. Required fields are marked *