Thanks Mark. I think think what you're saying backs up some of my arguments.
As before, after I'm done with the RC4 jdbc javadocs and the new junit test case stub framework, I will write some minor modifications to the CORE and a JDBC test harness to quickly check what might be expected for local connections using direct batchExecute and PreparedStatement (as opposed to going through the Parser and using CALL syntax). I suspect that the performance gains here can actually be quite substantial (maybe even up to an optimistic 50% for short running statments) and would not have the full complexity or potential to eat scads of Hashtable key memory the way simple cross-session statement caching can, a la Oracle shared sql cache and the similar methods used in my simple statement caching experiments, as posted at: Performance Gains via Caching Parsed Statments http://sourceforge.net/forum/forum.php?thread_id=633995&forum_id=73673 Also, going the PreparedStatement route has the advantantage, as you point out, of always selecting the correct precompiled statement, whereas simple hash lookup can result in a miss if nothing more than whitespace (as well as case that makes no difference to the semantics) is different. Of course, one could write a special Hashtable implementation using a case and whitespace-insensitive comparator, but I suspect that it would add more overhead than any potential savings it offered, since it would have to take into account sql comments and quoted identifiers, essentially forcing it to be a (maybe not that) scaled-down version of the Parser ( or at the very least, the Tokenizer, which I suspect accounts for a fair amount of the time spent in parsing). ----- Original Message ----- From: "Mark D. Anderson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 09, 2002 12:50 PM Subject: Re: [Hsqldb-developers] On The Subject Of Prepared And Callable Statements: > The reason that in many databases a client prepare() call does not talk to the > server, is that the server implements a parse cache which largely obviates it. > In oracle they call this the "shared sql cache". > > This makes sense actually -- since it is primarily server work that is being saved, that > is where a cache would be most effective, since it allows for caching across > multiple clients. > > Generally speaking, an unprepared query of > select * from people where ssn = ? /* bind "999-01-1234" */ > is just as fast as one that is prepared. > You just have to be careful not to bust the cache by doing a > select * from people where ssn = "999-01-1234" > because generally it is a simple hash look up on the server, that is affected even by > white space changes in the sql string. > > So if you are careful to use bound parameters instead of interpolated string > values, doing a prepare typically offers little in terms of improved server overhead. > There may be some minimal client-side improvement by doing a prepare just once, > and there may be improvements in application source code organization by > doing them separately. > > Note that in most client database drivers, a prepared statement is tied to a particular > database connection. This interacts poorly with connection pooling and with > reconnects (say, due to inactivity timeout, a common issue with web server farms). > This is addressed somewhat in JDBC3 with statement pooling, but it is difficult > to leverage that intelligently without knowing what the actual underlying implementation > is, and it is not easy to make it work with your own arbitrary pooling implementation. > > -mda > > > > _______________________________________________________________ > > Sponsored by: > Looking for hip toys and fun scwag. There is no better place > then the good friends at ThinkGeek. http://www.ThinkGeek.com/ > _______________________________________________ > hsqldb-developers mailing list > [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/hsqldb-developers > _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
