Re: Performance is slow when calling web services in SQL CLR

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi ,

im afraid Im not familiar enough with the internal workings of a servicepoint to answer your questions. All i know is that by upping the defaultconnectionlimit sorted my issue. I do this on each call of the Clr routine.


--

Dave Ballantyne
http://sqlandthelike.blogspot.com/

Naomi wrote:
Hi Dave

Thank you!! That seems to sort it.

Quick questions - Is setting this value once will do? or does it need to be set every time you call sp? also - is there a way to configure it in SQL, or do you have to do it programatically?

Lastly - do we need to set this property whenever we use web services, not just in sql clr sp?

Thanks

N
"Dave Ballantyne" wrote:

Hi ,

in the CLR routine add

System.Net.ServicePointManager.DefaultConnectionLimit = 9999


I think im correct in saying the Default is 2, so its probably waiting to get a connection

Dave

--

Dave Ballantyne
http://sqlandthelike.blogspot.com/

Naomi wrote:
Hi there,

We have SQL CLR stored procedures that call web services.

When the web service calls take long (a few seconds or more) then it affects the performance of SQL DB of that sp completely, and slows down other CLR stored procedures. Stored procedure in other databases on the server seem to work fine.

To test we created a web service call that just sleeps for a while (length of sleep based on input parameter). We also created a stored procedure that calls that service. When we are calling the test sp under relatively light load (every 10 seconds, about 10- 20 times) then sql slows down completely. Stored procedures that do not use this service slow down as well. The problem is not in the service side as when accessing all of them directly and not via CLR sql sproc, they work fine.

Any idea what is causing this performance issue and how it can be sorted?

Thanks!

N
.

Relevant Pages