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

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" <Naomi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC3B764C-9019-49CC-AAE5-4730DF705C00@xxxxxxxxxxxxxxxx
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" wrote:

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" <Naomi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4EC32E64-65DA-44D6-82E7-5C4D9466D6C2@xxxxxxxxxxxxxxxx
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
Quantcast