Asynchronous processing in SQL Server and .NET Asynchronous processing

Question

What is the advantage of using asynchronous processing in SQL Server for .NET asynchronous processing? Aren't they the same? I find it difficult to understand the advantage of using asynchronous processing in SQL Server instead of .NET APM. I can easily wrap the SQL call in a lambda expression and execute BeginInvoke (...).

Can someone help me in the difference and benefit of both?

Answer 1

The problem with .NET asynchronous processing ( BeginInvoke(...) ) is that all this happens, it is disabling the thread to process the code synchronously. A 5-minute request will bind the thread for 5 minutes, blocking (i.e. do nothing for ~ 99% of the time), while the result is calculated at the remote end. Under stress (many requests at the same time), this will exhaust the threadpool, linking all threads in a locked state. Threadpool will become unresponsive, and new job requests will suffer a lot of latency while waiting for threadpool to activate additional threads. This is not an intentional use of threadpool, as it is designed with the goal that it asks for completion to be short-lived and non-blocking.

With APM Begin / EndAction pairs, the same action can be called in a non-blocking way, and only when the result is returned through the I / O completion port, which is queued as a work item in threadpool. None of your threads are interconnected, and the moment the response queue is processed, data is available, which means that the user code is not blocked in IO and can be completed quickly ... much more efficient use of threadpool, which scales to many other client requests without the expense of a stream for an outstanding operation.

Answer 2

As mentioned in previous answers, BeginInvoke uses a .NET stream. Equally important, however, is that the thread comes from the ASP.NET thread pool, so it competes with clients for very limited thread resources. The same is true for ThreadPool.QueueUserWorkItem() .

Asynchronous SqlClient calls require a SqlConnection , which has async = true. This mode requires a bit more network overhead (therefore, it is not enabled by default), but it does not consume a stream from the .NET thread pool. Instead, it uses asynchronous I / O.

The advantage of the latter approach is that it is much more scalable. You can handle hundreds or thousands of simultaneous requests in a way where the overhead with flow per call will be extreme. In addition, you would very quickly consume an entire ASP.NET thread stream (by default it has a maximum of about 12 threads).

Answer 3

.Net BeginInvoke simply defers execution to another thread. It will always be slower than a synchronous call and consume additional resources. The only reason this will be used is to free the caller’s context for other operations (for example, return the result of an HTTP request to the client).

SqlClient asynchronous methods, when the AsynchronousProcessing property in the connection is set to true and the BeginExecute methods for SqlCommand are used, are truly asynchronous. An SQL command is sent to the network communication channel, and termination is triggered when the result is returned by the server.

In terms of reliability, though none of these methods are useful. They both rely on the client process to stay around until the call is completed, otherwise SQL Server will see the client disconnect and refuse processing, rolling back any intermediate work. Consider an ASP application that has accepted an HTTP request, sent "asynchronous" payment processing, and returned a response. There is no way to guarantee that the work presented will actually happen.

In situations where processing requires reliability, the solution guarantees a queue for work on the server, fixes it and then continues, relying on SQL Server's own asynchronous processing capabilities. This is a method that guarantees processing even when there are client disconnections, processing ASP processes, mirroring SQL Server or failover clusters, hardware disaster recovery, pretty much anything that you can pounce on it, because it is a transactional, robust way to send asynchronous requests processing. For an example, see Executing an Asynchronous Procedure .

Answer 4

From this article SQL Server 2008 Books Online (October 2009) - Performing Asynchronous Operations , I quote:

Asynchronous processing allows methods to immediately return blocking of the calling thread. This allows most of the power and multithreading flexibility, without requiring the developer to explicitly create threads or handle synchronization.

If you explicitly created these threads and handled synchronization, you probably won't find much difference.

Answer 5

If you mean “Why use the BeginExecute___()/EndExecute____() data provider methods and not just transfer the normal Execute____() to the delegate, I suspect the answer is that when they first designed the provider model for ado.net for .Net 1.0, wrapping something in a delegate and accessing it asynchronously was not so simple.

Other possible reasons are that this corresponded to how the old code that they updated or wrapped for the original .Net worked, or that the built-in methods allow you to easily check for any returned exceptions