Re: [AOLSERVER] ns_db and bind variable support
On 2008.04.16, Tom Jackson [EMAIL PROTECTED] wrote: Connections are persistent, but sessions are not. [ns_db releasehandle]! In most databases, a session is defined as a connection's lifetime. When you release the nsdb handle back to the pool, it doesn't actually terminate the session and/or connection to the database, until idletimeout has elapsed at which point it closes the connection. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On Wed, Apr 16, 2008 at 09:26:09PM -0700, Tom Jackson wrote: If something actually gets complex, it is probably better done in the pl language, which can end up being several orders of magnitude faster. Or it can end up being enormously slower. It Depends. I've seen both happen with Oracle PL/SQL vs. SQL. (Measure first, then optimize...) Typically, one chooses whether to use PL/SQL or not out of design and implementation convenience for your code, not as a performance tuning measure. -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On Wed, Apr 16, 2008 at 08:19:23PM -0700, Jade Rubick wrote: Can someone explain why we need prepared statements? With Oracle (and nsoracle), basically you don't. I thought by using bind variables, we avoid the SQL parse time (at least with Oracle, that's my understanding) if you're using the same SQL but with different values in your bind variables. Correct, because the Oracle server caches the parsed SQL itself. The literal SQL query you send it becomes the key to the cache. Many Oracle docs and books tend to emphasize the use of bind variables for performance reasons. ArsDigita and OpenACS, on the other hand, tended to emphasize bind variables for correctness and safety reasons, perhaps because Oracle's SQL parsing is fast enough that they never noticed much need for any extra speed! ACS had no bind variable support at all until version 3.4 or so, which was probably c. 2000. (ACS 3.2 definitely did not have it.) In the years before that, lots of sites got built with no bind variables at all, and some of those were very large and busy. (I was there for the ArsDigita company-wide fire drill to add use of bind variables and other security-enhancing measures, which I believe was kicked off because Guan Yang quietly demonstrated that several prominent ACS sites were at the time readily crackable via SQL smuggling.) -- 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.
Re: [AOLSERVER] ns_db and bind variable support
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.
Re: [AOLSERVER] ns_db and bind variable support
On 2008.04.17, Andrew Piskorski [EMAIL PROTECTED] wrote: 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. I never suggested that prepared statements _replace_ the current nsdb, but add to it. You would still be able to use ns_db (select, exec, etc.) if you don't want and/or need prepared statements for a query. Of course, given the elimination of SQL injection protection, it would be better to use prepared statements regardless, but, if folks are concerned with having a choice, they will still have a choice. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On Thursday 17 April 2008 05:50, Dossy Shiobara wrote: 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. I never suggested that prepared statements _replace_ the current nsdb, but add to it. You would still be able to use ns_db (select, exec, etc.) if you don't want and/or need prepared statements for a query. Just note that there is no difference between: 1. requiring someone to change from [ns_db exec] to [ns_pg_exec] and 2. requiring someone to change from [ns_db exec] to [ns_db prepared...] This is true because the statement itself is different for each database, some use :var, some use positional ?, and some use $1. And prepared statements are multi-command replacements. Personally I use [ns_db exec] exclusively and use the return code to handle things like dml, select, 1row, 0or1row, etc., otherwise you are forced to use a catch for no other reason than to recover and return an useful error message. tom jackson -- 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] ns_db and bind variable support
On Thu, Apr 17, 2008 at 08:50:46AM +0930, Bas Scheffers wrote: Subject: Re: [AOLSERVER] aolserver and Pgtcl I don't really like bind variables, I would much rather see it implemented like: There are other people who prefer different styles of bind-variable-like support too, notably D. Richard Hipp. His SQLite Tcl API is probably a good source of such alternate ideas, for those interested in such things. ns_db select $db select * from people where country = $1 and age $2 [list au 25] The api could even fall back to emulating this when the database itself does not support it. This would be in a database independent way, instead of the current way with Postgres and Oracle implementing their own commands. Note, when a database actually supports real bind variables and provides an interface to use them in its C API, like Oracle does, you REALLY want to use it, NOT ignore it and roll your own emulation of bind variables in higher level code. This means that the various ns* database drivers MUST be involved in proper AOLserver bind variable support. Now, ns_db certainly can and should provide the various calls, which hook into the driver-specific code underneath, rather than just punting completely and letting the user call driver-provided Tcl commands directly like it does now. As a design principle, remember that ns_db itself never cares what is in the query at all! ns_db doesn't even care whether the query is SQL or something else, the query is just some opaque string which gets passed allong to the proper database driver. Therefore, the right default approach for ns_db bind variable support is to transparently pass through whatever bind variable syntax and usage each database and its driver natively supports and expects. If you want to experiment with building something extra to support a single style of alternative bind-variable-like syntax across multiple datbasese, that's fine, but it should never be the default behavior of ns_db. -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On Wednesday 16 April 2008 17:06, Andrew Piskorski wrote: As a design principle, remember that ns_db itself never cares what is in the query at all! ns_db doesn't even care whether the query is SQL or something else, the query is just some opaque string which gets passed allong to the proper database driver. Therefore, the right default approach for ns_db bind variable support is to transparently pass through whatever bind variable syntax and usage each database and its driver natively supports and expects. Well put. ns_db is the interface between the tcl layer and the driver. You can add stuff above and below ns_db. Actually, ns_db has a little too much API. ns_db exec can handle a lot of the specialized API, and allow tcl level error detection and recovery. If you want to experiment with building something extra to support a single style of alternative bind-variable-like syntax across multiple datbasese, that's fine, but it should never be the default behavior of ns_db. Right, ns_db is more generic that even SQL, so it is hard to do too much. tom jackson -- 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.
Re: [AOLSERVER] ns_db and bind variable support
+1 - Original Message From: Dossy Shiobara [EMAIL PROTECTED] To: AOLSERVER@LISTSERV.AOL.COM Sent: Wednesday, April 16, 2008 7:00:14 PM Subject: Re: [AOLSERVER] ns_db and bind variable support 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. 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. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On 2008.04.16, Jade Rubick [EMAIL PROTECTED] wrote: Can someone explain why we need prepared statements? I thought by using bind variables, we avoid the SQL parse time (at least with Oracle, that's my understanding) if you're using the same SQL but with different values in your bind variables. If the implementation of bind variables takes a list of lists, one per row to execute, then perhaps you're okay. However, I'm guessing this isn't the case. In the naive implementation scenario, a SQL statement is prepared then executed with bind variables to prevent SQL injection attacks, but does nothing for performance: each time the query is invoked, the SQL is parsed, and then the bind variables passed to the statement execution operation. In order to take advantage of the parse once into a prepared statement, you'd need to be able to store a handle to that prepared statement, and use and execute that once-prepared statement over and over again. This is why the common idiom seen is: statement = prepare(SQL) execute(statement, bind values) ... execute(statement, bind values) The statement is prepared once, but executed multiple times. If your implementation doesn't allow/require you to pass along a statement handle, the odds are good that it's re-parsing the statement every time, just to pass the bind variables in. You benefit from the elimination of SQL injection attacks (very important!) but not from the saving of reducing time spent parsing the SQL. Does this help explain things? -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On Apr 16, 2008, at 8:48 PM, Jeff Rogers wrote: 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. True of Oracle, not true, for instance, of Postgres. However, parsing of a SQL statement's extremely fast (how often are queries longer than a couple dozen lines in your typical web app?), what needs caching is the execution plan generated query optimizer. However, that can be a bit dicey unless your data's nicely distributed and you can have confidence that one plan will work about as well as any other the optimizer might generate, and that the very first query happens to generate such a plan. And that you gather analyzer stats and clear the query plan cache fairly often ... Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- 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.
Re: [AOLSERVER] ns_db and bind variable support
On 2008.04.16, Don Baccus [EMAIL PROTECTED] wrote: Did you try speeding up the plan generation using Oracle's method of giving hints? Yes, we /*+HINTED*/. Oracle trace log showed parse times of 160ms +/- 10ms. Plan generation wasn't the killer. Parsing the SQL was the killer. It wasn't complex, but was large (~2K, before variables). Okay, maybe 2K of SQL is complex by many people's standards, but by size alone, it wasn't the largest SQL we had in this project. And, yes, we pushed to have no single query execution take longer than 60ms. (This was for an e-commerce implementation.) There was something about the query that was just expensive to parse. Rather than spend time trying to work through Oracle's parser, we just punted and pushed it into a stored procedure. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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.
Re: [AOLSERVER] ns_db and bind variable support
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.
Re: [AOLSERVER] ns_db and bind variable support
On 2008.04.16, Tom Jackson [EMAIL PROTECTED] wrote: Actual prepared statements would be of little benefit in AOLserver since the lifetime of an SQL session is so short, [...] Huh? AOLserver offers pooled, persistent database connections. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on. (p. 70) -- 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.