Database Connection Technique

[Mr. John A. Jackson]

Good evening,

In VB 6.0, Delphi & VC++, the basic database connection technique is to
connect at startup, disconnect at shutdown. In ASP.NET (regardless of
language), the technique is to connect on demand.

When developing client/server applications in .NET, which database
connection technique have you found to give you the best performance? Is
ADO.NET designed to perform best using the on demand technique, even in
client/server applications?

Thanks.

 

[William \(Bill\) Vaughn]

We've just discussed this over the last few days. Any design has to balance
scalability and performance. Since most applications don't have to scale
beyond a few hundred users, you can usually get away with connecting to a
server for the life of the application. With MARS you'll be able to (in some
cases) perform multiple operations on the same connection (in 2.0). Without
MARS you'll need to open a second connection when you're returning rows and
want to update at the same time. However, this is not a good practice--I
think that it's a better idea to return the rows you need at the time and
use the same connection to perform any changes. Opening and closing
connections is not expensive when the connections are pooled, but the
operations aren't free.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Miha Markic [MVP C#]]

Hi,

The golden rule is to open a connection as late as possible and close it
asap.
The connection pool handles the physical connections in the background so
there is no performance penality.