Single connection or not ?

[Julien Cheyssial]

Hi all,

To summarize my problem, I'd like to know if it's better to open many short
SqlConnection for each database calls, or one longer SqlConnection used by
all database calls.

Let's say I have a function called "GetMyPageData()" in my Business Logic
Layer :

function GetMyPageDataInXml(XmlDocument output, int userId)
{
MyDAL.GetUserInfos(userId, output);
MyDAL.GetUserItems(userId, output);
}

This functions let me get some data in about a User and its items. My Data
Access Layer is based on the Microsoft Data Access Application Block (v2.0)
and GetUserInfos() and GetUserItems() both are implemented like this :

string spName = "GetUserInfos";
SqlParameter[] spParams =
{
SqlHelper.MakeInParam("@UserId", SqlDbType.Int, 4, userId)
};
SqlDataReader reader =
SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings["ConnectionString"
], CommandType.StoredProcedure, spName, spParams);
....

SqlHelper.ExecuteReader() manages the SqlConnection itself. But, since I
make two calls to ExecuteReader (one for GetUserInfos, and another one for
GetUserItems), I guess two connections are being opened and closed. Should I
manage the SqlConnection myself in order to open only one connection for
such a request ?

Thanks

Julien C.

 

[William \(Bill\) Vaughn]

What architecture are you using? Is this an ASP.NET application or Windows
forms?
Each time you open a connection there is some overhead involved--even when
the connection is pooled. This can be eliminated by leaving the connection
open (if your architecture permit this), but at the price of holding a
connection on the server that could be used for other clients. If you don't
have that many clients, it won't make a snit of difference. If you're using
the Application Block I expect you had better stick to what they recommend.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Julien Cheyssial]

Thanks William for your answer. My application is a web ASP.NET application.
The application load will be pretty heavy so I have to optimize everything.
The Data Access Application Block doesn't recommend or preconize a
particular maner of doing things. It just provides a set of usefull
functions allowing you to incredibly reduce the number of lines of code.

For instance, in my sample I used :

SqlHelper.ExecuteReader(connectionString,
CommandType.StoredProcedure, spName, spParams);

but I could have used :

SqlHelper.ExecuteReader(sqlConnection, CommandType.StoredProcedure,
spName, spParams);

The DAAB just let me write fewer lines of code but doesn't tell me the best
maner to perform things... I guess I'll have to do some benchmarking to know
what's the best option.

What architecture are you using? Is this an ASP.NET application or Windows
forms?
Each time you open a connection there is some overhead involved--even when
the connection is pooled. This can be eliminated by leaving the connection
open (if your architecture permit this), but at the price of holding a
connection on the server that could be used for other clients. If you don't
have that many clients, it won't make a snit of difference. If you're using
the Application Block I expect you had better stick to what they recommend.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi all,

To summarize my problem, I'd like to know if it's better to open many short
SqlConnection for each database calls, or one longer SqlConnection used by
all database calls.

Let's say I have a function called "GetMyPageData()" in my Business Logic
Layer :

function GetMyPageDataInXml(XmlDocument output, int userId)
{
MyDAL.GetUserInfos(userId, output);
MyDAL.GetUserItems(userId, output);
}

This functions let me get some data in about a User and its items. My Data
Access Layer is based on the Microsoft Data Access Application Block (v2.0)
and GetUserInfos() and GetUserItems() both are implemented like this :

string spName = "GetUserInfos";
SqlParameter[] spParams =
{
SqlHelper.MakeInParam("@UserId", SqlDbType.Int, 4, userId)
};
SqlDataReader reader =

SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings["ConnectionString"

], CommandType.StoredProcedure, spName, spParams);
...

SqlHelper.ExecuteReader() manages the SqlConnection itself. But, since I
make two calls to ExecuteReader (one for GetUserInfos, and another one for
GetUserItems), I guess two connections are being opened and closed.

Should

I
manage the SqlConnection myself in order to open only one connection for
such a request ?

Thanks

Julien C.

 

[William \(Bill\) Vaughn]

In ASP architectures, you simply must close the connection between
invocations of the ASP page. However, while on performing multiple
operations on a page I have seen situations where a single connection is
reused. The problem is, if the connection is not closed while it's in scope
you can't depend on the GC to do it for you--it simply won't happen in time
(before the pool overflows). Unlike Windows forms applications, you can't
hold the connection state between invocations--it simply won't scale.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Thanks William for your answer. My application is a web ASP.NET application.
The application load will be pretty heavy so I have to optimize everything.
The Data Access Application Block doesn't recommend or preconize a
particular maner of doing things. It just provides a set of usefull
functions allowing you to incredibly reduce the number of lines of code.

For instance, in my sample I used :

SqlHelper.ExecuteReader(connectionString,
CommandType.StoredProcedure, spName, spParams);

but I could have used :

SqlHelper.ExecuteReader(sqlConnection, CommandType.StoredProcedure,
spName, spParams);

The DAAB just let me write fewer lines of code but doesn't tell me the best
maner to perform things... I guess I'll have to do some benchmarking to know
what's the best option.

What architecture are you using? Is this an ASP.NET application or Windows
forms?
Each time you open a connection there is some overhead involved--even when
the connection is pooled. This can be eliminated by leaving the connection
open (if your architecture permit this), but at the price of holding a
connection on the server that could be used for other clients. If you don't
have that many clients, it won't make a snit of difference. If you're using
the Application Block I expect you had better stick to what they recommend.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

used

by

all database calls.

Let's say I have a function called "GetMyPageData()" in my Business Logic
Layer :

function GetMyPageDataInXml(XmlDocument output, int userId)
{
MyDAL.GetUserInfos(userId, output);
MyDAL.GetUserItems(userId, output);
}

This functions let me get some data in about a User and its items. My Data
Access Layer is based on the Microsoft Data Access Application Block (v2.0)
and GetUserInfos() and GetUserItems() both are implemented like this :

string spName = "GetUserInfos";
SqlParameter[] spParams =
{
SqlHelper.MakeInParam("@UserId", SqlDbType.Int, 4, userId)
};
SqlDataReader reader =

SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings["ConnectionString"

], CommandType.StoredProcedure, spName, spParams);
...

SqlHelper.ExecuteReader() manages the SqlConnection itself. But, since I
make two calls to ExecuteReader (one for GetUserInfos, and another one for
GetUserItems), I guess two connections are being opened and closed.

Should

I
manage the SqlConnection myself in order to open only one connection for
such a request ?

Thanks

Julien C.