SQL Server connection string Asynchronous Processing=true

views:

983

answers:

SQL Server connection string Asynchronous Processing=true

Hello everyone,

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?

thanks in advance, George

+1 A:

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

marc_s 2009-10-29 11:12:03

Thanks Marc! 1. Does this option mean, if I enable this option, then I can use asynchronous API from ADO.Net, and if I disable this option, I cannot use asynchronous API from ADO.Net? 2. If I am not using asynchronous API from ADO.Net, I am not sure whether this option has any impact to my code if I do not use any asynchronous API from ADO.Net. I have this confusion because I am not sure whether underlying ADO.Net code use asynchronous processing to optimize performance (e.g. using thread pool queue work item) -- so if I set false to this option will block underlying code from optimization?

George2 2009-10-29 16:25:51

(continued.) even if I do not use asynchronous API from ADO.Net code explicitly. Any comments to my two points?

George2 2009-10-29 16:26:22

as far as I understand it, this setting just **enables** you to use async ADO.NET queries - but it doesn't do anything automatically. And yes - if you haven't specified this setting, you will **NOT** be able to use async ADO.NET queries.

marc_s 2009-10-29 16:29:35

"but it doesn't do anything automatically" -- means it just impact the code I am written, no impact to ADO.Net underlying code optimization?

George2 2009-10-29 16:44:19

It **enables** async ADO.NET queries - but unless you actually **write** such async queries, it has no effect.

marc_s 2009-10-29 17:29:23

As marc_s mensioned enabling or disabling async processing have no impact at your sytem performance. Even if you write your code to use async queries, this queries would not perform faster than without async calls. All it does is performing your query in another thread while your current thread remains responsive.

Tror 2009-10-29 20:59:41

@Tror: good point - the query isn't really faster, but it "feels" faster to the user

marc_s 2009-10-29 21:22:15

Thanks Marc, question answered!

George2 2009-10-30 04:54:21

Guys, I wish to set the asynchronous processing to true, as the error during inserting values to sql server asks me to set it to true. Any idea on how i can do it. I am using Sql server 2005 and ASP.NET 3.5 and not experienced in any

rahul k 2010-05-07 11:03:18

ansaurus

tags:

views:

answers:

SQL Server connection string Asynchronous Processing=true

related questions