Multithreading - One database connection vs multiple connections

[fatcodeguy]

If I have a group of threads inserting data to a database (Oracle 9.2.0.1.0), is it better to have a different connection per thread, or one connection for all threads?

 

[chiph]

Somewhere inbetween would be best.

One per thread means that you would have a lot of connections (which are licensed and cost money) that would be idle a lot of the time.

All threads using the same connection means that the threads get queued up waiting for the connection to become available.

So, it would be best if you had a thread pool of connection objects, and when you need one, request it from the pool, do your work, and then return it to the pool for other threads to use.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[fatcodeguy]

Is the only downside to one thread per connection cost though? If i have say 5 threads, and no need to consider cost, is having 5 connections more efficient/faster ?

 

[chiph]

The other issue would be that the database engine would have to keep track of those five connections, raising memory and CPU requirements on the database server (a machine which cannot afford it because it needs to be running queries, not doing housekeeping stuff).

The other issue is: are you going to put an upper bound on the number of threads? Obviously 5 isn't a big deal, but 500 might be...

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sedj]

use connection pooling. Simple as that. Any decent connection pool will handle load according to the load desired & used - and will expand or shrink the number of connections appropriately.



--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

If the behaviour of your system is totally deterministic, you can handle it without a pool. If not, I agree, a pool is the solution.

Cheers,

Dian

 

[fatcodeguy]

Thanks guys!

For anyone interested, here's a great article on Object Pooling:

http://www.javaworld.com/javaworld/jw-06-1998/jw-06-object-pool.html

 

[fatcodeguy]

How about if I already know how many threads are going to run. If i have 5 threads, wouldn't having 5 connections (and just connecting when the thread initiates) be faster/most efficient?

Thanks

 

[sedj]

what is the problem with connection pooling ? It will handle all your connection threading problems without you even knowing !!!

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

It depends, you need time for software development or to install any already made one. Connection pooling adds a little overhead on processing request to the pool, if it's distributed, you need some transmission objects time.

I agree pooling is the best solution for most systems, but each system is a world, and in some cases you don't need the complication of a pool for a correct functioning.

Cheers.

Dian

 

[fatcodeguy]

Point taken, connection pooling it is!

The second issue is how to implement it.
I figure I have 2 options.
1 - Implement something similar to this
2 - using my RDBMS's implementation of javax.sql.ConnectionPoolDataSource (oracle.jdbc.pool.OracleConnectionPoolDataSource)

// initialize JNDI lookup parameters
            Context ctx = new InitialContext(parms);
...
            ConnectionPoolDataSource cpds = (ConnectionPoolDataSource)ctx.lookup(cpsource);
...
// Following parms could all come from a JNDI look-up
            cpds.setDatabaseName("PTDB");
            cpds.setUserIF("XYZ");
...
            PooledConnection pc = cpds.getPooledConnection();
            Connection conn = pc.getConnection();
...
// do business logic
            conn.close();
      }

Which do you suggest I use?

 

[sedj]

I'm obviously biased, but I have quite a lot of experience in connection pooling, and I find primrose better than most (including by far, oracles own implementation) !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[fatcodeguy]

From what I can tell, primrose can't be used as a part of a stand alone app, and only as part of a container like Tomcat or JBoss. Is this right?

 

[sedj]

The current release only supports being embedded in tomcat or JBoss, but there is a non-released standalone version which I've been meaning to release ... if you want a copy, get in contact via

http://www.primrose.org.uk/contactUs.jsp

.


--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[fatcodeguy]

Thanks!

 

[chiph]

sedj -
Y'all probably need to take this offline here. You don't want to bring on the wrath of site management by being seen as "selling" in the forums.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first