Menu
I am using .Net 2.0 + SQL Server 2005 Enterprise + VSTS 2008 + C# + ADO.Net to develop ASP.Net Web application.
My question is, if I am using Asynchronous Processing=true with SQL Server authentication mode (not Windows authentication mode, i.e. using sa account and password in connection string in web.config), I am wondering whether Asynchronous Processing=true will impact performance of my web application (or depends on my ADO.Net code implementation pattern/scenario)? And why?
969
SQL Server allows applications to perform asynchronous database operations. Asynchronous processing enables methods to return immediately without blocking on the calling thread.
Set on the connection string: Asynchronous operation to work, the attribute Async=True; as shown here: <?
Asynchronous calls are most useful when facing relatively infrequent large, expensive operations that could tie up response threads which could otherwise be servicing requests while the originator waits. For quick, common operations, async can slow things down.
A trusted connection is used if you're connecting to the database without providing a user name and password. For example, if you are connecting via a Data Source Name, and the DSN contains the user ID you're using to connect, then your trusted connection is true.
Begining with .NET Framework 4.5, Asynchronous Processing property is ignored, thus it's unnecessary to include it.
Quote:
Prior to .NET Framework 4.5, asynchronous programming with SqlClient was done with the following methods and the Asynchronous Processing=true connection property:
- System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery
- System.Data.SqlClient.SqlCommand.BeginExecuteReader
- System.Data.SqlClient.SqlCommand.BeginExecuteReader
This functionality remains in SqlClient in .NET Framework 4.5.
Beginning in the .NET Framework 4.5, these methods no longer require Asynchronous Processing=true in the connection string.
For more information, refer to below links:
82
As a matter of fact, there are performance issues when you enable that option; see the ADO.NET 2.0 Asynchronous Command Execution (ASYNC) FAQ:
Q: What is the new ADO.NET 2.0 Asynchronous Command Execution feature.
A: ASYNC allows you to execute a command in a non-blocking manner. We are exposing in the SqlCommand the following asynchronous methods: BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXmlReader with polling, synchronization and (shudder) callbacks....
Q: So does this mean that every command I execute (sync or async) will happen in overlapped mode when I add ASYNC=TRUE to the connection string?
A: Yes it does, everything that we execute on this connection will be done in overlapped mode. For synchronous operations we internally wait for the completion before returning, we are basically faking the synchronous behavior on this connection. This is the reason why we require a connection string keyword.Q: Does this have a perf impact?
A: Definitely, only use ASYNC=TRUE when you know that you are going to be using the async functionality....
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
