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.

96% improvement.

Comments

*This post is locked for comments