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

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

  • Re: Performance is slow when calling web services in SQL CLR
    ... I do this on each call of the Clr routine. ... Lastly - do we need to set this property whenever we use web services, not just in sql clr sp? ... We have SQL CLR stored procedures that call web services. ... When the web service calls take long then it affects the performance of SQL DB of that sp completely, and slows down other CLR stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005
    ... Phil Barber wrote: ... > 2005 the CLR is now supported in stored procedures and triggers. ... SQL procedure outperform the CLR version quite a lot. ...
    (borland.public.delphi.non-technical)
  • Re: Performance is slow when calling web services in SQL CLR
    ... "Dave Ballantyne" wrote: ... Clr routine. ... just in sql clr sp? ... We have SQL CLR stored procedures that call web services. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance is slow when calling web services in SQL CLR
    ... "Dave Ballantyne" wrote: ... Clr routine. ... just in sql clr sp? ... We have SQL CLR stored procedures that call web services. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sequential Number in an Update
    ... among Standard SQL, ... Like I said again - how would you code this NOW in SQL Server; ... You've already told us "NO CLR, ... having a Mail Exchanger ius not a problem for all the sites that are ...
    (comp.databases.ms-sqlserver)