Directly injecting data into the CRM 4 SQL database – 1100% Faster!

Gayan Perera, 18 January 2011

Let me start by saying this is not recommended and not supported by Microsoft.

We’re replacing an old financial/lending application with Dynamics CRM 4; everything is going great so far except for the fact that it takes way too long to run a process that gets run every three days which processes 100,000+ records.

*

When these records are being processed calculations are run, cross referenced with other records as well as new records being created, on average it takes about 90 seconds to process roughly 1300 of these records on an 8 core machine.

Unfortunately a 2 hour wait is unacceptable and due to the integration limitations we’re not able to process these in the background, so it’s time to use raw SQL.

We’ve been working with CRM since 2003 and this is the first time we’ve had to resort to this, in other instances we got around the slowness by differing and processing in the background as records came through but this time none of those techniques were useful due to the nature of other systems involved.

**

Doing this reduced the processing time from 2 hours to less than 10 seconds and reduced C# code by 60% and ended up with ~100 lines of SQL code.

Impersonation

We created a new SQL user called <instance_crm>, allocated the CRMReaderRole and EXECUTE AS (impersonation) permission.  Impersonation is required to access the data because of the way CRM controls access.

Example stored procedure

CREATE procedure [dbo].[Test_ProcessTransactions]
      @p1 datetime,
      @p2 datetime
WITH EXECUTE AS 'magnetism\test_crm'
AS
BEGIN
END

Dates and Times – UTC / Local

If you query the base tables you’ll notice that all datetimes’ are stored in UTC, there is a scalar value function in CRM that’ll convert to local time for you, you’ll need to do this if you’re doing any sort of date comparisons by passing a datetime into the stored procedure. (see example above)

You can query the filtered views to get around this. Another advantage of filtered views is that it filters out the deleted records for you.

dbo.fn_UTCToLocalTime(<utc_time>)

Required fields

Columns in the CRM database are marked as null (even createdon, createdby) but these are required, if you don’t specify these you’ll get strange ‘Please contact your system administrator’ errors.

If you’re inserting $ values make sure to insert into the _Base column.

If you’re entering datetimes make sure they are in UTC or use getutcdate() as current SQL server time.

It would be great to hear how others deal with large amounts of data processing.

* Image 2 (old man) from http://www.belmert.de/kunde.gif
** Image 3 (turtle) from http://www.sbi2-4u.com/image-files/finish-line.gif