Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Services > Database Connection Overhead

Reply
Thread Tools

Database Connection Overhead

 
 
Timbo
Guest
Posts: n/a
 
      02-26-2009
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


 
Reply With Quote
 
 
 
 
Erland Sommarskog
Guest
Posts: n/a
 
      02-26-2009
Timbo ((E-Mail Removed)) 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, http://www.velocityreviews.com/forums/(E-Mail Removed)

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
 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      02-26-2009

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" <(E-Mail Removed)> wrote in message
news:e9O%(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Timbo
Guest
Posts: n/a
 
      02-26-2009
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" <(E-Mail Removed)> wrote in message
news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
> Timbo ((E-Mail Removed)) 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, (E-Mail Removed)
>
> 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



 
Reply With Quote
 
sloan
Guest
Posts: n/a
 
      02-26-2009

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
>> Timbo ((E-Mail Removed)) 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, (E-Mail Removed)
>>
>> 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

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Overhead of 4-port over 2-port SRAM John T. Goodman VHDL 0 01-25-2005 04:27 PM
Lowering Processing Overhead Radley Cisco 0 01-24-2004 04:30 PM
IP NBAR - What kind of overhead does it cause?? Douw Gerber Cisco 1 11-22-2003 09:27 AM



Advertisments