>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