Jesper Niedermann's .NET Blog
about .NET and related technologies RSS 2.0
# Sunday, 07 March 2010

In Microsoft CRM you have no choice when doing updates to the database. You have to use the CRM Web Service.

Microsoft does not support direct SQL updates of the underlying SQL Server tables, because of the behind the scenes CRM magic.

The Web Service is OK for small amounts of data. But recently I had a case where I had to code an import of a spreadsheet with up to 100.000 rows into CRM.

This would result in 600.000 web service calls, since there was 5 retrieves for each updates. The first tests showed that each web service call would be about 1,5 seconds even though they were done internally on the CRM server a fast quad-core 64 bit machine. This would be 900.000 seconds or 250 hours, more than 10 consecutive days.

Too slow for our particular business need.

Fortunately UnsafeAuthenticatedConnectionSharing came to the rescue.

proxy.OnGetWebRequest += (sender, e) =>
{
e.WebRequest.ConnectionGroupName = InitUserId.ToString();
e.WebRequest.UnsafeAuthenticatedConnectionSharing = true;
};

On the CrmService proxy you set the UnsafeAuthenticatedConnectionSharing to true. This just means that other calls to the web service will use the same connection to the database, and without reauthenticating. Which means that the next call with UnsafeAuthenticatedConnectionSharing set to true, will use the same connection. And will be assumed to be the same user. ConnectionGroupName is a convenient way to split up the connections so that we don’t accidentally reuse connections between different users. This can be done simply by using the guid of the current CRM User.

And now the important thing. This makes the web service call take 3/100 seconds. About 50 times faster ! The 600.000 calls can be done in 5 hours. Of course this speed was only attained because all calls where done internally on the CRM Server.

So why are these two settings not the default ? I don’t know. Perhaps because if you have a site with millions of users the amount of memory used up by each connection will be too big. But on a typical CRM site I would guess there would not be enough users to make this a problem.

But to be safe, if you just use the 2 properties in the areas that need performance with little concurrency between users you will be OK.

This and other performance best practices for CRM can be seen on technet. But I don’t think any will outperform this one.

Sunday, 07 March 2010 14:39:19 (GMT Standard Time, UTC+00:00)  #    Comments [0] -

Comments are closed.
Archive
<2017 October>
SunMonTueWedThuFriSat
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234
About the author/Disclaimer
I am a software architect with focus on Microsoft Technologies. I have been working with these in different large companies since 1995. I am currently employed at UVdata A/S.
Here is my View Jesper Niedermann's profile on LinkedIn

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Privacy policy
The privacy policy of this site.

© Copyright 2017
Jesper Niedermann
Sign In
Statistics
Total Posts: 28
This Year: 0
This Month: 0
This Week: 0
Comments: 32
All Content © 2017, Jesper Niedermann
DasBlog theme 'Niedermann' created by Jesper Niedermann, based on 'Business' created by Christoph De Baene (delarou)