Connection Pooling with thin driver for Oracle 9i
I know how to create a DataSource that utilizes connection pooling for my webapp using my web server. The problem is that my DAOs are also used for a standalone app, as well as standalone testing. I'd really like to be able to use connection pooling for that as well. As far as I can tell, though, I have to use an OracleConnectionPoolDataSource to get a PooledConnection and get my Connection from the PooledConnection. Not only is that awkward and annoying, but it breaks the other code. Any suggestions?
Thanks.
[528 byte] By [
betseyba] at [2007-11-27 10:51:24]
# 1
A non connection pooling takes about 1 to 2 seconds for each and every read/write to the database (end users will find this very annoying). Connection pooling takes a few milliseconds. Therefore I think its worthwhile to change your code to support connection pooling only.
Your code that runs in a container such as tomcat should get the datasource via JDNI from the datasource tag in the tomcat/config/server.xml or context.xml file. Tomcat will instansiate a connection pool using that tag to find a datasource object in your Oracle's JDBC jar file such as odjbc14.jar
If you are running code outside of tomcat enviornment such as JUNIT testing, main(), or a stand alone application, you need to create your own connection pool datasoruce (one instance for all) that programs can use. Somehow you have to tell your DAOs to use the connection pool from either the one provided by tomcat or from your standalone connection pool. The way I do it is to use JDNI to get it from tomcat, if not found, it knows its running stand alone, instead of throwing an error, I next try to get one from the local connection pool. If that too is not available, throw an exception. There are many variations of this concept that you may consider. Its up to you.
# 2
> A non connection pooling takes about 1 to 2 seconds
> for each and every read/write to the database (end
> users will find this very annoying). Connection
> pooling takes a few milliseconds. Therefore I think
> its worthwhile to change your code to support
> connection pooling only.
Not in my testing it doesn't.
Even on a developement lan with quite a few people establishing a connection is far less than a second.
Now if you have a lot of volume then that small savings adds up.
# 3
> I know how to create a DataSource that utilizes
> connection pooling for my webapp using my web
> server. The problem is that my DAOs are also used
> for a standalone app, as well as standalone testing.
> I'd really like to be able to use connection pooling
> for that as well. As far as I can tell, though, I
> have to use an OracleConnectionPoolDataSource to get
> a PooledConnection and get my Connection from the
> PooledConnection. Not only is that awkward and
> annoying, but it breaks the other code. Any
> suggestions?
>
Use a factory that returns a connection. For one configuration it returns a pooled connection, in another it doesn't.
# 4
Responding to everything at once:
- In our testing, getting a non-pooled connection took 500ms, which was unacceptable. I know that connection pooling is valuable, that's why I wanted to use it, even when I do not have a container available to do it for me.
- I figured it out on my own. I was able to create a OracleConnectionCacheImpl and set it as the DataSource, which made things work. It's deprecated in later versions of the driver, but it's what's available in the version we are forced to use so that's what I did. A method that previously took 15 seconds now takes 1.5. I'm happy with an order of magnitude improvement.
# 5
> - In our testing, getting a non-pooled connection
> took 500ms, which was unacceptable. I know that
> connection pooling is valuable, that's why I wanted
> to use it, even when I do not have a container
> available to do it for me.
>
> - I figured it out on my own. I was able to create a
> OracleConnectionCacheImpl and set it as the
> DataSource, which made things work. It's deprecated
> in later versions of the driver, but it's what's
> available in the version we are forced to use so
> that's what I did. A method that previously took 15
> seconds now takes 1.5. I'm happy with an order of
> magnitude improvement.
That would strongly suggest that you are using multiple connections rather than one in the "method". And that would further require that you are using multiple statements.
That would be the problem.
Note that using a single statement block rather than multiples should always produce a measurable speed improvement.
# 6
> > - In our testing, getting a non-pooled connection
> > took 500ms, which was unacceptable. I know that
> > connection pooling is valuable, that's why I
> wanted
> > to use it, even when I do not have a container
> > available to do it for me.
> >
> > - I figured it out on my own. I was able to create
> a
> > OracleConnectionCacheImpl and set it as the
> > DataSource, which made things work. It's
> deprecated
> > in later versions of the driver, but it's what's
> > available in the version we are forced to use so
> > that's what I did. A method that previously took
> 15
> > seconds now takes 1.5. I'm happy with an order of
> > magnitude improvement.
>
> That would strongly suggest that you are using
> multiple connections rather than one in the "method".
> And that would further require that you are using
> multiple statements.
>
> That would be the problem.
>
> Note that using a single statement block rather than
> multiples should always produce a measurable speed
> improvement.
I would agree, but that's not possible for what I am doing. The world would also be a happier place if everyone had milk and cookies every day at 2pm, but I'm not holding my breath.
Thanks anyway.
# 7
Hi
I have developed a multi threaded desktop application which uses Singleton pattern. I used a single database connection for all the threads using a single connection object and now I have used connection pooling instead of that single database connection.
I want to know whether I can get any performance improve from this change
Also When I closed the pooled connection will it automatically release the created Statements and Resultsets or do I have to close the physical connection to release the Statements completely ?
Thank you
# 8
> I want to know whether I can get any performance
> improve from this change
It depends on what the application does.
If you are making many requests in many threads then probably.
> Also When I closed the pooled connection will it
> automatically release the created Statements and
> Resultsets or do I have to close the physical
> connection to release the Statements completely ?
You still must close them.
