of the
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.
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.
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
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.
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.