Tuesday, July 01, 2008

Updating Records in Mass via the SDK

For some time I've been updating (or importing) records in mass via the SDK (when I say mass I mean some where in the hundred's of thousands). However, whenever I have tried to do this I have run into an issue with my processes over taking all of the socket connections. Whether via webpage or windows app, either way the SDK would overrun the connections that IIS would allow. So, I would have to plan out my imports or updates through the SDK accordingly with the most records I could run before the connections get all used. Then once I had run that maximum number I would stop, run an IISRESET and then start again. Needless to say it made these kinds of jobs even longer and more tedious.

So, after quite a bit of searching and testing I have found what I believe to be the best way to mass update records in CRM via the SDK without locking up sockets or connections. I have seen people who like to update the registry by changing the following values:

HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort to be 65534
And
HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\TCPTimedWaitDelay to be 30

And that works most of the time but I personally don't think that it's 100% reliable. Even after making these changes I have run into the same problem of locking up the sockets and connections. Here's the reference I found on this:

http://blogs.msdn.com/dgorti/archive/2005/09/18/470766.aspx

The best solution I found however is something that is to be implemented in your code. I have to give credit to AndrewN23 who posted his code in the groups. But the code is as follows:


int RETRY_ITERATIONS = 50;
int p = 0;
do
{
try
{
crmService.Update(entity);
p = RETRY_ITERATIONS;
}
catch (Exception e)
{
p++;
if (p >= RETRY_ITERATIONS)
{
throw (e);
//WriteToFile("updateContact error: " + e.Message + " " + e.InnerException);
}
System.Threading.Thread.Sleep(500);
}
} while (p < RETRY_ITERATIONS);

Basically the code goes and catches the connection error and waits for a connection to be freed up. Once a connection is freed up it continues on. I ran this against a group of more than 4,000 records as my first test and then ran it against more than 50,000 records without any issue.

One comment I will make on this however, is that if your update code triggers any other callout or plug-in code that will affect your update code and by using up connections that are already limited. I had to go through and add this block of code to ALL methods of my custom code that was triggered by my update code. When I only applied this code to my mass update code I would get the socket connection error showing up in the event log from my other custom code (callout or plug-in). Once I updated the rest of my custom code everything worked like a dream.

Hope this helps someone else out.

David Fronk
Dynamic Methods Inc.

6 comments:

ghchinoy said...

David, why not let the crmservice share connections between accesses using a webservice parameter:

crmService.UnsafeAuthenticatedConnectionSharing = true;

We bulk load data via the SDK all the time and this has helped immensely, w/o any of the regkey changes.

Dynamic Methods said...

ghchinoy,

I actually have not had anyone present that option as a solution for bulk loads. I will definitely have to try it out on the next bulk load I do.

Thanks for the tip.

David Fronk
Dynamic Methods Inc.

ghchinoy said...

David,

Let me know if you try it out and how it goes!

- Hussain

Dynamic Methods said...

Hussain,

I just included it in a mass merge of Accounts and Contacts and it ran beautifully. I'm going to try it out some more but it sure seems to work like a champ!

Thanks for again for the help.

David Fronk
Dynamic Methods Inc.

Brandon said...

Yes, you definitely want to enable crmService.UnsafeAuthenticatedConnectionSharing = true;

I've experienced serious issues with updating and retrieving records in Mass also (ie. any recurring/looped web service calls). When monitoring the Server's socket connections it was apparent that a single call to the method in question was killing the server by instantly blowing up the socket connections.

The symptom was that the Web Site calling the Web Service would simply trap an error vaguely saying "Service not available" or something to that effect.

I finally discovered this itty bitty line of code in the Microsoft Best Practices guide (forget which one exactly) after hours of searching.

The net effect is that it actually enables full Connection Pooling support that as most .Net developers tend to assume is on by default.

You'll also notice an impressive bump in the speed of your custom code with this enable also.

Dynamic Methods said...

Thanks for the feedback on this, it's good for everyone to know.

David Fronk
Dynamic Methods Inc.

Post a Comment