The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

How to manage a database connection in a loop?

I have a C# loop that calls n times an Oracle stored procedure through ODP.NET. It is better to  1) open a database connection a reuse it n times through the loop and then close the connection or it is better to 2) open and close the connection more atomically every time I call the stored procedure.
Thanks.
Glueless
Thursday, September 15, 2005
 
 
I'd say that if you're calling a connection from the Connection Pool within the loop, you should be fine no matter what.

The Connection Pool should know if it has one available and unused and can pass it along OR if it needs to create another.
KC Send private email
Thursday, September 15, 2005
 
 
It's better to open the connection once, use it multiple times in the loop, then close it.

The less stuff you have to do in a loop, the better, in terms of performance.  Personally, I don't know what the "price" is in terms of time and waiting to open a database connection under .NET.  I assume it's not zero, though.

So, if it's not zero, then you save time by moving the open before the loop, and the close after the loop.
AllanL5
Thursday, September 15, 2005
 
 
" It's better to open the connection once, use it multiple times in the loop, then close it."

Unless something goes bad on it... if it requires some sort of reset and/or intialization, it shouldn't be handled in the loop.

Go with a Connection Pool (essentially a variation on a Singleton Pattern).
KC Send private email
Thursday, September 15, 2005
 
 
Depends:


/* if you don't keep connections for too long */
get connection from pool  // wait until you get one
begin transaction
loop // through all data
  ...
end loop
commit/rollback transaction
close connection // returns the connection into the pool

Othewise

/* for large & lengthy bulk operations */
loop // through all savepoints
  get connection from pool // wait until you get one
  begin transaction
  loop // through all (savepoint) data
      ...
  end loop
  commit/rollback transaction
  /* releasing here allows the pool to optimze and share
    connections across the system. If you don't release
    the system may end up in resource starvation.
    This way you slow down the entire system but no
    process will starve for connections. */
  close connection // returns connection into the pool
end loop


Of course, to prevent deadlocking and such you would have to implement timeouts and retries. Welcome to the server side programming!
Dino Send private email
Thursday, September 15, 2005
 
 
>> Of course, to prevent deadlocking and such you would have to implement timeouts and retries. Welcome to the server side programming!

Less so in Oracle than in other vendor's attempts at concurrency, of course. Oracle = "writers don't block readers, readers don't block anything"

:D
David Aldridge Send private email
Tuesday, September 20, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz