Database Connection Performance Question

Hi,

I know that one of the costliest things in an application is database connections.
If I needed to read from multiple tables or write into multiple tables, in my old coding style -- which is top to bottom -- I'd always open the DB connection, get everything done then close the connection. This way, with one DB connection, I'd take care of a lot of things.
Now, I'm beginning to write my code in OOP fashion so I've started to create classes for commonly used functions. My question is with this method, I'll have to open and close the database connection several times to get the job done.
Will this take a toll on my application's performance? I know using classes is the right way to code in C#, but like I said, I'm always concerned about performance.
Once somebody told me that the performance hit would be negligable as IIS uses connection pooling. So even if I close a DB connection, it's not really removed from the memory right away just in case it needs to be recycled.
I'd appreciate some comments on this please. Thanks.
Thanks,

Sam
0
SamU
1/16/2003 6:13:14 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
612 Views

Similar Articles

[PageSpeed] 21

Hi Sam,

"Somebody" is generally right. As long as you use the exact same connection string every time you connect to the database--even an extra space somewhere will affect this--ADO.NET will manage the connections efficiently.
BUT! Be sure to load test the app to make sure this is happening. Look at the number of connections you have open, and check the performance counters for pooled connections on the Web or database servers to make sure that you're getting what you want. It's a bit too easy to sabotage the pooling.
There are a bunch of KB articles about this, but also check out Bill Vaughn's
ADO.NET and ADO Examples and Best Practices for VB Programmers
or
ADO.NET Examples and Best Practices for C# Programmers
. Lots of performance data and advice there.
Don

Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
1/16/2003 9:45:15 PM
::Now, I'm beginning to write my code in OOP fashion so I've started to create classes for

::commonly used functions. My question is with this method, I'll have to open and close the
::database connection several times to get the job done.
Which leads us to another question: ever throught of making your class design in a better way? The perf hit is neglegible, surely, but your design is also not exactly optimal :-)

Thomas Tomiczek
PowerNodes ApS
(Microsoft MVP C#/.NET)
---
Building a Website? Try the PowerNodes CMS - http://www.powernodes.com/
0
thona
1/17/2003 11:29:44 AM
I too struggled with the same question when architecting our migration to ASP.NET (our ASP pages use the one-connection per page model).  

Our new ASP.NET data layer handles all connection information, and only keeps a connection open for as long as is needed (this has the added benefit of hiding connection information from our upper tiers).
I found two issues with the alternative: 1) you have to pass an SqlConnection object around, which on a page with multiple controls, can be very unwieldy, and 2) during a page request, a single SqlConnection will spend a lot of "idle" time occupying the pool; this is time when it could be used by other connection requests.
Sorry, I don't have an quantitative data comparing the two methods; but our ASP.NET pages have had fewer IIS queueing issues and faster execution times.
Also, I wouldn't worry about the memory consumed by spent SqlConnection objects; its the garbage collector's job to worry about them.
0
harrycaul
1/18/2003 7:02:21 AM
Oh, and I forgot to mention: whenever possible, use caching!  Every hit to the cache is a relief for your database.  Use caching wisely, and you will be using far fewer SqlConnection objects.
0
harrycaul
1/18/2003 8:12:10 AM
I appreciate all the feedback. Thank you very much.
Thanks,

Sam
0
SamU
1/20/2003 1:17:59 PM
Open one connection to handle all SQLs from top to bottom or use OOP fashion coding and open/close the connection for each SQL request?

I have conducted a set of tests for the issue. Basically, I loops 10000 for a SELECT statement with different situations below:

Dim sConnectionString As String = "Data Source=localhost;uid=sa;pwd=;database=pubs"
Dim oConnection As New SqlConnection(sConnectionString)
Dim oCommand As New SqlCommand("SELECT_Proc", oConnection)

where "SELECT_Proc" is a stored Procedure "SELECT * FROM employee".

Test 1: One connection for the whole page: 11 seconds

oCommand.Connection.Open()
Do While Counter <= 10000
oCommand.ExecuteScalar()
Counter = Counter + 1
Loop
oConnection.Close()

Test 2: Each connection for each SQL: 13 seconds

Do While Counter <= 10000
oCommand.Connection.Open()
oCommand.ExecuteScalar()
oConnection.Close()
Counter = Counter + 1
Loop

Test 3: Each connection for each SQL with DAL: 13 seconds

Do While Counter <= 10000
SqlHelper.ExecuteScalar(sConnectionString, "SELECT_Proc_with_10000_Loop")
Counter = Counter + 1
Loop

Test 4: One connection with 1000 loops within SP: 3-4 seconds

oCommand.Connection.Open()
oCommand.ExecuteScalar()
oConnection.Close()

Test 1 only opened one connection, while Test 2 opened/closed 10,000 time of the connection. The cost is two seconds. That means one time open/close of a connection cost about 1/5000 seconds, actually nothing.
Test 2 and Test 3 all opened/closed a conneciton 10,000 times, but Test 2 used a simple .ExecuteScalar() method, while Test 3 used MS ADDB SqlHelper , that is typical OOP style Data Access Layer. They both took the same 13 seconds. If you had chance to navigate DAAB, you would know how many line code had been gone through for test 3, but they do have visible effect for the performance.
Test 4 had a loop of 10,000 times within the SP, which made a visible improvement for performance.
Suggestion
A way with OOP fashion coding and open/close a connection as close as possible to a SQL statement, or say with possible multiple open/close connection with a page (or other scope), will not affect the performance with any visible sense.
A byproduct from the test is a way with the loops within SP, or fewer round trips between database and code, will improve the performance 3 to 4 times.

MCAD.NET (C#)


MCP on: SQL Server 2k Design&Iimplementation, Web Development (VI 6.0) and VB 5.0
0
ghan
9/4/2003 8:52:30 PM
Reply:

Similar Artilces:

can't connect to sql server database with same connection used in visual studio .net 2003
I have the following code that works fine in visual studio .net 2003 but not with VWD.  I have sql server 2000 developer edition installed locally and this connection works fine with visual studio.  With VWD it fails with the exception "System.Data.SqlClient.SqlException: Login failed for user 'DARRELL\ASPNET'".  It fails on the conn.Open line.   any ideas on what the issue is?Dim conn As SqlConnectionDim connstr As Stringconnstr = "Data Source=DARRELL;Initial Catalog=Event;Integrated Security=SSPI;"Dim daEvents As New SqlDataAdapter("select * from events", connstr...

Connecting Datasource to SQL Server 7 database
Hi   I've been using the new features with  ASP.Net 2  for some months now. I use the gridview control a lot with the sqldatasource. I recently started a new job and am having problems connecting a sqldatasource control to a sql server 7 database. At my old job we used sql server 2000.   I get the error: "SQL server version needs be 2000 or higher" when I try to set up a new connection.. I read some documentation that said you can use asp.net 2 datasource controls with sql server 7. Does anyone know how to get round this issue. I don't want to have to go ...

problems connecting to my SQL server database in .NET
I am able to connect and display data in classic asp just fine, but I cannot connect to my database using ASP.NET. I have tried 2 tutorials on here and neither has worked. They both give me the same error: "Login failed for user 'NASHIE\ASPNET'." I am currently working on this tutorial: http://asp.net/webmatrix/tour/section3/binddatagrid.aspx "Display Data with MxDataGrid" [wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" UpdateCommand="" SelectCommand="SELECT * FROM [Orders]" ConnectionString="...

connecting from VB.NET to an online SQL database
Hi, I'm currently working on a project that consists of three parts:(1) an ASP.NET website(2) a VB.NET program(3) .mdf sql database that is shared between the program and the websiteso, because the database is shared, I need it to be uploaded to a web server, just like the website, but how can I connect my VB.NET program to the database?   Maybe I didn't get the question...The save way you connect from you asp.net application, with connection string, just change the server attribute in the connection string to the ip of the database serverTal GiladiSoftware engineerwww.ta...

Connection to SQL 2000 database with .Net 1 Application
Hi there,   Has anyone ever encountered problems making .Net applications connect to a SQL database on SQL 2000 with SP3a?   It's running on a virtual machine with a Windows Server 2000 Std with SP4, the .Net application is working with .Net Framework 1.1.4322.573. For some reason it can't connect to the SQL database using the specified username and password in the web.config file even though the user exists in the SQL database. I've tried using the SA password but this still doesn't work. I can logon to SQL Query Analyzer using the SA password but not usin...

Managing Connections for optimal performance question, switch from Oracle to SQL
I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database. To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire app...

absolute newbie question
Hi, I am trying to connect my Asp.net page to my database residing at sql server. As soon as I click on the database path to map the path i get two error alerts. 1. MS Data Link Error [DBNET LIB][CONNECTION OPEN(Connect()).] SQL Server does not exist or access denied. 2. MS Data Link Login failed. Catalog information cannot be retrived. My questions 1.Where am i going wrong or what am I doing wrong? 2. Any online source where I can get the basic tutorials regarding ADO.NET. I know these are very basic silly questions, My apologies if these ques...

problem with Connecting to a SQL Anywhere Studio Database Using ADO.NET
I am trying to connect and read the data from the asademo.db database that is used in the document "Connecting to a SQL Anywhere Studio Database Using ADO.NET ". I am using Sql Anywhere v7.0 and MS Visual Studio .Net 2003 with C#. I can connect to the database but I get a 'Specific cas is not valid' exception when I try to run the myReader = myCommand.ExecuteReader() line in the demo course. Can anyone tell me why it is not working? I'm getting the same error using V7.0.2.1402. Did you find any solution/workaround? You need a more recent version of the A...

SQLMonitor.exe questions for ODBC connection to a MS SQL Server database
I have been reading the newsgroups for quite a while, and they, along with web searches have answered many questions that I have had over the years. However, I have come across a problem that I have been unable to research and resolve successfully. I am starting to program applications with databases, in this case MS SQL Server. I cannot get the SQLMonitor.exe to correctly show database access times and connection/SQL info. Using Delphi 2010 and ODBC connections, I just get a series of hollow square characters in the "SQL Statement" column. I am using "SQL Monitor version...

connect to MS SQL database using visual studio.net failed
Hi, I'm trying to connect to MS SQL 2000 using visual studio.net in window server 2003. Clicking the server explorer, connect to database icon, and selecting the provider and so on, when i click on the 'test connection', an error appear indicating : "Test connection failed because of an error in initializing provider. Device activation error. The physical name : c:\MCSDWebapps\Chapter05\vbADOsnippers\Contact.MDF may be incorrect " Note : I'm connecting to window server 2003 locally i.e I'm working on the same computer i.e server 2003. I've tried in win XP Pro and it wor...

SQL Anywhere 5.5.05:[[Sybase][ODBC Driver]Watcom SQL rejected connection: too many connections to database-102]
I use SQL Anywhere 5.5.05 with WinNT 4.0 SRV and WebIntelligence 2.6.1 and sometimes when i refresh a query of WebI there is this error: [[Sybase][ODBC Driver]Watcom SQL rejected connection: too many connections to database-102] Why ??? Why isn't there always ??? Denis PS: sorry for my bad english :( Are you using dbsrv50 or dbeng50? If you're using dbegn50 then there is a limit of 10 local connections to the database. The 11th is rejected. If you are using dbsrv50, then you have an unlimited number of local connections, but the number of distinct machines tha...

Cannot connect to SQL Server database using ado.net after IP address has changed
Dear all, I wasn't able to connect to my SQL server 2000 database (which reside in the network in a windows 2003 server) after the IP address of the server has changed and the server is shut down, moved and re-started. I was able to connect to this server by using ADO using VB. I was able to register this server in my EM and was able to create ODBC connection. Just when using ADO.NET, I wasn't able to connect. Any ideas? Will there be any caching within ADO.NET still pointing to the old IP address? Thank you for all the help in advance. There are a lot of variable possibilites her...

how to connect sql database(vb.net) to authenticate using login n pwd
Hi there, I am new to programming. Can any one please tell me how to connect to sqldatabase through vb.net. I must use login n password to authenticate the user.. if authenticated i must return true  Please help!! The video How Do I: Set Up the SQL Membership Provider should show you everything you need."Peace is Every Step." -Thich Nhat Hanh Hi there,  Thanks for ur reply. I am using a webservice where i get login n password from client request. I the link u hav provided show how to use login details in asp.net. And they hav used connection string in web co...

Please Help me:SQL statement failed: (-301) Internal database error ??? -- transaction rolled back SQL statement failed: (-101) Not connected to a database
Hi all, I am using Adaptive Server Anywhere 8 version 8.0.2.4239. I am able to sync successfully between 2 desktops with the same version. When I am trying to sync with my pocket pc device version 8.0.4122, Towards the end of Synchronization (nearly 95% completed) ,I am getting this error "E. 05/29 19:28:31. SQL statement failed: (-301) Internal database error ??? -- transaction rolled back E. 05/29 19:28:31. SQL statement failed: (-101) Not connected to a database" The log file contains E. 05/29 19:28:31. SQL statement failed: (-301) Internal database er...

Web resources about - Database Connection Performance Question - asp.net.sql-datasource

Performance - Wikipedia, the free encyclopedia
A performance , in performing arts , generally comprises an event in which a performer or group of performers behave in a particular way for ...

Jada Pinkett Smith Bares It All in See-Through Dress for Will Smith's Latin GRAMMYs Performance – See ...
Jada Pinkett Smith Bares It All in See-Through Dress for Will Smith's Latin GRAMMYs Performance – See the Pics!

Supercomputing Trends: Performance Lags, China Rises, Cray Gains
While the overall performance of systems continues to increase, that growth continues to slow. We look at these and other supercomputing trends. ...

AMD’s new Radeon Crimson software crushes Catalyst, boosts performance
AMD has launched a complete overhaul of its driver stack today, with new performance boosting features, per-game overclocking, shader cache support, ...

American Music Awards 2015: Five Seconds Of Summer's 'Wall-Shaking' Performance One To Watch
The American Music Awards promises some big performances for its 2015 show, but only Five Seconds of Summer promises to "make the walls shake" ...

Latin Grammys gets political as Will Smith returns to stage in epic performance
Latin Grammys gets political as Will Smith returns to stage in epic performance

DOE Announces a High Performance Computing Fortran Compiler Agreement
... (NNSA) and its three national labs this week announced they have reached an agreement for an open-source Fortran front-end for Higher Performance ...

Miranda Lambert Felt For Gwen Stefani During ‘Used To Love You’ AMA Performance
... with Miranda Lambert’s ex-husband, Blake Shelton, the country singer was reportedly impressed with Stefani’s American Music Awards performance ...

Audi Painted Their Top Performance Cars In Super '70s Soviet-Style Wonder
Imagine your dream 1970s Soviet vacation to the beach, and this would be your color palate: leaving your grey apartment block outside Bucharest ...

Be Mesmerized by Joseph Gordon Levitt's 'Rhythm Nation' Performance
Joseph Gordon-Levitt recently paid tribute to Janet Jackson on Lip Sync Battle and it may be the best performance the show has seen so far. Gordon-Levitt ...

Resources last updated: 11/25/2015 1:52:21 PM