Async Calls to DB does not help in the efficiency department?

[roberto3214]

Hi Guys,

I was recently reading an article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnservice/html/service10012002.asp

[Excerpt]
"One of the most common types of I/O operations that occur within a Web
method is a call to a SQL database. Unfortunately, Microsoft® ADO.NET
does not have a good asynchronous calling mechanism defined at this
time, and simply wrapping a SQL call in an asynchronous delegate call
does not help in the efficiency department. Caching results is
sometimes an option, but you should also consider using the Microsoft
SQL Server 2000 Web Services Toolkit to expose your databases as a Web
service. You will then be able to use the support in the .NET Framework
for calling Web services asynchronously to query or update your
database."

It stated that perhaps wrapping an sql call in an async delegate
doesn't really provide any performance gains. Is this true? Or does
this only apply to webmethods? Maybe I'm misinterpreting what is being
said?

Regards DotnetShadow

 

[W.G. Ryan eMVP]

Roberto - they can definitely help the responsiveness of an application
depending on how you code things- but AFAIK - I haven't seen any performance
benefits - in fact depending on how things are called- there's actually a
hit to performance. But the hit to performance can be trivial and more than
offset itself by the increased responsivness.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Hi Guys,

I was recently reading an article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnservice/html/service10012002.asp

[Excerpt]
"One of the most common types of I/O operations that occur within a Web
method is a call to a SQL database. Unfortunately, Microsoft® ADO.NET
does not have a good asynchronous calling mechanism defined at this
time, and simply wrapping a SQL call in an asynchronous delegate call
does not help in the efficiency department. Caching results is
sometimes an option, but you should also consider using the Microsoft
SQL Server 2000 Web Services Toolkit to expose your databases as a Web
service. You will then be able to use the support in the .NET Framework
for calling Web services asynchronously to query or update your
database."

It stated that perhaps wrapping an sql call in an async delegate
doesn't really provide any performance gains. Is this true? Or does
this only apply to webmethods? Maybe I'm misinterpreting what is being
said?

Regards DotnetShadow

 

[Pablo Castro [MS]]

Yep, async delegates may help with responsiveness (e.g. will avoid blocking
the UI thread in a Windows app, or may allow to make multiple database API
calls in parallel), but it will typically have no positive effect on perf
(and in many scenarios it can hurt it pretty bad).

The main problem is that in the end the APIs that you're calling are
synchronous, so they'll block a thread; by using an async delegate all
you're doing is blocking another thread instead of yours. If you can get
something else done in the meanwhile, it might be good, but now you're using
more threads per-request.

FWIW, in the next release of ADO.NET we will support true asynchronous
command execution , you can read about it in this article:

http://msdn.microsoft.com/data/Data...px?pull=/library/en-us/dnvs05/html/async2.asp

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[roberto3214]

Thanks for your input guys, so basically no point doing async SQL
requests? Like I'm mainly interested for the purposes of forget and
fire type requests

Regards Rob

Yep, async delegates may help with responsiveness (e.g. will avoid blocking
the UI thread in a Windows app, or may allow to make multiple database API
calls in parallel), but it will typically have no positive effect on perf
(and in many scenarios it can hurt it pretty bad).

The main problem is that in the end the APIs that you're calling are
synchronous, so they'll block a thread; by using an async delegate all
you're doing is blocking another thread instead of yours. If you can get
something else done in the meanwhile, it might be good, but now you're using
more threads per-request.

FWIW, in the next release of ADO.NET we will support true asynchronous
command execution , you can read about it in this article:

http://msdn.microsoft.com/data/Data...px?pull=/library/en-us/dnvs05/html/async2.asp

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

Roberto - they can definitely help the responsiveness of an application
depending on how you code things- but AFAIK - I haven't seen any performance
benefits - in fact depending on how things are called- there's actually a
hit to performance. But the hit to performance can be trivial and

more

than
offset itself by the increased responsivness.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Hi Guys,

I was recently reading an article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnservice/html/service10012002.asp

[Excerpt]
"One of the most common types of I/O operations that occur within a Web
method is a call to a SQL database. Unfortunately, Microsoft® ADO.NET
does not have a good asynchronous calling mechanism defined at this
time, and simply wrapping a SQL call in an asynchronous delegate call
does not help in the efficiency department. Caching results is
sometimes an option, but you should also consider using the Microsoft
SQL Server 2000 Web Services Toolkit to expose your databases as a Web
service. You will then be able to use the support in the .NET Framework
for calling Web services asynchronously to query or update your
database."

It stated that perhaps wrapping an sql call in an async delegate
doesn't really provide any performance gains. Is this true? Or does
this only apply to webmethods? Maybe I'm misinterpreting what is being
said?

Regards DotnetShadow