pooled connections and prepared statements

I am building an application which will use pooled jdbc connections (either our own pooling or vendor pooling). A question arises about prepared statements in this regard - especially with Informix. One wants to use prepared statements to avoid the overhead of repeatedly running the query parser and optimizer on frequently used statements. However, the last time I used informix, prepared statements were associated only with connections, not kept in a global cache. Oracle, as I understand it, uses a global cache. The implication is that I need to keep track of the prepared statements *by connection* - keep a hash table or something for each connection, which seems like a nuisance. My questions: Is this true with Informix? Oracle? If one keeps prepared connections around, what sort of client/server resources are tied up by this? I have seen postings that one should not keep statements around (someone implied that they tie up cursors). Is this true? Thanks in advance John Sent via Deja.com http://www.deja.com/ Before you buy.

stormchaser wrote: > I am building an application which will use pooled jdbc connections > (either our own pooling or vendor pooling). A question arises about > prepared statements in this regard - especially with Informix. One > wants to use prepared statements to avoid the overhead of repeatedly > running the query parser and optimizer on frequently used statements. > However, the last time I used informix, prepared statements were > associated only with connections, not kept in a global cache. Oracle, > as I understand it, uses a global cache. > > The implication is that I need to keep track of the prepared statements > *by connection* - keep a hash table or something for each connection, > which seems like a nuisance. > > My questions: > > Is this true with Informix? Oracle? > If one keeps prepared connections around, what sort of client/server > resources are tied up by this? I have seen postings that one should not > keep statements around (someone implied that they tie up cursors). Is > this true? Versions 9.2x and above do provide a server side statement cache (similar to Oracle's global pool) that should significantly reduce shared memory consumption when different sessions have the same statement prepared. Hope this helps, Heiko > > Thanks in advance > > John > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <39B6750D.CA52812D@informix.com>, Heiko Giesselmann <heiko.giesselmann@informix.com> wrote: > stormchaser wrote: > > > I am building an application which will use pooled jdbc connections > > (either our own pooling or vendor pooling). A question arises about > > prepared statements in this regard - especially with Informix. One > > wants to use prepared statements to avoid the overhead of repeatedly > > running the query parser and optimizer on frequently used statements. > > However, the last time I used informix, prepared statements were > > associated only with connections, not kept in a global cache. Oracle, > > as I understand it, uses a global cache. > > > > The implication is that I need to keep track of the prepared statements > > *by connection* - keep a hash table or something for each connection, > > which seems like a nuisance. > > > > My questions: > > > > Is this true with Informix? Oracle? > > If one keeps prepared connections around, what sort of client/server > > resources are tied up by this? I have seen postings that one should not > > keep statements around (someone implied that they tie up cursors). Is > > this true? > > Versions 9.2x and above do provide a server side statement cache (similar to > Oracle's global pool) that should significantly reduce shared memory consumption > when different sessions have the same statement prepared. > > Hope this helps, Heiko Thank you. Does this mean it makes sense for me to re-prepare each statement whenever I am going to use it rather than try to cache it? That would certainly be the easiest. John Sent via Deja.com http://www.deja.com/ Before you buy.

Informix's IDS2000 does support Statement Caching (see the ONCONFIG parameters STMT_CACHE & STMT_CACHE_SIZE). The default is "no caching). However, preparing and reusing statements (which is on a by-connection basis) does provide better results, depending on how often prepared statements are reused. In tests I carried out in the context of our application, this is how they stacked up Arbitrary CPU units used by Informix to accomplish 1000 iterations of a set of SQL statements after establishing a connection Vanilla (caching off, no preparing) : 100 Caching ON, no preparing : 70 Preparing ON, no caching : 30 In other words, preparing/reusing made the application more than 3 times as fast as Vanilla and more than twice as fast as Engine caching. Caution : Results will vary [ :) Am I sounding like a salesman?] In our 3 tier context wherein our Application Server establishes a "handful" of "permanent" connections to support the "hundreds or thousands" of end-users, preparing/reusing gives us significant benefits. Rudy stormchaser wrote: > I am building an application which will use pooled jdbc connections > (either our own pooling or vendor pooling). A question arises about > prepared statements in this regard - especially with Informix. One > wants to use prepared statements to avoid the overhead of repeatedly > running the query parser and optimizer on frequently used statements. > However, the last time I used informix, prepared statements were > associated only with connections, not kept in a global cache. Oracle, > as I understand it, uses a global cache. > > The implication is that I need to keep track of the prepared statements > *by connection* - keep a hash table or something for each connection, > which seems like a nuisance. > > My questions: > > Is this true with Informix? Oracle? > If one keeps prepared connections around, what sort of client/server > resources are tied up by this? I have seen postings that one should not > keep statements around (someone implied that they tie up cursors). Is > this true? > > Thanks in advance > > John > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Hi. Yes, you would have to retain and control cached preparedStatements by connection, because the DBMS will associate any transactional semantics to the connection, for all work done through any of it's statements. If a client used statements from two connections he might deadlock himself. Furthermore, you will need to store and access prepared statements by the SQL that formed them, and lastly, if your client may involve several classes, in an execution, you need to make sure that no two methods using the same connection get the same statement. If method A gets results from using statement X, and calls method B which also uses statement X, a reuse of a statement will automatically cancel any previous results from the statement, and if method A tries to continuew with a resultset gotten from the statement, it will find that the resultset has been closed. Joe Weinstein at BEA stormchaser wrote: > > I am building an application which will use pooled jdbc connections > (either our own pooling or vendor pooling). A question arises about > prepared statements in this regard - especially with Informix. One > wants to use prepared statements to avoid the overhead of repeatedly > running the query parser and optimizer on frequently used statements. > However, the last time I used informix, prepared statements were > associated only with connections, not kept in a global cache. Oracle, > as I understand it, uses a global cache. > > The implication is that I need to keep track of the prepared statements > *by connection* - keep a hash table or something for each connection, > which seems like a nuisance. > > My questions: > > Is this true with Informix? Oracle? > If one keeps prepared connections around, what sort of client/server > resources are tied up by this? I have seen postings that one should not > keep statements around (someone implied that they tie up cursors). Is > this true? > > Thanks in advance > > John > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for people who want to work with Java and E-Commerce infrastructure products. Send resumes to joe@beasys.com -------------------------------------------------------------------------------- The Weblogic Application Server from BEA JavaWorld Editor's Choice Award: Best Web Application Server Java Developer's Journal Editor's Choice Award: Best Web Application Server Crossroads A-List Award: Rapid Application Development Tools for Java Intelligent Enterprise RealWare: Best Application Using a Component Architecture http://www.bea.com/press/awards_weblogic.html

In article <39B6A69C.AA2FF6BB@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > Informix's IDS2000 does support Statement Caching (see the ONCONFIG > parameters STMT_CACHE & STMT_CACHE_SIZE). The default is "no caching). > However, preparing and reusing statements (which is on a by-connection > basis) does provide better results, depending on how often prepared > statements are reused. In tests I carried out in the context of our > application, this is how they stacked up > > Arbitrary CPU units used by Informix to accomplish 1000 iterations of a > set of SQL statements after establishing a connection > > Vanilla (caching off, no preparing) : 100 > Caching ON, no preparing : 70 > Preparing ON, no caching : 30 > > In other words, preparing/reusing made the application more than 3 times > as fast as Vanilla and more than twice as fast as Engine caching. Caution > : Results will vary [ :) Am I sounding like a salesman?] > > In our 3 tier context wherein our Application Server establishes a > "handful" of "permanent" connections to support the "hundreds or > thousands" of end-users, preparing/reusing gives us significant benefits. > > Rudy Rudy, Thanks for your informative reply. I am disappointed that user statement re-use is required, since the database should be smart enough to do that for me. Do you know if preparing *each time* might result in better cache performance? In other words, does preparing the statement clue the engine that the statement should be cached? That is one thing you didn't report statistics on. Again, Thanks. We are trying to do the same thing (many many users over a few connections). We have done this in the past with Informix, where we used C, with one connection per process, but in that case one doesn't have to keep track of which statement is prepared with which connection. However, using a threaded Java environment and connection pooling doesn't give us that option. I assume that you are keeping a copy of each prepared statement with each connection? John Sent via Deja.com http://www.deja.com/ Before you buy.

In article <39B6A69C.AA2FF6BB@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > Informix's IDS2000 does support Statement Caching (see the ONCONFIG > parameters STMT_CACHE & STMT_CACHE_SIZE). The default is "no caching). > However, preparing and reusing statements (which is on a by-connection > basis) does provide better results, depending on how often prepared > statements are reused. In tests I carried out in the context of our > application, this is how they stacked up > > Arbitrary CPU units used by Informix to accomplish 1000 iterations of a > set of SQL statements after establishing a connection > > Vanilla (caching off, no preparing) : 100 > Caching ON, no preparing : 70 > Preparing ON, no caching : 30 > > In other words, preparing/reusing made the application more than 3 times > as fast as Vanilla and more than twice as fast as Engine caching. Caution > : Results will vary [ :) Am I sounding like a salesman?] > > In our 3 tier context wherein our Application Server establishes a > "handful" of "permanent" connections to support the "hundreds or > thousands" of end-users, preparing/reusing gives us significant benefits. > > Rudy Rudy, Thanks for your informative reply. I am disappointed that user statement re-use is required, since the database should be smart enough to do that for me. Do you know if preparing *each time* might result in better cache performance? In other words, does preparing the statement clue the engine that the statement should be cached? That is one thing you didn't report statistics on. Again, Thanks. We are trying to do the same thing (many many users over a few connections). We have done this in the past with Informix, where we used C, with one connection per process, but in that case one doesn't have to keep track of which statement is prepared with which connection. However, using a threaded Java environment and connection pooling doesn't give us that option. I assume that you are keeping a copy of each prepared statement with each connection? John Sent via Deja.com http://www.deja.com/ Before you buy.

In article <39B6A69C.AA2FF6BB@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > Informix's IDS2000 does support Statement Caching (see the ONCONFIG > parameters STMT_CACHE & STMT_CACHE_SIZE). The default is "no caching). > However, preparing and reusing statements (which is on a by-connection > basis) does provide better results, depending on how often prepared > statements are reused. In tests I carried out in the context of our > application, this is how they stacked up > > Arbitrary CPU units used by Informix to accomplish 1000 iterations of a > set of SQL statements after establishing a connection > > Vanilla (caching off, no preparing) : 100 > Caching ON, no preparing : 70 > Preparing ON, no caching : 30 > > In other words, preparing/reusing made the application more than 3 times > as fast as Vanilla and more than twice as fast as Engine caching. Caution > : Results will vary [ :) Am I sounding like a salesman?] > > In our 3 tier context wherein our Application Server establishes a > "handful" of "permanent" connections to support the "hundreds or > thousands" of end-users, preparing/reusing gives us significant benefits. > > Rudy Rudy, Thanks for your informative reply. I am disappointed that user statement re-use is required, since the database should be smart enough to do that for me. Do you know if preparing *each time* might result in better cache performance? In other words, does preparing the statement clue the engine that the statement should be cached? That is one thing you didn't report statistics on. Again, Thanks. We are trying to do the same thing (many many users over a few connections). We have done this in the past with Informix, where we used C, with one connection per process, but in that case one doesn't have to keep track of which statement is prepared with which connection. However, using a threaded Java environment and connection pooling doesn't give us that option. I assume that you are keeping a copy of each prepared statement with each connection? John Sent via Deja.com http://www.deja.com/ Before you buy.

stormchaser wrote: > Thanks for your informative reply. I am disappointed that user > statement re-use is required, since the database should be smart enough > to do that for me. Do you know if preparing *each time* might result in > better cache performance? In other words, does preparing the statement > clue the engine that the statement should be cached? That is one thing > you didn't report statistics on. I don't think preparing and caching interact in the way you are hoping. Execution of a prepared statement doesn't benefit from caching because it is already pre-compiled. Repreparation of a prepared statement does benefit from caching, but just like any other vanilla SQL that is being reused. If caching is turned on for the session, all SQL (barring some exceptions) is cached, whether prepared or not. > Again, Thanks. We are trying to do the same thing (many many users over > a few connections). We have done this in the past with Informix, where > we used C, with one connection per process, but in that case one > doesn't have to keep track of which statement is prepared with which > connection. However, using a threaded Java environment and connection > pooling doesn't give us that option. > > I assume that you are keeping a copy of each prepared statement with > each connection? That's right, we do keep a copy of prepared statements with each connection. Since we have pre-defined the SQL that we want prepared-reused (only what's deemed as high-use SQL is selected), each connection (very quickly, in our case) develops its own copy of all such prepared statements. As far as I can see, the mechanics should be the same in your Java environment - each connection has its own copy of ALL prepared statements while threads order execution of a statement not caring which connection gets to do the job. Of course, since my knowledge of the Java threaded environment & connection pooling amounts to little more than knowing the spelling of the words involved, be wary. All the best. Rudy

We use an intermediate server to multiplex a small number of Informix connections over a large number of users and to create a concept of a 'global cursor' as user followup requests (ie next 10 rows) can come from a different client app copy (we use application servers also) and can be handled by a different connection in the 'query server'. In our application most of the queries are pre-defined and the text stored in a set of database tables along with information about the how to deblock an incoming data block containing values for replaceable parameters and other information. At startup each connection has prepared all of the statements stored in the database that it is responsible for (we run two copies of the this server each responsible for a subset of the stored and dynamic queries based on the stored query's key column value). When a request comes in to execute one of the stored queries the corresponding cursor is simply OPENED and FETCHED. This is very efficient and seems to not cost much in resources either in the 'query server' or in the IDS engine. Dynamic queries (ie where the SQL text is passed to the server from the client instead of a stored query key) are prepared, 'cursored', OPENED, and fetched on the fly. Obviously the stored queries are far more efficient and use less resources in the server so dynamic queries are discouraged and reserved, at least in theory ;0), for situations where a query either cannot be pre-determined or there are so many possible versions of a user request that canning all of them would be silly. In the latter case we can the 90% queries and allow the client apps to pass the remainder dynamically. Hope this helps some. Art S. Kagel stormchaser wrote: > > In article <39B6A69C.AA2FF6BB@americasm01.nt.com>, > Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > > Informix's IDS2000 does support Statement Caching (see the ONCONFIG > > parameters STMT_CACHE & STMT_CACHE_SIZE). The default is "no > caching). > > However, preparing and reusing statements (which is on a by-connection > > basis) does provide better results, depending on how often prepared > > statements are reused. In tests I carried out in the context of our > > application, this is how they stacked up > > > > Arbitrary CPU units used by Informix to accomplish 1000 iterations of > a > > set of SQL statements after establishing a connection > > > > Vanilla (caching off, no preparing) : 100 > > Caching ON, no preparing : 70 > > Preparing ON, no caching : 30 > > > > In other words, preparing/reusing made the application more than 3 > times > > as fast as Vanilla and more than twice as fast as Engine caching. > Caution > > : Results will vary [ :) Am I sounding like a salesman?] > > > > In our 3 tier context wherein our Application Server establishes a > > "handful" of "permanent" connections to support the "hundreds or > > thousands" of end-users, preparing/reusing gives us significant > benefits. > > > > Rudy > > Rudy, > > Thanks for your informative reply. I am disappointed that user > statement re-use is required, since the database should be smart enough > to do that for me. Do you know if preparing *each time* might result in > better cache performance? In other words, does preparing the statement > clue the engine that the statement should be cached? That is one thing > you didn't report statistics on. > > Again, Thanks. We are trying to do the same thing (many many users over > a few connections). We have done this in the past with Informix, where > we used C, with one connection per process, but in that case one > doesn't have to keep track of which statement is prepared with which > connection. However, using a threaded Java environment and connection > pooling doesn't give us that option. > > I assume that you are keeping a copy of each prepared statement with > each connection? > > John > > Sent via Deja.com http://www.deja.com/ > Before you buy.