At 06:28 PM 6/25/2000 +0100, you wrote:
> >>> Dave Ford <[EMAIL PROTECTED]> 25-Jun-00 5:16:03 PM >>>
> >2. Pooled JDBC Connections:
> >a. One pool per servlet (Pool as static variable of servlet)
> >b. One pool per application (Store pool in ServletContext)
> >c. One pool per application (Store pool as singleton)
> >d. One pool per application (Store pool in jndi server)
> >It seems to me that 2d, is what Sun is recommending.
>
>For MOST applications 2d is good (though I personally prefer 2b).
>
>But another excellent way of doing it is to have a Connection per
>session. You would use that in an application that required
>authenticated connections or some low-level (eg: table level)
>security.
>
>It's best suited to apps where the user is going to be doing querying
>over a long period of time and the querying is specific to the user
>(perhaps an accounting tool where each user has different rights).
You need to be careful with both of these in performance-sensitive
applications--it's very tempting to stash connections in sessions, but if
you're going to have a large number of users it's not going to be practical
- even large RDBMS instances will slow down once you start loading the
connections on at a high level. You've also got to watch out for proper
transaction management on session expiration, which means writing Session
event handlers (not a big deal, just remember to do it). Garbage Collection
doesn't work that well on Connection objects for a variety of reasons, so
you have to make absolutely sure that everything is explicitly closed.
Finally, since sessions aren't serializable, you're increasing the memory
load on the server fairly dramatically.
None of which is to say that this isn't a useful technique in some
situations, as Nic described. The two cases where I would suggest this are
when authentication runs through the database's native authentication
system, and when transactions might run across multiple servlet
invocations. However, there are relatively few situations where this will
come up: authenticating through the database is a pretty resource intensive
operation, and you're probably better served storing this information in an
LDAP server (via JNDI) or a database table accessed by the server via a
generic account. I'd also suggest structuring your application to avoid the
necessity of stringing a transaction out across multiple invocations: use
intermediary tables and hold on to an identifier rather than a whole
connection.
I'd be interested in hearing about apps where this kind of system was
required - I'm sure there are some out there.
I tend towards a variant of 2a - all of the classes in a particular
application are subclassed from a core functionality servlet, which
contains the connection pool or pools. Not ideal in all situations, but
it's a nice balance of flexibility and restrictiveness (other applications
can't get to the pool, for instance).
>There are some things that you've missed.
>
>
>- Pooling other SQL objects
>There are general pooling classes available to allow you to pool any
>object. You could, for example, pool PreparedStatements with such a
>pool - you setup the PS in the init() method - add it to the
>generalized pool and checkout/in as needed in the service method.
>
>This works well when you have one, or a few, queries that each user
>of the system will repeatedly execute and when those queries are
>generalized across all users.
Yes, but the performance benefits here aren't as dramatic--in some cases
you may find the pool overhead almost cancels out the benefit of pooling.
For most databases, SQL is compiled and cached by the database, not the
JDBC driver classes. PreparedStatement is a "Prepared Statement" because
you can substitute data values. Precompiling and caching are done by the
driver--there's not much overhead in the prepareStatement() call itself.
For instance, take following two pieces of code:
Statement stmt = dbCon.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM CONSUMERS");
PreparedStatement pstmt = dbCon.prepareStatement("SELECT * FROM PRODUCERS");
ResultSet rs2 = pstmt.executeQuery()
(assume CONSUMERS and PRODUCERS are identical - the different names would
keep Oracle from caching the SQL after the Statement and using it in the
PreparedStatement). If you ran this code a second time, both statements
would run faster, as the database will precompile and cache from the
Statement object as well.
However, "SELECT * FROM CONSUMERS WHERE ID = 2" and "SELECT * FROM
CONSUMERS WHERE ID = 9" are different (in fact, Oracle is smart enough that
it will cache these too, but never mind). Using a PreparedStatement with ?
variable substitutions makes it easier for the database to cache its
execution instructions for the statement.
Hopefully that was relatively clear. The bottom line? You can get a
performance boost by keeping your SQL consistent across invocations. Use
PreparedStatements, and if something is used in more than one place in your
code, try storing the SQL as a static String somewhere.
>- deliver a pool with your application
>Portability is not so much of a concern with a simple pooling system.
>It would be nice if all JDBC drivers offered pooling but until they do
>it's safer to just deliver a pooling class with your application.
Definitely.
---
William Crawford + 01 617 577 7844
Invantage, Inc [EMAIL PROTECTED]
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
