On 17/04/2008, at 11:30 AM, Dossy Shiobara wrote:
In web applications, one of the big performance hits is SQL query
parse
time.
But the problem is creating a cache that knows which connections from
the pool already have the statement cached and the ID that goes with
it. This why few, including myself, use it - the only logical way is
to handle this at the driver level, not the application code level.
(although in the API there will be ways to control the caching
behavior, obviously)
set stmt [ns_db prepare ?-pool $pool | -handle $handle? $sql]
I would say this needs to be at the handle level. It needs to be
compiled per connection anyway and if you run your web application
long enough, each handle will eventually have every statement cached.
You could add a -name flag where the developer could name the query
themselves.
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.
My though on this is that you need to prepare the statement against a
handle as a prepared statement is valid per connection anyway. If the
developer passes the -cache flag, it will be cached by name/hash, in
an ns_set in connection pool entry. This can be done by either a
provided name or a hash.
If you pass the cache flag, it will look up if for that connection a
prepared version already exist and then either create or re-use it. It
returns the statement name/id for use in "ns_db exec|select|dml -
statement".
Of you omnit the cache flag, a new one will be created with a unique
name and can be re-used until the connection is passed back to the
pool, at which time it will be deallocated.
One thing to keep in mind is that the query will be planned when
prepared. But as a table grows, the strategy might change and a re-
compile is needed. A limited TTL on any connection, configurable in
the pool config, should do the trick.
If we solve the caching problem and people re-code their apps, I would
not be surprised if there will be a good performance improvement.
Heck, it could be the biggest performance boost in AOLserver in years!
Bas.
--
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.
