Tuesday, November 30, 2010

Connecting to a Web Service, you CAN set timeout on the client...

This information would have been helpful, ohhhhh, a few months back, after countless (and random, of course) timeout occurrences and lots of hair loss and shoulder shrugging.

Background: I built a .net console application that downloads our HR data from a “major” HR provider. This project consisted of security certificates (purchasing and exchanging) on all provider server as well as client server; logins and passwords and research, research, research. The company provided sample code, which basically showed how to query one module at a time and one module at a time.

But my task was to download all the data for all employees every day, including data for terminated employees. We have over 600 active employees, which employee has, obviously, multiple rows in most, and sometimes more than one, in each module of data. I call them modules, for lack of a better term. This equates to downloading approximately 70,000 records once a day. The connection and download takes approximately 9 minutes. Of that 9 minutes I’d say a few are spent waiting for a response, I’d guess that the actual workings take about 5 minutes, not bad I think but I don’t have much to compare it to at this point.

The provider does not provide a “changed” mechanism (or at least they did not tell me they have that mechanism – although many of the hurdles I came across on this journey were questions that when asked multiple times were simple answers – maybe the question just had to be asked of the right person?). Anyway, I stray… One of the first errors I would receive, randomly, would be a timeout attempting to download data from a module. And one particular module would always timeout so that I had to break up the requests by choosing an additional filter.

So, seemed to be pretty stable with a timeout occurring once a week or two, I could handle that, especially since when I asked them the question and sent them the error messages, I was given the response “it’s not on our side”… Time passed, and timeouts increased to a point where I couldn’t get that one module, previously broken down into four filtered requests, to even budge.

I sought empathy from our network engineer to monitor the traffic back and forth and also re-wrote most of the application to save the SQL insert commands to a lengthy string and execute after each module completed, so I wasn’t shooting off single SQL insert statements continually. Still nothing seemed to help. I narrowed it down, with debug printable statements, and could show that there was over a 1.5 minute delay from when a request was sent and a response on that one pesky module. Everything I’d researched stated the timeout value was set inside IIS on the provider server.

After compiling my error list and debug statements into a very convincing e-mail to our HR application provider, I finally received a response from them telling me to up my timeout setting to 5 minutes… Huh?

And this is what you get with someone who learns C# from a book and sample code, over having someone who is an expert mentor you…

Simple addition of this line in each module call to download data:

[ServiceName] proxy = new [ServiceName]();
proxy.Timeout = 300000;

Yep, just that simple. Months of frustration being fixed with 23 characters. Sometimes you feel like an idiot at the end of the day. Live and learn.

Thursday, November 4, 2010

Determine Size of All Tables in a database...

This script has come in handy a few times, enough to warrant me posting this so I don't have to remember where I put it. Elephant wishes...