Re: [AOLSERVER] ns_db and bind variable support

2008-04-17 Thread Dossy Shiobara
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

2008-04-17 Thread Andrew Piskorski
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

2008-04-17 Thread Andrew Piskorski
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

2008-04-17 Thread Andrew Piskorski
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

2008-04-17 Thread Dossy Shiobara
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

2008-04-17 Thread Tom Jackson
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

2008-04-16 Thread Andrew Piskorski
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

2008-04-16 Thread Tom Jackson
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

2008-04-16 Thread Brett Schwarz
+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

2008-04-16 Thread Dossy Shiobara
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

2008-04-16 Thread Don Baccus

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

2008-04-16 Thread Dossy Shiobara
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

2008-04-16 Thread Tom Jackson
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

2008-04-16 Thread Dossy Shiobara
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.