Database Connection Overhead

T

Timbo

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
 
E

Erland Sommarskog

Timbo said:
<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 address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
S

sloan

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_SINGLE_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.
 
T

Timbo

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
 
S

sloan

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top