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
