Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
 Posted - 2004-10-14 : 14:10:57
|
| Hi guys.I was reviewing an open source .NET application which used Data Access Layer for each class. So for a User class there would be a UserDB class that provided methods for Inserting, Updating, and Deleting.One thing that I found strange was each method took care of opening and closing a database connection. This means that if there is a batch of database tasks to perform a database connection would have to be opened and closed for each part in the batch.I thought whenever possible you were supposed to reuse a database connection until the end of the batch and then close it. Can't you run into performance problems when you open and close many database connections instead of using a single one?Thanks for reading.Dustin Michaels |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-14 : 14:38:16
|
| It's probably relying on connection pooling so the connection will actually stay open and be reused.It means that there is no posibility of holding transactions across calls.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-14 : 14:39:34
|
| I don't remember Oracle and Watcom ("Sybase Anywhere" or somesuch re-name these days) haveing any issue with pushing multiple queries through a single connection, but SQL gets very upset if you do that - UNLESS the previous query has "finished". So its fine for an atomic task like an INSERT or UPDATE, but not so good for getting a list of Order Headers and then issuing a SELECT for the items on each order in turn.But other than that I would agree with you; in my experience, getting a connection is about 10 times as expensive as doing a "simple select"Kristen |
|
|
|
|
|
|
Question about Database Connections.