RE: [JBoss-dev] Re: [JBoss-user] A little BMP philosophy/understa nding

[Jay W. <jwa...@ne...>]

The cache of statements within the server is global and that is the reason
it gives Oracle a big win in performance vs not cached statements.  Hot SQL
statements are always there just waiting to be executed i.e. their execution
plan is already computed.

Within JDBC the statement objects are bound to a connection, that is how
they are created.  Whether they are tightly bound internally or not I don't
know.  I have only seen these statement caches written at the connection
level and that is how Oracle has done theirs.

I used to work on a web site using Informix and we had a statement cache at
the connection level.  It was there when I started and I never checked to
see if it gave any benefit.  My reading of their docs indicated they didn't
have any cache of these things either.

As for oracle cursors, they are not SQL cursors.  Of course they had to
choose the same word for two different things.  At the API level oracle
cursors correspond to statements.  Within the SQL language cursors
correspond to result sets.  The cursor limit in oracle is on their API level
cursors, so it really maps onto how many open statements a connection will
support at one time.

As for other vendors, it would be interesting to see if they already have or
are considering building this into their JDBC drivers.  This is almost a
place where a layered approach would be nice, sort of like all the
interceptors in the EJB container world, though that is probably overkill
since so far there is only one enable/disable thingy needed.

Cheers
Jay Walters

-----Original Message-----
From: David Jencks [mailto:dav...@ea...]
Sent: Friday, March 23, 2001 12:15 AM
To: jbo...@li...
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
> fi...@fi...
> www.filip.net
> 
> > -----Original Message-----
> > From: Bill Burke [mailto:wb...@co...]
> > Sent: Thursday, March 22, 2001 5:54 PM
> > To: Filip Hanik
> > Cc: jbo...@li...
> > 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
> > > fi...@fi...
> > > www.filip.net
> > >
> > >> -----Original Message-----
> > >> From: jbo...@li...
> > >> [mailto:jbo...@li...]On Behalf Of
> Bill
> > >> Burke
> > >> Sent: Thursday, March 22, 2001 3:29 PM
> > >> To: Filip Hanik
> > >> Cc: jbo...@li...
> > >> 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
> > >>> fi...@fi...
> > >>> www.filip.net
> > >>>
> > >>>> -----Original Message-----
> > >>>> From: jbo...@li...
> > >>>> [mailto:jbo...@li...]On
> > Behalf Of Bill
> > >>>> Burke
> > >>>> Sent: Thursday, March 22, 2001 2:57 PM
> > >>>> To: jbo...@li...
> > >>>> Cc: jbo...@li...
> > >>>> 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: ha...@ts... [mailto:ha...@ts...]
> > >>>>>     Sent: Thursday, March 22, 2001 1:25 PM
> > >>>>>     To: jbo...@li...
> > >>>>>     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 <MJau@eLaw.com>
> > >>>>>     Sent by: jbo...@li...
> > >>>>>
> > >>>>>     03/22/2001 12:42 PM
> > >>>>>     Please respond to jboss-user
> > >>>>>
> > >>>>>
> > >>>>>             To:        "'jbo...@li...'"
> > >>>>>     <jbo...@li...>
> > >>>>>             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:wb...@co...]
> > >>>>>     Sent: Thursday, March 22, 2001 12:10 PM
> > >>>>>     To: jbo...@li...
> > >>>>>     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
> > >>>>>     JBo...@li...
> > >>>>>     http://lists.sourceforge.net/lists/listinfo/jboss-user
> > >>>>>
> > >>>>>     _______________________________________________
> > >>>>>     JBoss-user mailing list
> > >>>>>     JBo...@li...
> > >>>>>     http://lists.sourceforge.net/lists/listinfo/jboss-user
> > >>>>>
> > >>>>>
> > >>>>
> > >>>> _______________________________________________
> > >>>> Jboss-development mailing list
> > >>>> Jbo...@li...
> > >>>> http://lists.sourceforge.net/lists/listinfo/jboss-development
> > >>>>
> > >>
> > >>
> > >> _______________________________________________
> > >> Jboss-development mailing list
> > >> Jbo...@li...
> > >> http://lists.sourceforge.net/lists/listinfo/jboss-development
> > >>
> >
> >
> >
> 
> 
> _______________________________________________
> Jboss-development mailing list
> Jbo...@li...
> http://lists.sourceforge.net/lists/listinfo/jboss-development
> 


_______________________________________________
Jboss-development mailing list
Jbo...@li...
http://lists.sourceforge.net/lists/listinfo/jboss-development