Connetion pooling and statement caching: pros and cons

Thread view:

?skar Sturluson - 01 Oct 2003 17:04 GMT

Hello all,

I am wondering if someone here knows of a good overview (preferably
http accessible) of the pros and cons of Connection pooling and
statement caching.

Also: does anyone know if it is possible to use a plain connection
object (i.e. not pooled) but still give seperate threads their own
transactions? My research here indicates this is not possible, but it
doesn't hurt to ask.

A litle background on why I am asking:

I am developing a web application using Oracle 9i. I have made two
test applications, one using pooling with stament caching and the
other using a plain connection.

What I like about the pooling solution is that each user gets a
virtual connection with its seperate transaction control.
What I dislike is that when a users connection is closed all
statements in the statement cache are cleared. This is akward since
it means that statements have to re-prepared for each user, thus
greatly dimishing the value of the statement cache.

The plain connection solution has the drawback that having seperate
transactions for different clients seems impossible. Of course I also
need to implement my own statement caching, but that is straight
forward: just use HashMaps to store prepared statments. But when it
comes to transactions I am at a loss. Commiting attempts to commit
for all clients/threads, since there is just one user logged on
through the connection.

On the whole the plain connection implementation appears to be
considerable faster (i.e. for lookup and querying), although I haven't
done any real "scientific" measurements on that.

thanks,
Oskar

Reply to this Message

G Winstanley - 01 Oct 2003 22:59 GMT

> Hello all,
>
[quoted text clipped - 34 lines]
> thanks,
> Oskar

The transaction control requirement does seem to rule out having a single
connection, so what seems appropriate is a minimalist pooling technique
which allows you just enough connections as required for the transaction
management, and one that also provides statement caching. Obviously and
shamelessly plugging my code you could try my DBPool at
http://www.snaq.net/java/DBPool/ which does PreparedStatement caching and
avoids you having to reinvent the wheel, so to speak.

Bear in mind, however, that one problem you may encounter is if you need to
cast Connections to the Oracle-specific type to perform Oracle-specific
calls. Because the Connections are wrapped it is an impossible cast, so I
would only recommend this solution if you are using generic JDBC-compatible
code and not Oracle tailored code.

Stan

Reply to this Message

Robert Klemme - 02 Oct 2003 09:03 GMT

> Hello all,
>
[quoted text clipped - 6 lines]
> transactions? My research here indicates this is not possible, but it
> doesn't hurt to ask.

Your research is correct since one Connection cannot server more than one
transaction at a time.

> A litle background on why I am asking:
>
[quoted text clipped - 8 lines]
> it means that statements have to re-prepared for each user, thus
> greatly dimishing the value of the statement cache.

This is easily solved: write a wrapper class for Connections and
PreparedStatements and put that into the pool:

class PooledConnectionContext {
private final Map cache = new HashMap();

public Connection getConnection() ...
public PreparedStatement getPreparedStatement( String stmt )
throws SQLException {
PreparedStatement pst = ( PreparedStatement ) cache.get( stmt );
if ( pst == null ) {
pst = getConnection().prepareStatement( stmt );
cache.put( stmt, pst );
}
return pst;
}

public void clearStatementPool() throws SQLException {
for ( Iterator iter = cache.values().iterator() ; iter.hasNext(); ) {
PreparedStatement pst = ( PreparedStatement ) iter.next();
pst.close();
iter.remove();
}
}
}

You get the picture. This is often sufficient for applications that
reexecute the same statements over and over again.

> The plain connection solution has the drawback that having seperate
> transactions for different clients seems impossible. Of course I also
[quoted text clipped - 3 lines]
> for all clients/threads, since there is just one user logged on
> through the connection.

This is not a solution: if you need multiple concurrent transactions you
need multiple Connections.

Regards

robert

Reply to this Message

?skar Sturluson - 02 Oct 2003 19:10 GMT

> > Hello all,
> >
[quoted text clipped - 66 lines]
>
> robert

thank you very much for this feedback Robert,

On the other hand I am not sure if your suggestion, for statement
caching in the pooled connection environment, would work for me.

Well maybe I didn't describe the problem that well in the first place.
Each client/user gets it own (logical) connection. The built in
statment cache (an Oracle thing) for each logical connection works
fine for each user.
The problem (as I see it) is that statements have to prepared for each
user (i.e. there is a sperate statement cache for each logical
connection)
But this is a big draw-back since in my application all connection
will have indentical statements prepared. The only difference between
clients are the parameters supplied for the statements, i.e. i have
something like:

dbconn = connCache.getConnection();
....

pstmt = (OraclePreparedStatement)dbconn.prepareStatement("SELECT
empno, empname FROM EMPS WHERE
EID=?",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
if (pstmt.creationState()==OraclePreparedStatement.NEW) {
// ok statment not in cache, let initialize some variables
...
}
pstmt.setFixedCHAR(1,parameter retrieved from the user's (request)
context);

Here i feel that it should be possible to do some optimization.
Considering that all the logical connections are using the same
(pooled) physical connection, one thinks that it should be possible to
somehow share the work involved in preparing the same statements over
and over again.

thanks and best regards
Oskar

Reply to this Message

Joseph Weinstein - 02 Oct 2003 19:23 GMT

Hi.
Statements are associated closely with the given connection they come from.
You do not want multiple threads operating concurrently on a given statement.
If the JDBC driver caches it's statements, it will be per connection, and
presumably this cache will make sure no statement is used simultaneously
by more than one thread or at more than one place in the same stack.
If the statement cache is implemented well, then your call to prepareStatement
or prepareCall() will be quick when it can be satisfied from the cache, and
will do what needs to be done regardless.
Joe Weinstein at BEA.

> > > Hello all,
> > >
[quoted text clipped - 105 lines]
> thanks and best regards
> Oskar

Reply to this Message

?skar Sturluson - 03 Oct 2003 11:51 GMT

Thank you very much Joseph,

Well I was beginning to suspect that this would be the answer. So I
guess I can just stop worrying about this.

Well, a new worry: why bother with preparedstaments and caching at
all? I can see it has great value for many applications, but I don't
see that I will benefit much from it in my case.

So I guess I just use regular statment objects

Thanks again,

Oskar

Reply to this Message

?skar Sturluson - 03 Oct 2003 15:26 GMT

Hi again,

Of course, I have now found a lot of threads which discuss my concern.
Concerning my new problem (i.e. using PreparedStatements vs. regular
statments) I found this post from you Joe:

>>Hi. See the code I sent you in the previous email/post. You should

not/

>>cannot retain and use a reference to a statement that came from a

pool

>>connection that you have closed. We will disable and close any
>>statement etc that has been given to you when you close the

connection.

>>However *we* cache the real DBMS-driver prepared statement, so when
>>you call conn.prepareStatement() again, we will already have the
[quoted text clipped - 3 lines]
>>only live as long as your code has the connection reserved, but the
>>inner object is cached, and we can quickly give you a new wrapper

around

>>the cached object, the next time you get a connection from the pool.
>>Joe

(link http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3B7C5D56.6C7
17E39%40mwwalker.com&rnum=8&prev=/groups%3Fq%3Dprepared%2Bstatements%2B%2Bperfor
mance%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D3B7C5D56.6C717E39%254
0mwwalker.com%26rnum%3D8)

If I understand you correctly here, it seems that you would recommend
using preparedstatements instead of regular statements anyway. Well,
maybe I just need to get into some actual ms measurements. Or what is
the general recommendation concerning this? My user's sessions will
generally be short lived, invlove queries from 6-8 different tables,
and then inserting/updateing 3-4 different tables. Generally user's
will not be doing the same query over and over again during a given
session.

Reply to this Message

Joseph Weinstein - 03 Oct 2003 17:06 GMT

> Hi again,
>
[quoted text clipped - 20 lines]
> >>Joe
> (link http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3B7C5D56.6C7
17E39%40mwwalker.com&rnum=8&prev=/groups%3Fq%3Dprepared%2Bstatements%2B%2Bperfor
mance%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D3B7C5D56.6C717E39%254
0mwwalker.com%26rnum%3D8

The issue isn't what happens during a session. If every session is creating and using prepared statements
for the same SQL (different parameter values are OK), then if the connection caches prepared statements
so that each session that uses a connection re-uses a cached prepared statement, then you win.
BEA's weblogic connection pooling does this. You just code to standard JDBC, as if everything is
new, but under-the-covers regardless of whose JDBC driver is being used, we provide you with
cached Prepared statements when you do a prepareStatement() or prepareCall(). We saw *big*
application speed improvements when we did this.
Joe Weinstein at BEA

Reply to this Message

Robert Klemme - 08 Oct 2003 14:47 GMT

> > (link http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3B7C5D56.6C7
17E39%40mwwalker.com&rnum=8&prev=/groups%3Fq%3Dprepared%2Bstatements%2B%2Bperfor
mance%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D3B7C5D56.6C717E39%254
0mwwalker.com%26rnum%3D8
>
[quoted text clipped - 6 lines]
> application speed improvements when we did this.
> Joe Weinstein at BEA

So you're essentially doing what I proposed - just much more elegantly
packaged (i.e. with a complete JDBC layer on top of the original driver).

:-)

Regards

robert

Reply to this Message

Clemens Anhuth - 04 Oct 2003 01:28 GMT

[...]

> If I understand you correctly here, it seems that you would recommend
> using preparedstatements instead of regular statements anyway.

Oskar,

using prepared statements resolves SQL injection issues too, relieving
you of escaping special characters and the like.

With best regards

Clemens Anhuth

Signature

---------------------------------------------------------------------
Clemens Anhuth com.primebase @ clemens.anhuth
SNAP Innovation Software GmbH
Altonaer Poststrasse 9a, Tel: ++49 (40) 389 044 - 0
22767 Hamburg, Germany FAX: ++49 (40) 389 044 - 44
http://www.primebase.com http://www.snap.de
---------------------------------------------------------------------

Reply to this Message

Clemens Anhuth - 04 Oct 2003 01:20 GMT

[...]

> Here i feel that it should be possible to do some optimization.
> Considering that all the logical connections are using the same
> (pooled) physical connection, one thinks that it should be possible to
> somehow share the work involved in preparing the same statements over
> and over again.

Oskar,

I may be wrong on this, but keep in mind to only optimize what needs to
be optimized (Donald Knuth: "Premature optimization is the root of all
evil").

I would try to keep the design as simple as possible.

That would mean using using a connection pool, using a (real) connection
for everything I need in a transaction, etc. as that is what any other
programmer who will ever have to maintain my project will be expecting.

If I were not to go this direct route there would have to be hard facts
about the benefit - afterall I am about to obscure the system design and
code...

With best regards

Clemens Anhuth

Signature

Reply to this Message

Connetion pooling and statement caching: pros and cons

Free Magazines