On Wed, Apr 16, 2008 at 10:00:14PM -0400, Dossy Shiobara wrote: > On 2008.04.17, Bas Scheffers <[EMAIL PROTECTED]> wrote: > > That brings me to another subject: do we want prepared statements? > > Yes!
No. Or perhaps it's more accurate to say, no, hardly ever, but perhaps on very rare occasions. Oracle caches the parsed SQL server-side, which is basically equivalent to automatic prepared statements. For databases that don't do that server-side, you can have your database API or driver effectively do the same thing for you. That's what SQLite's Tcl API does, and there was some good discussion of how any why on the SQLite list in the past. In other words, the right API approach for high level programming languages (like Tcl) is usually AUTOMATIC prepared statements, where the human programmer using the API gets the performance boost for free without any additional work. Perhaps in certain rare edge cases you'd want to drop down to some lower level API and control query parsing in detail, but making people do that sort of thing for every single statement just seems silly. Note, OraTcl, which works very well (I particularly like it's new -arraydml switch for doing fast inserts), mostly shares the same style of lower-level parse/bind/execute/fetch API. It is nice that ns_db avoids that and thus feels a little higher level. And of course, the OpenACS db_* API builds upon ns_db to make something much nicer, easier to use, and more powerful than that. I don't recall ever even HEARING of anyone having to drop down beneath the OpenACS DB API for performance tweaking of some particular query, or anything like that. This strongly suggests that direct low-level control over SQL parsing and the like is enormously overrated, and that 99+% of the time, a high-level Tcl API, as with OpenACS or SQLite, is what you want to use. > 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 I doubt it. Oracle is typically blazingly fast at SQL parsing, and I bet many other databases are as well. More importantly, some of the people running very large, very serious OpenACS sites (largely on PostgreSQL like WU Wien) have done some careful performance investigation and tuning work, and I don't recall them ever even MENTIONING SQL parsing as a significant overhead on their sites. >From my own experience tuning queries, and from informal reports I've heard from users running much busier sites, I'd say that most people would be doing VERY well to get things so speedy that SQL parse overhead was an important rate limiter, or in many cases, even noticeable. > 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: That sounds very much like what SQLite's Tcl API does. I recall at least one person here mentioning that he never reads any OpenACS code, because it is GPL and he is working on a commercial codebase. For folks like that, SQLite may be a useful alternative source of some examples, as its code is in the public domain. > 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. Yuck. IMO, the application developer MIGHT on occasion want that level of control available, but forcing him to use it for every single database access is sub-standard API design. (C gives me lots of "control" too, but 98% of the time I avoid using it in favor of higher level programming languages. It's not 1978 anymore...) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ -- 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.
