 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / July 2008[Info-Ingres] jdbc caches prepared statements?
Hi Everyone, I have a jdbc application which maintanes a connection to a database. Every morning, the application fires up and runs a set of queries using this long standing connection. To generate a set of reports the application runs the same sets of queries, using a prepare statement, open cursor for statement, fetch data and close cursor. From one day to another, the code used should be exactly the same. I have tracked this using querytext auditing.... Why is it that even though the code says 'prepare this statement', some queries do not seem to have a prepared statement executed more than once? They use statements of the same name prepared several days earlier by prior executions of the application. For example: This was prepared and used on 18/07/2008 03:04:11. SELECT G.aid rowid, V.field_id field_id, V.value value, COUNT(*) n FROM REP_GOBZ G, INT_VALUE V WHERE 1=1 AND G.status = 1000 AND G.pid >= 100000000 AND G.pid <= 899999999 AND V.field_id = ? AND G.rep_id = V.rep_id GROUP BY 1,2,3 The same section of code ran the next day, but the querytext auditing shows no sign of the prepare statement, only the cursor open for readonly. Trouble was, in between the executions the tables were dropped and recreated, and the second (and subsequent) executions generated some weird messages in the errlog, which is how I found it. Martin Bowes Hi Marty, yes, the JDBC driver caches prepared statements. Whenever the prepareStatement(...) method is used, the JDBC driver scans an internal list to see whether that statement(identified by the query text) has been already prepared. This list is cleared, when a transaction is commited, which is in line with the behaviour of the DBMS. I guess your application uses autocommit, in this case prepared statements are not cleared (which is again the same behaviour as in the DBMS). The obvious workaround for your application would be to send a commit, but keep in mind that the JDBC driver does some "optimization", the commit is simply ingnored in autocommit mode or if there is no transaction open. So you would need to switch off autocommit first, then open a transaction ("select 1" for example) and then doing the commit before switching autocommit on again. Kristoff Hi Kristoff, Thanks for the response, it certainly ties in what we see, and yes we are running with autocommit. But.... Making a distinction between autocommit and regular commit for the caching of prepared statements (or anything else) just seems to suck out load as far as I can see. Is there a good reson for this behaviour? Marty -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of kristoff.picard@ingres.com Sent: 30 July 2008 09:18 To: info-ingres@kettleriverconsulting.com Subject: Re: [Info-Ingres] jdbc caches prepared statements? Hi Marty, yes, the JDBC driver caches prepared statements. Whenever the prepareStatement(...) method is used, the JDBC driver scans an internal list to see whether that statement(identified by the query text) has been already prepared. This list is cleared, when a transaction is commited, which is in line with the behaviour of the DBMS. I guess your application uses autocommit, in this case prepared statements are not cleared (which is again the same behaviour as in the DBMS). The obvious workaround for your application would be to send a commit, but keep in mind that the JDBC driver does some "optimization", the commit is simply ingnored in autocommit mode or if there is no transaction open. So you would need to switch off autocommit first, then open a transaction ("select 1" for example) and then doing the commit before switching autocommit on again. Kristoff _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres Hi Marty, the JDBC driver needs to follow the DBMS here. For the DBMS, in theory I would agree, but it turned out that changing this behaviour, which was tried years ago, introduced other problems - so that change was removed. But back to the JDBC application: When using PreparedStatement.close(), LATER versions of the driver should remove this statement from the internal cache (so you have to re-prepare before executing it again). It also frees the internal statement name, so if that name is used again to prepare a statement, the old statement in the DBMS is replaced by the new one(similar to embedded sql). You need to have the fix for bug 117108 for this. (I haven't tested this, but this is my understanding) Kristoff Hi Kristoff, I'll check for a fix to that bug number. Marty -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of kristoff.picard@ingres.com Sent: 30 July 2008 13:28 To: info-ingres@kettleriverconsulting.com Subject: Re: [Info-Ingres] jdbc caches prepared statements? Hi Marty, the JDBC driver needs to follow the DBMS here. For the DBMS, in theory I would agree, but it turned out that changing this behaviour, which was tried years ago, introduced other problems - so that change was removed. But back to the JDBC application: When using PreparedStatement.close(), LATER versions of the driver should remove this statement from the internal cache (so you have to re-prepare before executing it again). It also frees the internal statement name, so if that name is used again to prepare a statement, the old statement in the DBMS is replaced by the new one(similar to embedded sql). You need to have the fix for bug 117108 for this. (I haven't tested this, but this is my understanding) Kristoff _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.