I haven't seen any discussion here which approximates how current SQL standards handle prepared statements. Actual prepared statements would be of little benefit in AOLserver since the lifetime of an SQL session is so short, there is very little opportunity for reuse. Prepared statements are usually stored in the database or in the driver and are associated with a particular session (unlike compiled procedures like plsql or plpgsql).
If prepared statements were supported the benefit would be that a statement would be compiled once, another benefit is that only data would be passed through the driver instead of the entire query (for more than one query). So the benefit is for persistent applications, like an accounting application which remains connected for hours, or a whole day. But what is the first thing you need with a prepared statement? A name, and some kind of global scope, essentially reuse. If you define your queries on a web page, you lose this. One thing Jeff says is also important. Query plans are data dependent. New data, new plan. Also, if you are returning large amounts of data, the amount of data sent to the database is unimportant. If something actually gets complex, it is probably better done in the pl language, which can end up being several orders of magnitude faster. On Wednesday 16 April 2008 20:48, Jeff Rogers wrote: > 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. -- 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.
