Performance is slow when calling web services in SQL CLR

[Naomi]

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

[Dave Ballantyne]

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]

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]

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.

[Naomi]

OK, Thanks anyway! that helped me a lot!!

[Naomi]

OK. Thanks anyway!! that helped me a lot!!

[TheSQLGuru]

I highly recommend you decouple the web service call if you can. Perhaps a
service broker app. Something asynchronous. Otherwise your database apps
can become blocked by the locks held for extended durations while this
external-to-the-database activity occurs.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Naomi" <Na...@discussions.microsoft.com> wrote in message
news:4EC32E64-65DA-44D6...@microsoft.com...

[Naomi]

Hi there,

I have done async before, but not with sql clr sp. So i am not sure how it
works.

Do you mean that the sp fires a request and get notified when it is finished
so it can finish the process? If so - how exactly does it work with clr sp?
Also the sp are static methods of the StoredProcedures class. Can you apply
do async calls in such case?

Do maybe have sample codes or know of good a reference about async clr sp?

Thanks

Naomi

[TheSQLGuru]

Clarification: do not even call CLR from within the TSQL action that wishes
to fire the web service. Simply store the relevant information into a
driver table (for rolling your own polling application) or to a service
broker arrangement. If you step outside sql server during transactions,
such as for a CLR webservice call, you are setting yourself up for locking
pain.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Naomi" <Na...@discussions.microsoft.com> wrote in message

news:DC3B764C-9019-49CC...@microsoft.com...