time-to-instantiation versus time-to-connect

  • Thread starter Thread starter John A Grandy
  • Start date Start date
J

John A Grandy

wxp pro sp1, vs.net 2003, sql-svr 2k sp3

to a rough approximation ...

what is the relative time needed to instantiate ADO.NET objects, such as
SQLConnection, SQLCommand, SQLDataAdapter, etc , ....

..... versus the time needed for operations such as
SQLConnection.OpenConnection() , SQLConnection.CloseConnection() ... ?

is instantiation a trivial amount of time compared with opening a connection
?
 
Instantiation is faster. Neither is 'static' per se, but there are a lot of
external influences that can affect how long it takes to open a connection
(assume one is available). You can always instantiate an object, but you
may not be able to open a connection...

HTH,

Bill
 
Something to take into account, if you use cn.Open(), and there is a usable
connection to the database sitting in the connection pool, then the
instantiation is the slowest part. This makes a big difference if you are
writing a web app, as your connections can be 'closed' and 'opened' a lot
without a significant hit to performance.
On the other hand, if there is no connection available in the pool, the
cn.Open() is one of the most expensive things you can do in an application.

Kirk Graves
KRGIT Software
 
Connecting can still be longer--even if there is a connection in the pool
because if you use SSPI, authentication is done again. This slows down some
providers--including SqlClient.

--
____________________________________
Bill Vaughn
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.
__________________________________
 
Ok. I am working with an ASP.NET web-app.

Every SQLConnection I instantiate is based on the same connection string :

sqlcon = New
SqlConnection(ConfigurationSettings.AppSettings("my-db-connect-string"))

Therefore, I assume connection pooling is in full force.

For this scenario, what I am trying to figure out is the *relative* cost of
connection instantiation versus connection open.

Because of my OOP approach to ASP.NET, my code needs to open and close
connections many times during the server processing of a page-request. I
already decided that I am not going to sacrifice OOP in order to gain the
possible performance benefit of performing a single open/close connection
per page-request.

So, the remaining question is : do I implement my ado-objects once as
members of a single class, and instantiate this class (constructor
instantiates its member objects) once per page-request?

Or can I be more cavalier about things and instantiate & utilize ado-objects
in an ad-hoc manner?
 
John,

I have a standard model I use for all my web applications in ASP.Net. It
involves developing classes that represent the tables in the database. At
runtime, each page opens a single connection, then passes that connection to
any of the objects I am creating in the page via their constructor. It
allows me to maintain a very nice OOP model and still manage my connections
in a way that works.

To answer the question you were really asking though, It is costing you a
fair amount to open and close the connections if you are doing it multiple
times per page. In this case you are instantiating many instances of the
connection object (object creation + garbage collection on those objects
will add some overhead) , plus you have the over head of "opening" and
"closing" the connection. Even if you are getting it from the Connection
Pool you might want to consider reducing to a single connection object.

Kirk Graves
KRGIT Software
 
Back
Top