Dossy Shiobara wrote:
On 2008.04.17, Bas Scheffers <[EMAIL PROTECTED]> wrote:
That brings me to another subject: do we want prepared statements?
Yes!
In web applications, one of the big performance hits is SQL query parse
time. The irony is, in web applications, the queries aren't really
dynamic: most can be parsed once, and different bind variable values
used at execution time.
Do you have test code and results to back this up?
I've been told this by every oracle dba I've worked with and the
performance gains I've seen by moving to prepared statements is
generally quite small unless the sql is horrendously complex, and even
then the backends appear to do caching of query plans anyway and so
essentially use prepared queries implicitly even when the application
code doesn't. sqlite even throws particular errors when something
happens to invalidate its cached query plans.
Not that I doubt query parse time is a hit, but without numbers I won't
buy that it is a big performance hit. I submit as anecdotal evidence
the large base of db-backed aolserver apps running just fine despite
using entirely ad-hoc queries. And I think postgresql didn't even have
client-side prepared statements before v3 of the protocol which is late
7.x or maybe 8.0.
None of this should be interpreted in any way as objecting to the
inclusion of prepared statements.
In my local sandbox, where I've been hacking on bind variable support, I
also implemented an [ns_db prepare] which returns an opaque ID to an
entry in a prepared statement cache. The concept looks like this:
set stmt [ns_db prepare ?-pool $pool | -handle $handle? $sql]
set values [list a 1 b 2 ... z 26]
ns_db exec -statement $stmt $values
Or, something very much like that. One thought, to avoid having
application code store and save and retrieve the statement handle
between requests was to hash the SQL statement and make the opaque ID
the hash. The unlikely hash collision issue aside, this could fail
where a prepared statement can't be cached (and there's no way for nsdb
to "know" this)--so, caching/reuse of prepared statements really should
be left up to the application code, as the developer ought to know when
it can be reused vs. when it should be flushed/re-prepared.
One possible although complicated way around this might be to implement
a new tcl type for cached statements, using the sql as the string rep
and the internal handle as the "other" type. Then ns_db could shimmer a
sql string to a prepared statement as necessary. This might cause
issues with sharing tho, since I nsv only stores the string
representation to avoid thread-local data problems. OTOH, if the cached
statement has thread local data (unlikely but possible) this could be
just fine.
-J
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
