Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Questions on an Article about Prepared Statements and Connection Pooling

Elo people of EE,

<warning:  asker is a n00b>

I have a question regarding the use of prepared statements in J2EE.  I came upon an article regarding the use of prepared statements:

http://www.theserverside.com/resources/article.jsp?l=Prepared-Statments

Probably some of you must have been familiar with it.  Anyway, it says there that the efficient way to use prepared statements is to place it in this form:

PreparedStatement ps = conn.prepareStatement("select a,b from t where c = ?");
For(int I = 0; I < 1000; ++I)
{
        ps.setInt(1, I);
        ResultSet rs = ps.executeQuery();
        Rs.close();
}
ps.close();

and not like:

For(int I = 0; I < 1000; ++I)
{
        PreparedStatement ps = conn.prepareStatement("select a,b from t where c = " + I);
        ResultSet rs = Ps.executeQuery();
        Rs.close();
        Ps.close();
}

because each call to conn.prepareStatement() is the same as recompiling the same SQL all over again like its a different SQL.  <correct me on this as this'll be a key part of my question>

Now that's fine and dandy, until i continued reading the rest of the page that somewhat gives a different air.  I came upon a line that says:

"...after the connection is returned to the pool and later reused by the same, or another application, , then ideally, we want the same PreparedStatement to be returned to the application. ..."

To my understanding, what was wanted here was that after preparing the statement in a particular connection and returning it to the connection pool, then when it is reused by another app, say another jsp, the previously prepared statement that was made on that connection can be used again.  

After that came a section about the J2EE prepared statement cache which basically says, that each connection object in the connection pool contains one of these caches.  and that a cache "keeps a list of  prepared statements".  Perhaps, as another assumption, this list was the result of a previous usage of the said connection.

Finally, at the ending, it says:
"If we want to take advantage of this cache, the same rules apply as before. We need to use parameterized queries so that they will match ones already prepared in the cache. "

I am assuming that the rules the author was referring to was the pseudocode i posted above.

Now my questions are:
1.  If i'm using a J2EE server, would repeated calls to conn.prepareStatement() on the same sql be still inefficient (as shown in the second pseudocode) ?  Because it states recently that if a parameterized query matches that of what's in the cache then in effect i would still be using a cached ps instead of making a new access plan for the same sql like a database cache.

2.  Am i correct in saying that a prepared statement in the cache of a connection still carried over and can be possibly used by another app?  Would it be like, a connection object at one time, will become 'complete' with a list of prepared statements in its cache?

32.  What would be the best pattern for use in prepared statements for use in client/servers?  <links would be nice :)>


Follow up questions:
1.  Is IBM Websphere 3.5.3 be considered a J2EE server that uses the mentioned J2EE connection scheme?
0
Anche
Asked:
Anche
1 Solution
 
randydCommented:
1. yes.  you should minimize the calls to any function to reduce overhead if at all possible.  in your example, you would be forcing a bunch of lookup in the cache and some stack to pass the results back...  for no benefit.

2. there are memory limitations to how many are kept around, i think its based on how many times the particular query has been accessed (stays around longer if used more) and the number that will fit into the cache memory. (not sure where this is parameterized)

3. your example works well.  it's up to you to decide whether that loop should be in a GUI client or in some servlet or something... depends on your overall architecture.

hope this helps
randy
0
 
AncheAuthor Commented:
well, this'll suffice i guess.  thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now