ADO.NET 2.0 and Asynchronous Execution

Url: http://msdn.microsoft.com/library/default.asp?u…

After reading this interesting article by Pablo Castro, I have to assume that the real purpose of using Async Execution is for specific use-cases when you need to fire off multiple concurrent queries in service situations (e.g. ASP.NET, Web Services or Windows Services).

There are several interesting observations in this article:

The first one i’d like to point out is in the “Keeping WinForms Applications Responsive” section. He states explicitly that the BackgroundWorker is a better solution than using Async. This means that you should avoid using it in UI-based apps (probably WinForms and WPF).

Another observation is detailed in this quote from the article (emphasis added):

“Note that if you know you’ll use a given connection object with synchronous commands only, it’s better not to include the async keyword in the connection string, or alternatively include it but set it to false. Execution of synchronous operations on connections that have asynchronous operations enabled will have noticeably increased resource utilization.”

This leads me to two conclusions:

1. Async connections won’t be pooled with non-async (since they have different connection strings).
2. You will need to weigh the higher resource utilization against the performance improvement of concurrent query execution to determine whether to use it.

Also, since you can manipulate a resultset from each concurrent query at the same time, it seems that the async keyword also turns on MARS on the connection, but I do not have any evidence of that…just a suspicion.

My advice?  Take time and read (carefully) what Pablo Castro has to say in the article and only use the Async functionality when you have a very clear case of concurrent execution in a service environment (ASP.NET, Web Service or Windows Service).

Feedback is encouraged…