This sequence is typically correct (with the close portions in a finally{}
block.) You could add some timing around each of these statements, and
determine the exact impact. Think about the relative complexity of each of
these operations
- Get a context - hashed lookup
- Get a datasource - hashed lookup
- Get a connection - DNS resolution, TCP connection, parameter transmission
and negotiation, authentication... (possibly more depending on the driver
and DB)
- Make the statements - query optimization, caching (or cache lookup if a
repeat), execution
- Close resultsets - clean up
- Close statements - clean up
- Close connection - clean up, close TCP connection
Now, replace the two connection operations with hash-type operations - one
to remove it from a pool (list, collection, hash) and one to put it back.
This is where you get the savings. This leaves the statement execution as
the next largest time consumer, but that is an invariant in this problem.
Empirically, I can tell you I added pooling to an older web app recently and
some 'typical' web requests improved by about 2.5X. More importantly,
Oracle 8i stopped running out of resources, saving operations from nightly
DB restarts.
Tim
-----Original Message-----
From: Warrick Wilson [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 22, 2006 9:24 PM
To: 'Tomcat Users List'
Subject: RE: Setting up connection pools "on the fly"...
> -----Original Message-----
> From: Tim Lucia [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 22, 2006 6:21 AM
> To: 'Tomcat Users List'
> Subject: RE: Setting up connection pools "on the fly"...
>
> The point of connection pooling is to eliminate the overhead
> of setting up and tearing down a (TCP, database, AAA)
> connection for every database transaction (typically, the web
> request in a web app.) This can add 100s or 1000s of
> milliseconds to every request, and is quite expensive.
>
> If you can architect your application so that there is a JNDI
> data source for each server (database server, instance, etc.)
> with its own pool of connections, you can always call
> setCatalog
> (http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.h
> tml#setCatalog
> (java.lang.String)) on the connection returned from the pool.
> One doesn't typically add new database servers "on the fly",
> and this option would require defining a new JNDI datasource
> for a new server. Of course all catalogs processed in this
> way must be accesible to the user specified in the
> connnection pool properties. I would suggest setting the
> defaultCatalog property as well, so that you are not
> accidentally connected to the last-used catalog from the
> previous borrower of the connection.
>
> Tim
Tim - If you could educate me on an aspect of this, I'd hugely appreciate
it...
What part of the standard set of calls for making a JDBC connection is the
part that sets up the connection?
Normally, all the tutorial stuff shows something like (assuming the Tomcat
JNDI stuff is set up in context.xml):
Get a context
Get a datasource
Get a connection
Make the statements
Close resultsets
Close statements
Close connection
Is there a part of this that takes a lot of time? Part of what I need to do
is handle database calls in a couple of different places (at least in the
current design, which I'm the first to admin is likely not optimal). So that
means figuring out how to get at the database in an effective manner as far
as performance goes. I've seen a number of references to "setting up a
connection takes a long time" - I'm not sure which of the calls is the part
that takes a long time.
Thanks...
Warrick
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
