Tag Archives: reporting

CRM Reporting Tips and Tricks for Faster Query Performance #2 – Using Table Variables

If you have a report query that joins multiple data sets together then consider using table variables to speed up query performance.

Here’s an example:  I have a report with a Sales Team parameter that allows the user to select one more Sales Team to report on.  The report then displays the open appointments, quotes, and opportunities owned by the Account Managers in those Sales Teams.

Typically I would write my query like this:

select metric_name = ‘Appointments’,  metric_count  = count(*)
from filteredappointment
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)

union all

select metric_name = ‘Opportunities’,  metric_count  = count(*)
from filteredopportunity
where statecodename = ‘Open’
and createdby in (select systemuserid from FilteredSystemUser where new_saleteamid in @Team)

union all

select metric_name = ‘Quotes’,  metric_count  = count(*)
from filteredquote
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)

But recently I’ve found with more complex queries I’ve needed to take a slow performing component of my query and specifically direct SQL to process that component and to then place the results in a table variable for me to refer to later in the query.   In the above query each of the 3 query components refer to the list of users that should be reported on.   By using a Table variable I can query for and store that user list as the first step in my query and then join that result set into each of the 3 queries.   This approach looks like this:

DECLARE @USERS_SELECTED TABLE(USERS varchar(50))

INSERT INTO @USERS_SELECTED (USERS)
(select systemuserid from filteredsystemuser where new_saleteamid in @Team)

select metric_name = ‘Appointments’,  metric_count  = count(*)
from filteredappointment a
inner join @USERS_SELECTED x on a.createdby = x.USERS
where statecodename = ‘Open’

union all

select metric_name = ‘Opportunities’,  metric_count  = count(*)
from filteredopportunity o
inner join @USERS_SELECTED x on o.createdby = x.USERS
where statecodename = ‘Open’

union all

select metric_name = ‘Quotes’,  metric_count  = count(*)
from filteredquote q
inner join @USERS_SELECTED x on q.createdby = x.USERS
where statecodename = ‘Open’

Now the above example is very simplistic, but this approach has proved very successful for me.   If your SQL statement contain a number of selects which then get aggregated up and joined together, then you will benefit from this too.   If your query is performing poorly run each of the selects in isolation, find the slow running one and try running it at the start of your SQL and place the result set into a table variable.  You might start to find that these sorts of complex SQL statements become a lot more readable as well if you follow this approach.  I have one query where I have followed this approach for each of the ten selects embedded in the query giving me ten of these work tables which the query then joins together in a very simple SQL statement at the end of the query.  Very easy to read and very easy to troubleshoot.

I hope this helps.

Advertisements

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