this looks like a promise
http://java.sun.com/aboutJava/communityprocess/jsr/jsr_107_cache.html
although it is in very early stages, it may be useful following the progress
Filip
~
Namaste - I bow to the divine in you
~
Filip Hanik
Software Architect
[EMAIL PROTECTED]
www.filip.net
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Filip
> Hanik
> Sent: Friday, March 23, 2001 3:22 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [JBoss-dev] Re: [JBoss-user] A little BMP
> philosophy/understanding
>
>
> >There are definitely some dbs that do not cache compiled SQL... such as
> >Interbase/Firebird. Until such time as they do, I think the statement
> >cache will be useful. Perhaps part of the configuration of the
> pool could
> >be to turn off the statement caching for e.g. Oracle.
>
> actually in this case, the JDBC driver for Interbase/Firebird should take
> care of that.
> if the driver doesn't, I would recommend letting the people who wrote the
> driver that it can be improved.
> :)
>
>
> Filip
>
> ~
> Namaste - I bow to the divine in you
> ~
> Filip Hanik
> Software Architect
> [EMAIL PROTECTED]
> www.filip.net
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of David
> > Jencks
> > Sent: Thursday, March 22, 2001 9:15 PM
> > To: [EMAIL PROTECTED]
> > Subject: RE: [JBoss-dev] Re: [JBoss-user] A little BMP
> > philosophy/understanding
> >
> >
> > Hi,
> >
> > This has been a very interesting and informative discussion, thanks. I
> > have a couple of further questions...
> >
> > (Along with Jay Walters)-- Do any databases other than Oracle cache
> > compiled SQL? How do you know?
> >
> > In regard to 5, 6 below... It's been a while since I worked with Oracle
> > cursors but I thought an open cursor corresponded roughly to a resultset
> > rather than a statement. Don't you have to, at least in a sp, close a
> > cursor and reopen it to change the parameters? Wouldn't this
> > correspond to
> > closing the resultset rather than the prepared statement?
> >
> > Why isn't the precompiled statement cache db- wide rather than
> > per-session?
> > I would expect most modern apps, ejb based or not, to have all users
> > reusing most of the same SQL.
> >
> >
> > There are definitely some dbs that do not cache compiled SQL... such as
> > Interbase/Firebird. Until such time as they do, I think the statement
> > cache will be useful. Perhaps part of the configuration of the
> pool could
> > be to turn off the statement caching for e.g. Oracle.
> >
> > Thanks,
> >
> > David Jencks
> > On 2001.03.22 21:05:21 -0500 Filip Hanik wrote:
> > > > My application is not caching prepared statements. Jboss is not
> > > caching
> > > > prepared statements. Minerva(the connection pool implementation) is
> > > > caching the prepared statements.
> > >
> > > Yes Minerva is doing this, and it shouldn't do that anymore.
> This is for
> > > drivers written a long long time ago that nobody is using.
> > > You should not cache the statement, if you are using Oracle and are
> > > familiar
> > > with the term Oracle session, you should know that caching a prepared
> > > statement is useless.
> > > A prepared statement is not based on client side logic.
> > >
> > > > Yes, our application code looks very similar to yours. We
> > always close
> > > > our statements and connections in a "finally" block as well. But I
> > > > haven't been talking about application code, but rather the actual
> > > > connection pool implementation itself(Minerva). Maybe there
> > has been a
> > > > miscommunication.
> > >
> > > nope, no miscommunication.
> > >
> > >
> > > this is the way it works, let me try be more clear.
> > >
> > > 1. A physical connection to the database is opened ( a user session is
> > > established)
> > > 2. A programmer gets a connection from a connection pool.
> this is a pass
> > > through reference to the physical connection
> > > 3. A programmer prepares a statement,
> > > 4. The driver sends the SQL to the database, the database interprets,
> > > compiles and caches the SQL in memory associated with the session.
> > > 5. The programmer executes the statement - the driver or the database
> > > recognizes this statement and executes the precompiled
> statement (note!
> > > not
> > > minerva, not jboss, not you as a programmer does this)
> > > 6. The programmer closes the statement, indicating to the
> driver to free
> > > up
> > > resources for this cursor.
> > > 7. the programmer closes the connection indicating to the
> > connection pool
> > > (minerva) to return the physical connection to the pool.
> > > 8. A programmer gets a connection from a connection pool.
> > > 9. A programmer prepares a statement
> > > 10. The driver or the database (not you, not minerva, and not jboss!)
> > > recognizes that this statement is already prepared (string
> matching-very
> > > simple) and ignores the compilation request.
> > > 11. A programmer executes a query, this time, all the driver
> does is to
> > > set
> > > the parameters and re-execute the precompiled statement.
> > >
> > > what is important to remember here, is that the precompiled SQL
> > exists on
> > > the database. there is no way a driver or minerva could implement
> > > anything
> > > that makes precompilation faster on the database. it simply happens on
> > > the
> > > database.
> > >
> > > I know that minerva cache prepared statements and I'm trying
> to explain
> > > to
> > > you why this is not necessary and sometimes even harmful. Like with
> > > Oracle,
> > > minerva could actually be causing problems by caching statements.
> > >
> > > If you want me to be even more clearer, call me tomorrow at
> work 650-875
> > > 1538, and I can explain to you the little secrets of pre-compiling sql
> > > statements into temporary stored procedures.
> > >
> > > Filip
> > >
> > >
> > > ~
> > > Namaste - I bow to the divine in you
> > > ~
> > > Filip Hanik
> > > Software Architect
> > > [EMAIL PROTECTED]
> > > www.filip.net
> > >
> > > > -----Original Message-----
> > > > From: Bill Burke [mailto:[EMAIL PROTECTED]]
> > > > Sent: Thursday, March 22, 2001 5:54 PM
> > > > To: Filip Hanik
> > > > Cc: [EMAIL PROTECTED]
> > > > Subject: Re: [JBoss-dev] Re: [JBoss-user] A little BMP
> > > > philosophy/understanding
> > > >
> > > >
> > > >
> > > >
> > > > Filip Hanik wrote:
> > > >
> > > > >> Unless you actually close the PreparedStatement, it keeps an
> > > > open cursor
> > > > >> into the database, at least with Oracle. Closing the
> > > PreparedStatement
> > > > >> releases all of it's resources and makes it useless for caching.
> > > > >> Somebody correct me if I'm wrong.
> > > > >
> > > > >
> > > > > Bill, why would you cache the prepared statements on the
> > > > application level -
> > > > > please explain.
> > > >
> > > > My application is not caching prepared statements. Jboss is not
> > > caching
> > > > prepared statements. Minerva(the connection pool implementation) is
> > > > caching the prepared statements.
> > > >
> > > > >
> > > > > I tried to earlier explain why this wasn't necessary because
> > > > the connection
> > > > > keeps precompiled code alive on the DB server. it belongs to
> > > > the connection
> > > > > session, not to a statement.
> > > > > Closing the statement (and you should always do this!), closes
> > > > the cursor,
> > > > > but keeps the pre compiled SQL as long as the connection is open.
> > > >
> > > > If your JDBC drivers support XAConnections, pooling, and statement
> > > > caching, don't use Minerva. We're using Minerva so I need
> to fix how
> > > it
> > > > caches PreparedStatements. JDBC doesn't allow you to re-use a
> > > > PreparedStatement after it is closed. So if Minerva is caching
> > > > PreparedStatements, it must wrap the creating and closing
> of them too
> > > so
> > > > it can easily cache them.
> > > >
> > > > How does Minerva know that your DBS is caching pre-compiled SQL?
> > > > Minerva was written to support all JDBC implementations.
> > > >
> > > > Take a look at the code. It's in the jbosscx archive under the
> > > external
> > > > directory. minerva-b3src.zip or something like that.
> > > >
> > > > >
> > > > > if you look at this code, this is how JDBC code should close
> > > > connections and
> > > > > statements.
> > > >
> > >
> > > >
> > > > Regards,
> > > > Bill
> > > >
> > > > >
> > > > > public void mymethod()
> > > > > {
> > > > > Connection con = null;
> > > > > PreparedStatement pstmt = null;
> > > > > try
> > > > > {
> > > > > con = getConnection();
> > > > > pstmt = con.prepareStatement("my sql string");
> > > > > ResultSet rs = pstmt.executeQuery();
> > > > > ...bla bla bla...
> > > > > rs.close();
> > > > > }
> > > > > finally //always make sure resources are cleaned up!!
> > > > > {
> > > > > try
> > > > > {
> > > > > pstmt.close();//this should close dependent result sets
> > > > too if there
> > > > > are any open
> > > > > con.close(); // if this is a connection pool, the connection
> > > gets
> > > > > returned to the pool
> > > > > }catch (Exception ignore() {}
> > > > > }//finally
> > > > > }
> > > > >
> > > > > ~
> > > > > Namaste - I bow to the divine in you
> > > > > ~
> > > > > Filip Hanik
> > > > > Software Architect
> > > > > [EMAIL PROTECTED]
> > > > > www.filip.net
> > > > >
> > > > >> -----Original Message-----
> > > > >> From: [EMAIL PROTECTED]
> > > > >> [mailto:[EMAIL PROTECTED]]On
> Behalf Of
> > > Bill
> > > > >> Burke
> > > > >> Sent: Thursday, March 22, 2001 3:29 PM
> > > > >> To: Filip Hanik
> > > > >> Cc: [EMAIL PROTECTED]
> > > > >> Subject: Re: [JBoss-dev] Re: [JBoss-user] A little BMP
> > > > >> philosophy/understanding
> > > > >>
> > > > >>
> > > > >> Filip Hanik wrote:
> > > > >>
> > > > >>> oops, we are cross posting, no good, sorry about that.
> > > > >>>
> > > > >>>> I'm re-writing the PreparedStatement cache
> > > > >>>> - so that it is configurable from jboss.jcml. I can no way
> > > > right now
> > > > >>>> other than changing the source directly of configuring the PS
> > > > >>>
> > > > >> cache size.
> > > > >>
> > > > >>>> - so that connections watch all open cursors that are
> > > > >>>
> > > > >> created(Statements
> > > > >>
> > > > >>>> and PreparedStatements). Basically if you have 50
> > > PreparedStatements
> > > > >>>> cached, your max open cursors is 50, and you want to
> > > > createStatement,
> > > > >>>> I'm making the connection release one of the cached
> > > > >>>
> > > > >> PreparedStatement so
> > > > >>
> > > > >>>> that the new createStatement won't fail.
> > > > >>>
> > > > >>>
> > > > >>> remember that during a transaction, the connection (JDBC 1) used
> > > gets
> > > > >>> associated with the transaction context.
> > > > >>> hence, other transactions/threads will not be accessing the
> > > connection
> > > > >>> during that time since they are not involved in this DB
> > > transaction.
> > > > >>
> > > > >> Good. Just making sure. (more comments follow)
> > > > >>
> > > > >>> why would you want to keep the cursors open. once you retrieved
> > > your
> > > > >>> resultset, your are done with the cursor and should close it.
> > > > >>
> > > > >> the prepared
> > > > >>
> > > > >>> statement is nothing but precompiled (during runtime)
> SQL, and if
> > > the
> > > > >>> connection should keep this precompiled statement alive on the
> > > > >>
> > > > >> database, not
> > > > >>
> > > > >>> through the PreparedStatement reference that the programmer
> > > > holds in his
> > > > >>> code.
> > > > >>>
> > > > >>> can somebody please tell *ME* to shut up, if I am completely
> > > > off balance
> > > > >>> here :)
> > > > >>>
> > > > >>> Filip
> > > > >>
> > > > >> Unless you actually close the PreparedStatement, it keeps an
> > > > open cursor
> > > > >> into the database, at least with Oracle. Closing the
> > > PreparedStatement
> > > > >> releases all of it's resources and makes it useless for caching.
> > > > >> Somebody correct me if I'm wrong.
> > > > >>
> > > > >> BTW, please don't shut up. I'm no JDBC expert, just
> trying to make
> > > the
> > > > >> PS caching work so that my application will succeed. :-)
> > > > >>
> > > > >> Bill
> > > > >>
> > > > >>
> > > > >>> ~
> > > > >>> Namaste - I bow to the divine in you
> > > > >>> ~
> > > > >>> Filip Hanik
> > > > >>> Software Architect
> > > > >>> [EMAIL PROTECTED]
> > > > >>> www.filip.net
> > > > >>>
> > > > >>>> -----Original Message-----
> > > > >>>> From: [EMAIL PROTECTED]
> > > > >>>> [mailto:[EMAIL PROTECTED]]On
> > > > Behalf Of Bill
> > > > >>>> Burke
> > > > >>>> Sent: Thursday, March 22, 2001 2:57 PM
> > > > >>>> To: [EMAIL PROTECTED]
> > > > >>>> Cc: [EMAIL PROTECTED]
> > > > >>>> Subject: [JBoss-dev] Re: [JBoss-user] A little BMP
> > > > >>>> philosophy/understanding
> > > > >>>>
> > > > >>>>
> > > > >>>> In JBoss 2.1 (minerva beta3?) the PreparedStatement cache does
> > > have a
> > > > >>>> limit. Also, when the cache reaches it's limit, it removes the
> > > least
> > > > >>>> recently used PS and closes it.
> > > > >>>>
> > > > >>>> - Is it useful to block if the max open cursors have been
> > > > reached when
> > > > >>>> creating a new Statement or PreparedStatement? This
> > would only be
> > > > >>>> useful if more the one thread had access to the connection, but
> > > does
> > > > >>>> that ever happen, and is it allowed to happen?
> > > > >>>>
> > > > >>>> Bill
> > > > >>>>
> > > > >>>> Mike Jau wrote:
> > > > >>>>
> > > > >>>>> So, the caching of the PreparedStatement is stored in the
> > > database
> > > > >>>>> connection context and is not shared between the database
> > > > connection.
> > > > >>>>> I am thinking a work around way and it may solve the caching
> > > > >>>>> issue. If we have the "named connection" from the
> pool with the
> > > > >>>>> lifecyclye control to release the PreparedStatement from the
> > > > >>>>> applicaiton which invoke the container specific API,
> it probably
> > > can
> > > > >>>>> solve the problem.
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> - Mike Jau
> > > > >>>>>
> > > > >>>>> -----Original Message-----
> > > > >>>>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > > > >>>>> Sent: Thursday, March 22, 2001 1:25 PM
> > > > >>>>> To: [EMAIL PROTECTED]
> > > > >>>>> Subject: RE: [JBoss-user] A little BMP
> > > philosophy/understanding
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> In the original JBoss 2.0 version the PreparedStatement
> > > > cache was
> > > > >>>>> not discarded after the connection was returned
> to the pool
> > > > >>>>> because more than likely you might want to issue that
> > > > one of these
> > > > >>>>> PreparedStatements again. To make matters worse
> there wasn't
> > > an
> > > > >>>>> upper limit on the number of PreparedStatement
> > objects in the
> > > > >>>>> cache so things would continue to grow as you prepared new
> > > SQL
> > > > >>>>> statements. If you happened to prepare the same exact SQL
> > > > >>>>> statement then you received the previously cached
> > > > >>>>> PreparedStatement object but otherwise you got a new
> > > > >>>>> PreparedStatement that was also added to the cache. This
> > > would
> > > > >>>>> continue until either a) the database complained or b)
> > > > you ran out
> > > > >>>>> of memory which ever came first. On Oracle, for
> > example, each
> > > > >>>>> PreparedStatement takes memory on the database
> and once you
> > > hit
> > > > >>>>> 100 or so the database throws an exception when you try to
> > > get
> > > > >>>>> another one.
> > > > >>>>>
> > > > >>>>> I patched the code by releasing the
> PreparedStatement cache
> > > when
> > > > >>>>> the Connection was released and submitted that fix but I'm
> > > not
> > > > >>>>> sure it was accepted. What really needs to happen
> > is that the
> > > > >>>>> PreparedStatement cache needs to be enhanced so
> > that an upper
> > > > >>>>> bound can be established via a configuration
> > variable so that
> > > > >>>>> after x PreparedStatements have been cached new
> > > > PreparedStatements
> > > > >>>>> will push one of the old ones out of the cache.
> > > > >>>>>
> > > > >>>>> - Jon Harvie
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> Mike Jau <[EMAIL PROTECTED]>
> > > > >>>>> Sent by: [EMAIL PROTECTED]
> > > > >>>>>
> > > > >>>>> 03/22/2001 12:42 PM
> > > > >>>>> Please respond to jboss-user
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> To: "'[EMAIL PROTECTED]'"
> > > > >>>>> <[EMAIL PROTECTED]>
> > > > >>>>> cc:
> > > > >>>>> Subject: RE: [JBoss-user] A little BMP
> > > > >>>>> philosophy/understanding
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> Could you give me some background information about the
> > > > >>>>> Preparedstaement
> > > > >>>>> caching on the EJB container side?
> > > > >>>>>
> > > > >>>>> Since the connection get from pool need to return to
> > > > pool once the
> > > > >>>>> transaction done. I assumed that the resouce associate to
> > > this
> > > > >>>>> connection
> > > > >>>>> should be released and the released resoure include the
> > > > >>>>> preparedstatement.
> > > > >>>>> Later on, the create preparedstatement will be invoked
> > > > again from
> > > > >>>>> different
> > > > >>>>> connection. How the preparedstatement cached is
> my question?
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> - Mike
> > > > >>>>>
> > > > >>>>> -----Original Message-----
> > > > >>>>> From: Bill Burke [mailto:[EMAIL PROTECTED]]
> > > > >>>>> Sent: Thursday, March 22, 2001 12:10 PM
> > > > >>>>> To: [EMAIL PROTECTED]
> > > > >>>>> Subject: Re: [JBoss-user] A little BMP
> > > philosophy/understanding
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> Dan Christopherson wrote:
> > > > >>>>>
> > > > >>>>> > On Fri, 23 Mar 2001, Peter Routtier-Wone wrote:
> > > > >>>>> >
> > > > >>>>> >>> Someone from this discussion group indicate that
> > > container
> > > > >>>>> might cache
> > > > >>>>> the
> > > > >>>>> >>> PreparedStatement.
> > > > >>>>> >>
> > > > >>>>> >> I can't speak with authority on this, but that
> > > > rings true. I'm
> > > > >>>>> guessing
> > > > >>>>> that
> > > > >>>>> >> interception doesn't happen for the setEntityContext()
> > > > >>>>
> > > > >>>> method and
> > > > >>>>
> > > > >>>>> therefore
> > > > >>>>> >> you actually create a PreparedStatement rather than
> > > > receiving
> > > > >>>>> one from
> > > > >>>>> the
> > > > >>>>> >> pool.
> > > > >>>>> >>
> > > > >>>>> >>> Just for kicks, I gave it a try but
> > transactions weren't
> > > > >>>>> completed and
> > > > >>>>> >>> they'd just hang out there forever, blocking
> > every other
> > > > >>>>> persistence and
> > > > >>>>> >>> finder method until they timed out.
> > > > >>>>> >>
> > > > >>>>> >> That would bollox lifecycle management, and the
> > described
> > > > >>>>> behaviour
> > > > >>>>> wouldn't
> > > > >>>>> >> be at all surprising.
> > > > >>>>> >
> > > > >>>>> > This is also a common bean bug: 'close()' should be
> > > > >>>>
> > > > >>>> called on every
> > > > >>>>
> > > > >>>>> > resultset, statement, and connection in a
> finally clause
> > > so
> > > > >>>>> that you know
> > > > >>>>> > it happens every time.
> > > > >>>>> >
> > > > >>>>> >> On the other hand, I'd have thought that
> > > PreparedStatements
> > > > >>>>> would be far
> > > > >>>>> >> less costly to manufacture than Connections, and
> > > > therefore not
> > > > >>>>> worth the
> > > > >>>>> >> overhead of managing a pool. I think I'll poke my nose
> > > into
> > > > >>>>> the source
> > > > >>>>> and
> > > > >>>>> >> see what's there.
> > > > >>>>> >
> > > > >>>>> > There's often communication with the database to create
> > > the
> > > > >>>>> > PreparedStatement. That way it can pre-compile a query
> > > plan.
> > > > >>>>> There is a
> > > > >>>>> > prepared statement cache in JBoss: in JBoss 2.0,
> > it caused
> > > > >>>>> problems with
> > > > >>>>> > Oracle's cursor limit (fixed in 2.1).
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> I'm re-writing the minerva PreparedStatement caching so
> > > > it handles
> > > > >>>>> cursor limit better. I'll submit the code tomorrow after
> > > > >>>>
> > > > >> I test it.
> > > > >>
> > > > >>>>> Bill
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> _______________________________________________
> > > > >>>>> JBoss-user mailing list
> > > > >>>>> [EMAIL PROTECTED]
> > > > >>>>> http://lists.sourceforge.net/lists/listinfo/jboss-user
> > > > >>>>>
> > > > >>>>> _______________________________________________
> > > > >>>>> JBoss-user mailing list
> > > > >>>>> [EMAIL PROTECTED]
> > > > >>>>> http://lists.sourceforge.net/lists/listinfo/jboss-user
> > > > >>>>>
> > > > >>>>>
> > > > >>>>
> > > > >>>> _______________________________________________
> > > > >>>> Jboss-development mailing list
> > > > >>>> [EMAIL PROTECTED]
> > > > >>>> http://lists.sourceforge.net/lists/listinfo/jboss-development
> > > > >>>>
> > > > >>
> > > > >>
> > > > >> _______________________________________________
> > > > >> Jboss-development mailing list
> > > > >> [EMAIL PROTECTED]
> > > > >> http://lists.sourceforge.net/lists/listinfo/jboss-development
> > > > >>
> > > >
> > > >
> > > >
> > >
> > >
> > > _______________________________________________
> > > Jboss-development mailing list
> > > [EMAIL PROTECTED]
> > > http://lists.sourceforge.net/lists/listinfo/jboss-development
> > >
> >
> >
> > _______________________________________________
> > Jboss-development mailing list
> > [EMAIL PROTECTED]
> > http://lists.sourceforge.net/lists/listinfo/jboss-development
> >
>
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-development
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development