Database Connection Overhead
 

Hi There,

I have a ASP.NET Web Service that retrieves various data from a SQL 2000
database and returns results to the user. I have written individual
functions for each kind of data set that I'm returning. All was working
fine and now I've scaled it up so that potentially 50 people are making
requests the web service is sometimes hanging. I'm using a hosted server,
so at the moment I'm not sure if its IIS or SQL2000 that is falling over and
re-starting itself.

My main query at the moment is regarding database connections and if I'm
doing it correctly... here is a small example..


<WebMethod(Description:="Updates some data and return a string based on
result.", EnableSession:=True)> _
Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
String

Dim cmd As New SqlCommand
Dim cn As New SqlConnection

cn.ConnectionString = cnStr()
cmd.Connection = cn

..... do some SQL stuff etc...

cn.Close()
cmd.Dispose()
cn.Dispose()
End Function

I have about 20 different functions all doing something different. Is there
an overhead by me declaring the Connection string and Command string
everytime the function is called, could I not just declare them once at the
top of my class? If so would it be thread safe?

Is what I'm doing at the moment opening too many connections to the server
therefore causing it to crash when too many people are making requests?

I hope this makes sense and any advice or best practises would be greatly
received

Thanks.
Tim

Re: Database Connection Overhead
 

Timbo (me@home.com) writes:
><WebMethod(Description:="Updates some data and return a string based on
> result.", EnableSession:=True)> _
> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
> String
>
> Dim cmd As New SqlCommand
> Dim cn As New SqlConnection
>
> cn.ConnectionString = cnStr()
> cmd.Connection = cn
>
> .... do some SQL stuff etc...
>
> cn.Close()
> cmd.Dispose()
> cn.Dispose()
> End Function
>
> I have about 20 different functions all doing something different. Is
> there an overhead by me declaring the Connection string and Command
> string everytime the function is called, could I not just declare them
> once at the top of my class? If so would it be thread safe?

Now, I am not the right person to talk about thread-safeness in ASP .Net
that I know next to nothing about, but I can't imagine that it would be
thread-safe for five seconds.

You seem to be doing the right thing, but there are two things that need
qualification:

o cnStr() should return the same result every time. If you are adding
something unqiue each time, you effectively kill connection pooling.

o If you run ExecuteReader, I think you need to close it explicitly,
or else the command and connection will stay around, which again
causes problem.

> Is what I'm doing at the moment opening too many connections to the server
> therefore causing it to crash when too many people are making requests?

50 simultaneous users on a web site is not a startling number.

As I said, I don't have any web-programming experience (I'm an SQL guy),
but if I were you, I would try to have a local test environment, so you
can track down where the bottleneck is, IIS or SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Re: Database Connection Overhead
 

You should investigate the EnterpriseLibrary.Data framework.
Why?
Because it encapsulates alot of these best practices for you, was written by
experts and gets reviewed hundreds if not thousands of users.

Go here:
http://sholliday.spaces.live.com/feed.rss
Find
Multiple RDBMS Support and the Factory Design Pattern

Dont' focus on the WCF so much.
Find the CustomerSqlServerData(.cs) file/class and check out what is going
on there.


It also makes very "clean" code.
Example:

public override IDataReader
CustomersGetSingleWithOrdersReader(string customerId)
{

IDataReader returnReader = null;
try
{
Database db = this.GetDatabase();
DbCommand dbc =
db.GetStoredProcCommand(this.PROC_CUSTOMER_GET_SIN GLE_BY_ID); // This is a
CONST from earlier in the class


//Again another CONST from above...
db.AddInParameter(dbc, PARAMETER_CUSTOMER_ID,
System.Data.DbType.String, customerId);


returnReader = db.ExecuteReader(dbc);
return returnReader;
}
finally
{
}
}


You care about 2 things in the above code. Which stored procedure to call.
What parameters to give it.
The EnterpriseLibrary.Data does the rest for you.


From my experience, the only thing the EnterpriseLibrary.Data cannot protect
you from (because it can't in this situation) is using an IDataReader AND
THEN NOT CLOSING IT.
That's a big no-no.





"Timbo" <me@home.com> wrote in message
news:e9O%23EkBmJHA.3760@TK2MSFTNGP03.phx.gbl...
> Hi There,
>
> I have a ASP.NET Web Service that retrieves various data from a SQL 2000
> database and returns results to the user. I have written individual
> functions for each kind of data set that I'm returning. All was working
> fine and now I've scaled it up so that potentially 50 people are making
> requests the web service is sometimes hanging. I'm using a hosted server,
> so at the moment I'm not sure if its IIS or SQL2000 that is falling over
> and re-starting itself.
>
> My main query at the moment is regarding database connections and if I'm
> doing it correctly... here is a small example..
>
>
> <WebMethod(Description:="Updates some data and return a string based on
> result.", EnableSession:=True)> _
> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
> String
>
> Dim cmd As New SqlCommand
> Dim cn As New SqlConnection
>
> cn.ConnectionString = cnStr()
> cmd.Connection = cn
>
> .... do some SQL stuff etc...
>
> cn.Close()
> cmd.Dispose()
> cn.Dispose()
> End Function
>
> I have about 20 different functions all doing something different. Is
> there an overhead by me declaring the Connection string and Command string
> everytime the function is called, could I not just declare them once at
> the top of my class? If so would it be thread safe?
>
> Is what I'm doing at the moment opening too many connections to the server
> therefore causing it to crash when too many people are making requests?
>
> I hope this makes sense and any advice or best practises would be greatly
> received
>
> Thanks.
> Tim
>

Re: Database Connection Overhead
 

Thanks for the response Erland, I do close everything explicitly including
DataReader Objects, but most of the time I use Output Parameters against the
Command Object in SQL which I consider to be the most efficient way of
returning small amounts of data i.e., Single names or True/False results -
Am I right in this assumption?

TIA Tim



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
> Timbo (me@home.com) writes:
>><WebMethod(Description:="Updates some data and return a string based on
>> result.", EnableSession:=True)> _
>> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
>> String
>>
>> Dim cmd As New SqlCommand
>> Dim cn As New SqlConnection
>>
>> cn.ConnectionString = cnStr()
>> cmd.Connection = cn
>>
>> .... do some SQL stuff etc...
>>
>> cn.Close()
>> cmd.Dispose()
>> cn.Dispose()
>> End Function
>>
>> I have about 20 different functions all doing something different. Is
>> there an overhead by me declaring the Connection string and Command
>> string everytime the function is called, could I not just declare them
>> once at the top of my class? If so would it be thread safe?
>
> Now, I am not the right person to talk about thread-safeness in ASP .Net
> that I know next to nothing about, but I can't imagine that it would be
> thread-safe for five seconds.
>
> You seem to be doing the right thing, but there are two things that need
> qualification:
>
> o cnStr() should return the same result every time. If you are adding
> something unqiue each time, you effectively kill connection pooling.
>
> o If you run ExecuteReader, I think you need to close it explicitly,
> or else the command and connection will stay around, which again
> causes problem.
>
>> Is what I'm doing at the moment opening too many connections to the
>> server
>> therefore causing it to crash when too many people are making requests?
>
> 50 simultaneous users on a web site is not a startling number.
>
> As I said, I don't have any web-programming experience (I'm an SQL guy),
> but if I were you, I would try to have a local test environment, so you
> can track down where the bottleneck is, IIS or SQL Server.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Re: Database Connection Overhead
 

output variables are faster than "single row" IDataReaders (or similar)

Performance is better, maintenance is at a slightly higher cost. (Aka, when
you need to add a column to the result, the signature changes using the
output variables).



"Timbo" <me@home.com> wrote in message
news:ec6eBcCmJHA.1172@TK2MSFTNGP05.phx.gbl...
> Thanks for the response Erland, I do close everything explicitly
> including DataReader Objects, but most of the time I use Output Parameters
> against the Command Object in SQL which I consider to be the most
> efficient way of returning small amounts of data i.e., Single names or
> True/False results - Am I right in this assumption?
>
> TIA Tim
>
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
>> Timbo (me@home.com) writes:
>>><WebMethod(Description:="Updates some data and return a string based on
>>> result.", EnableSession:=True)> _
>>> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
>>> String
>>>
>>> Dim cmd As New SqlCommand
>>> Dim cn As New SqlConnection
>>>
>>> cn.ConnectionString = cnStr()
>>> cmd.Connection = cn
>>>
>>> .... do some SQL stuff etc...
>>>
>>> cn.Close()
>>> cmd.Dispose()
>>> cn.Dispose()
>>> End Function
>>>
>>> I have about 20 different functions all doing something different. Is
>>> there an overhead by me declaring the Connection string and Command
>>> string everytime the function is called, could I not just declare them
>>> once at the top of my class? If so would it be thread safe?
>>
>> Now, I am not the right person to talk about thread-safeness in ASP .Net
>> that I know next to nothing about, but I can't imagine that it would be
>> thread-safe for five seconds.
>>
>> You seem to be doing the right thing, but there are two things that need
>> qualification:
>>
>> o cnStr() should return the same result every time. If you are adding
>> something unqiue each time, you effectively kill connection pooling.
>>
>> o If you run ExecuteReader, I think you need to close it explicitly,
>> or else the command and connection will stay around, which again
>> causes problem.
>>
>>> Is what I'm doing at the moment opening too many connections to the
>>> server
>>> therefore causing it to crash when too many people are making requests?
>>
>> 50 simultaneous users on a web site is not a startling number.
>>
>> As I said, I don't have any web-programming experience (I'm an SQL guy),
>> but if I were you, I would try to have a local test environment, so you
>> can track down where the bottleneck is, IIS or SQL Server.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/pro...ads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
>