Single connection or not ?

  • Thread starter Thread starter Julien Cheyssial
  • Start date Start date
J

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.
 
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.
__________________________________
 
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.


William (Bill) Vaughn said:
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 said:
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.
 
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.
__________________________________

Julien Cheyssial said:
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.


William (Bill) Vaughn said:
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.
 
Back
Top