SQL Server connection string Asynchronous Processing=true

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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:

  1. System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery
  2. System.Data.SqlClient.SqlCommand.BeginExecuteReader
  3. 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:

Method 2

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.

Method 3

Just having the Asynchronous Processing=True in your connection string just simply enables you to write asynchronous queries – I don’t see how having that setting in your connection string should affect your performance, if you don’t change anything else.

You will hopefully begin to see a positive effect on your performance when you start doing asynchronous processing of your database queries. But just specifying that one option shouldn’t have any (positive or negative) impact on your app.

Marc

Method 4

Contradictory to what the accepted answer says, it actually has an impact on performance.

Atleast: according to the msdn documentation.
In practice however I wasn’t able to see any difference in a SQL 2005 Express scenario with .Net 3.5 SP1.

Since MSDN docs warns about this I thought it should be interesting for future reference.

Method 5

I’ve just tested performance of sync database calls with ASYNC=TRUE and ASYNC=FALSE. I was concerned about:

A: Definitely, only use ASYNC=TRUE when you know that you are going to be using the async functionality

I can say that the performance is exactly the same. I tested in Azure web role under high load and calculated average for big number of requests.

So if your application uses different types of database queries (sync and async) you can freely set Asynchronous Processing=true and use this connection for both sync and async queries. It will also keep your connection pool smaller, I believe.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x