[AOLSERVER] aolserver and Pgtcl
Xavier Bourguignon
I know aolserver comes with postgres support, but I would like to use
the Pgtcl library for a project.
Has anybody managed to use Pgtcl and keep a persistent connection to
the database that can be re-used by the requests coming in, or do you
have to keep opening a connection to the db, query the db then close
the connection for every single request?
Thank you
--
Xavier Bourguignon
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <list...@listserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Brett Schwarz
>
> I know aolserver comes with postgres support, but I would like to use
> the Pgtcl library for a project.
> Has anybody managed to use Pgtcl and keep a persistent connection to
> the database that can be re-used by the requests coming in, or do you
> have to keep opening a connection to the db, query the db then close
> the connection for every single request?
>
Just curious, why use pgtcl instead of the underlining db api in aolserver?
Note that I am one of the maintainers of pgtcl (at least one of theversions), and I don't use it within aolserver. I don't think it hasbeen proven that pgtcl is thread safe...so I don't know what kind oftrouble you will get in (although it's on my list to make it so).
Given that, I don't think you want to share connection/resulthandles across connections, so you would want to connect/disconnectevery time, just to be safe...although you could give it a try to seewhat happens...
I know people who use pgtcl from Apache + mod_tcl, but Apache is a different environment.
HTH,
--brett
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Tom Jackson
use it, I have to put in a trace filter to close/return the handle.
I can't imagine that it is a good idea to just randomly reuse an open handle.
You risk running out of handles for one, but the state of the connection
would be more or less random.
BTW, this is one of the advantages of ns_db: a persistent shared pool of db
connections, but they need to be cleaned up between requests (by ns_db
releasehandle).
tom jackson
On Wednesday 16 April 2008 11:48, Brett Schwarz wrote:
> > Hi,
> >
> > I know aolserver comes with postgres support, but I would like to use
> > the Pgtcl library for a project.
> > Has anybody managed to use Pgtcl and keep a persistent connection to
> > the database that can be re-used by the requests coming in, or do you
> > have to keep opening a connection to the db, query the db then close
> > the connection for every single request?
>
> Just curious, why use pgtcl instead of the underlining db api in aolserver?
>
> Note that I am one of the maintainers of pgtcl (at least one of
> theversions), and I don't use it within aolserver. I don't think it hasbeen
> proven that pgtcl is thread safe...so I don't know what kind oftrouble you
> will get in (although it's on my list to make it so).
>
> Given that, I don't think you want to share connection/resulthandles across
> connections, so you would want to connect/disconnectevery time, just to be
> safe...although you could give it a try to seewhat happens...
>
> I know people who use pgtcl from Apache + mod_tcl, but Apache is a
> different environment.
>
> HTH,
> --brett
>
>
>
>
> ___________________________________________________________________________
>_________ Be a better friend, newshound, and
Xavier Bourguignon
Thanks for the message.
I just wanted to try it with pgtcl.
My problem with the underlying db api in aolserver is that there is no
mechanism for binding variables to an SQL statement, e.g:
set sql {
select
*
from
table
where
status = ?
}
There is no mean to bind a variable to the placeholder. May be the db
api is not matured yet, but this would certainly be a worth while
addition.
--
Xavier Bourguignon
Tom Jackson
maybe it is part of the db_* API of OpenACS.
I also have a wrapper API for ns_db which has more of a stored procedure type
API.
Here is a link to an example application:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=tree;f=packages/dbo
Most of the API are used on this tcl page:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=blob;f=packages/dbo/www/select-relations.tcl
The queries are defined in individual files:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=tree;f=packages/dbo/queries
In general, bind variables is something which is handled by the driver,
because there is no standard on how to do it. Some use ?, some use :varname.
tom jackson
On Wednesday 16 April 2008 13:04, Xavier Bourguignon wrote:
> Hi Brett,
>
> Thanks for the message.
>
> I just wanted to try it with pgtcl.
> My problem with the underlying db api in aolserver is that there is no
> mechanism for binding variables to an SQL statement, e.g:
>
> set sql {
> select
> *
> from
> table
> where
> status = ?
> }
>
> There is no mean to bind a variable to the placeholder. May be the db
> api is not matured yet, but this would certainly be a worth while
> addition.
Don Baccus
> You should look at the pg driver, I think it can emulate bind
> variables, or
> maybe it is part of the db_* API of OpenACS.
You have to compile nspostgres.c for OpenACS use to make the command
available.
Then use ns_pg_bind rather than ns_db. :foo will reference the tcl
var foo in the caller.
----
Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org
Dossy Shiobara
> In general, bind variables is something which is handled by the
> driver, because there is no standard on how to do it. Some use ?, some
> use :varname.
Can someone please help by doing the necessary research and put together
a matrix of supported databases, and list what version of each supports
bind variables _at all_ (older MySQL didn't), and which ones support
named (":varname") vs. unnamed ("?") bind variables?
I have wanted to add bind variable support to nsdb for a _long_ time,
but never got around to computing this support matrix that I describe
above.
Would anyone care to take up the task?
--
Dossy Shiobara | do...@panoptic.com | 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)
Tom Jackson
> On Apr 16, 2008, at 2:33 PM, Tom Jackson wrote:
> > You should look at the pg driver, I think it can emulate bind
> > variables, or
> > maybe it is part of the db_* API of OpenACS.
>
> You have to compile nspostgres.c for OpenACS use to make the command
> available.
>
> Then use ns_pg_bind rather than ns_db. :foo will reference the tcl
> var foo in the caller.
So Xavier, your query would/could look something like this:
set status 123
set sql {
select
*
from
table
where
status = :status
}
ns_pg_bind execute $db $sql
(There are other options, check out the -bind switch, which takes an ns_set
id.)
tom jackson
Don Baccus
>
>
> I have wanted to add bind variable support to nsdb for a _long_ time,
> but never got around to computing this support matrix that I describe
> above.
If you do, it needs to be a config option, because with Oracle you do
*not* want to do this in ns_db.
Bas Scheffers
> I have wanted to add bind variable support to nsdb for a _long_ time,
> but never got around to computing this support matrix that I describe
> above.
implemented like:
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.
Bas.
Don Baccus
> On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote:
>> I have wanted to add bind variable support to nsdb for a _long_ time,
>> but never got around to computing this support matrix that I describe
>> above.
> I don't really like bind variables, I would much rather see it
> implemented like:
>
> ns_db select $db "select * from people where country = $1 and age >
> $2" [list "au" 25]
Geez, names are more readable than numbers in any sizable query.
nspostgres supports the optional passing of an ns_set to define the
bind vars, and the openacs db api will convert an array get list to
the ns_set for you.
And the default bind var syntax is just damned convenient, why force
more work than is necessary on the part of the person writing queries?
And before answering "well, they can always use the variable directly"
remember that both emulated and real bind vars give protect against
sql smuggling.
Tom Jackson
> I don't really like bind variables, I would much rather see it
> implemented like:
> ns_db select $db "select * from people where country = $1 and age >
> $2" [list "au" 25]
You can look at the examples in my recent post. It works with plain 'ol
AOLserver, doesn't use bind vars, but is safe from SQL smuggling attacks
(which is most of the reason for bind vars).
Your script/page level code can remain unchanged even when you switch
databases.
I look at ns_db as a starting point, but it is too vulnerable to the database
you are using. The stored procedure model is more robust and easy to emulate,
as my example code demonstrates.
Also, if anyone is interested on working on a database driver, please choose
and odbc driver. The unixodbc library code is well maintained and the newer
SQL standards follow it. Both Oracle and postgreSQL point to their ODBC
interface as their approximation to the newer standards.
One interesting enhancement to ns_db might be to exploit the data dictionary
which is now standard for SQL. Using this, you can get tables, columns, etc.
The queries don't change from db to db, so you can query capabilities, etc.
I'm slowly working on it, very slowly.
tom jackson
Andrew Piskorski
> 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 <a...@piskorski.com>
http://www.piskorski.com/
Bas Scheffers
> Your script/page level code can remain unchanged even when you switch
> databases.
thing to have also but to me it is separate from having bind variables
in the core nsdb api. I would expect any OR mapping tool to build on
it, rather than emulate it.
Bas.
Bas Scheffers
>> ns_db select $db "select * from people where country = $1 and age >
>> $2" [list "au" 25]
> Geez, names are more readable than numbers in any sizable query.
that I would like to see variables explicitly attached to the query
rather than simply saying: "use this existing Tcl variable."
> nspostgres supports the optional passing of an ns_set to define the
> bind vars
How does this work, do you have an example?
> And before answering "well, they can always use the variable
> directly" remember that both emulated and real bind vars give
> protect against sql smuggling.
I would never say that; not having to worry about quoting is one of
the main advantages of using bind variables/parameters.
Bas.
Tom Jackson
> 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
Bas Scheffers
>
> 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
underlying driver does not itself implement the correct functions
needed for allowing bind variables.
> 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.
Yup, that is what I would like to get rid of. ns_pg_exec is not
something I would like to use. I'd like to think we are above the PHP-
esque pg_connect/my_connect/pg_exec/etc!
> 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.
Ah, but which is the driver, libpq or nspostgres? If comparing to JDBC
and the various .NET schemes, it would be the latter, allowing for a
single syntax across all databases. But it would be up to the
individual drivers to comply with this standard, not for nsdb to
emulate it.
To be honest, I am not a "database switcher" and don't really care all
that much about being completely db independent. That said, in any
case I would like to get rid of ns_pg_exec and make any command that
uses bind variables or parameters (the more correct term when looking
at libpq) a standard nsdb command.
So for Postgres that would be $1, $2, etc and extra parameters to the
ns_db function after the SQL, for Oracle this might be using the Tcl
variables if that is how it natively does it. (the OACS pg driver is
already emulating Oracle, not working the way libpq does it natively)
MySQL I believe only supports parameters when using prepared statements.
That brings me to another subject: do we want prepared statements? I
use them all the time in JDBC, but that is simply because it is the
only way add variables; I rarely re-use them unless it is some bulk
data loader I am implementing.
Bas.
Dossy Shiobara
> 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 | do...@panoptic.com | 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)
Brett Schwarz
Yes!
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Brett Schwarz
----- Original Message ----
From: Bas Scheffers <b...@SCHEFFERS.NET>
To: AOLS...@LISTSERV.AOL.COM
Sent: Wednesday, April 16, 2008 4:20:46 PM
Subject: Re: [AOLSERVER] aolserver and Pgtcl
On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote:
> I have wanted to add bind variable support to nsdb for a _long_ time,
> but never got around to computing this support matrix that I describe
> above.
I don't really like bind variables, I would much rather see it
implemented like:
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.
Bas.
Jeff Rogers
>> 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.
> Yup, that is what I would like to get rid of. ns_pg_exec is not
> something I would like to use. I'd like to think we are above the
> PHP-esque pg_connect/my_connect/pg_exec/etc!
Considering how easy tcl makes such things, I'm rather surprised oacs
doesn't do something like
rename ns_db _ns_db
proc ns_db {args} {
if {[is_postgres]} {
eval ns_pg_exec $args
} else {
eval _ns_db $args
}
}
For that matter this parameter/bind var we're talking about could be
implemented in the near term as a compatible extension to ns_db through
exactly this channel.
-J
Jade Rubick
Jade
I would never say that; not having to worry about quoting is one of the main advantages of using bind variables/parameters.
Bas.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <list...@listserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Jade Rubick
Senior Architect
United eWay
jade....@uwa.unitedway.org
tel (503)285-4963
fax (707)671-1333
www.UNITEDeWAY.org
Dossy Shiobara
> 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?
Jade Rubick
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.
Jade Rubick
Senior Architect
United eWay
jade....@uwa.unitedway.org
tel (503)285-4963
fax (707)671-1333
www.UNITEDeWAY.org
Bas Scheffers
decided to mimic Oracle behavior simply to lower the amount of re-
writing of existing queries to be done. Which is perfectly valid for
their goal and how I would likely have done it. However, to implement
this in ns_db, there is no good reason to make it work the same way as
Oracle does unless it is decided we want it to work the same for every
database and this is the chosen syntax.
Bas.
Michael A. Cleverly
> On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote:
>
> I don't really like bind variables, I would much rather see it implemented
> like:
>
> ns_db select $db "select * from people where country = $1 and age > $2"
> [list "au" 25]
You mean:
ns_db select $db {select * from people where country = $1 and age
> $2} [list "au" 25]
I presume.
Bas Scheffers
> 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.
Jeff Rogers
> On 2008.04.17, Bas Scheffers <b...@SCHEFFERS.NET> 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
Bas Scheffers
Dossy Shiobara
> Do you have test code and results to back this up?
Not any longer. I did, ~5 years ago, when I worked as a developer on a
web app on Oracle 8i.
> 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,
I was dealing with a query that was taking ~200 ms to execute. That
didn't leave much breathing room for any of the other queries necessary
to build the page.
After some benchmarking, we had empirically deduced through repeated
tests that the query in question was spending ~180ms in parse (incl.
execution plan) and only ~20ms to execute and return the results.
Pushing the query into a stored procedure (effectively the same as a
prepared statement, as the stored procedure is parsed/compiled once)
dropped the execution time to ~30ms, which is within the window of
what was acceptable.
> Not that I doubt query parse time is a hit, but without numbers I
> won't buy that it is a big performance hit.
Generally, it's a non-issue. However, in certain cases, it can be a
material difference. In those cases, _not_ having the _capability_ to
use prepared statements because they aren't implemented, is a defect,
IMHO.
> 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.
As Bas pointed out, prepared statements are tied to the actual database
connection.
--
Dossy Shiobara | do...@panoptic.com | 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)
Don Baccus
> Bas Scheffers wrote:
>
>>> 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.
>> Yup, that is what I would like to get rid of. ns_pg_exec is not
>> something I would like to use. I'd like to think we are above the
>> PHP-esque pg_connect/my_connect/pg_exec/etc!
>
> Considering how easy tcl makes such things, I'm rather surprised
> oacs doesn't do something like
> rename ns_db _ns_db
> proc ns_db {args} {
> if {[is_postgres]} {
> eval ns_pg_exec $args
> } else {
> eval _ns_db $args
> }
> }
>
> For that matter this parameter/bind var we're talking about could be
> implemented in the near term as a compatible extension to ns_db
> through exactly this channel.
Our db api manages handles and transactions, for one thing.
But deep down in the guts it does:
switch $driverkey {
oracle {
return [eval [list ns_ora $type $db -bind
$bind $sql] $args]
}
postgresql {
return [eval [list ns_pg_bind $type $db -bind
$bind $sql]]
}
nsodbc {
return [eval [list ns_odbc_bind $type $db -
bind $bind $sql]]
}
The oracle call is different because LOBS are kludged through the
driver due to OCI weirdness.
Don Baccus
> 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.
Yes, Oracle will cache the parse for you automatically if you tell it
to.
Don Baccus
> It is the obvious way to do it. I would suspect that the OpenACS
> team decided to mimic Oracle behavior simply to lower the amount of
> implement this in ns_db, there is no good reason to make it work the
> same way as Oracle does unless it is decided we want it to work the
> same for every database and this is the chosen syntax.
Well, yes, of course compatibility with Oracle bind variables was a
goal, that's why we called it bind variable emulation.
However, in no imaginable world would we have built a mechanism that
*forced* you to append a list of value to the query, outside the query.
At times it's convenient, mostly, though, it just noise.
a bit like saying set a $1 [list foo] when set a foo works just fine.
And, yes, I have had 25 years of experience as a bona-fide compiler
writer and have a background in language design.
Don Baccus
> uild the page.
>
> After some benchmarking, we had empirically deduced through repeated
> tests that the query in question was spending ~180ms in parse (incl.
> execution plan)
What the plan generator does is not parsing. A little precision in
language would help the discussion move along smoothly, please.
Did you try speeding up the plan generation using Oracle's method of
giving hints?
Don Baccus
>
> 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
> On Apr 16, 2008, at 8:19 PM, Jade Rubick 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.
>
>
> Yes, Oracle will cache the parse for you automatically if you tell
> it to.
A little googling reveals that it's possible to do in-db plan caching
and, as of 11g, in-db result set caching also, in Oracle.
(the OpenACS db api provides result set caching ...)
Dossy Shiobara
> 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 | do...@panoptic.com | 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)
Tom Jackson
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.
Dossy Shiobara
> Actual prepared statements would be of little benefit in AOLserver
Huh? AOLserver offers pooled, persistent database connections.
--
Dossy Shiobara | do...@panoptic.com | 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)
Tom Jackson
tom jackson
On Wednesday 16 April 2008 21:51, Dossy Shiobara wrote:
> On 2008.04.16, Tom Jackson <t...@RMADILO.COM> 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.
--
Bas Scheffers
> Do you have test code and results to back this up?
because it is so easy to use prepared statements. I took the
"dellstore2" database from the postgres sample database project and
did a relatively simple query on a small data set:
select *
from customers c
join orders o on o.customerid = c.customerid
join orderlines l on o.orderid = l.orderid
where c.customerid = ?
1000 invocation with a random customerid between 0 and 20,000. (single
connection and thread) Obviously I ran these several times each and
took the average execution time. This to avoid the situation where the
computer was doing something else during one of the runs influencing
the results.
The prepared version gained 6.7% in performance.
Unfortunately, this only proves that this Java sample is faster using
prepared statements, not where those gains are made. I find it safe to
assume at least some of those gains are from Postgres, probably making
it worth creating at least a prototype for it in AOLserver where it
will also be easy to put a load tester against it and see what the
gains are in a real (multithreaded) situation.
Hmmm, come to think of it, I can do this test natively in libpq. I'll
try and do that soon.
Bas.
Dossy Shiobara
> 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 | do...@panoptic.com | 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)
Andrew Piskorski
> On 2008.04.17, Bas Scheffers <b...@SCHEFFERS.NET> 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 <a...@piskorski.com>
http://www.piskorski.com/
Andrew Piskorski
> 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 <a...@piskorski.com>
http://www.piskorski.com/
Andrew Piskorski
> 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 <a...@piskorski.com>
http://www.piskorski.com/
Dossy Shiobara
> > 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 | do...@panoptic.com | 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)
Tom Jackson
> > 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
Tom Jackson
> On 17/04/2008, at 9:25 AM, Tom Jackson wrote:
> > Your script/page level code can remain unchanged even when you switch
> > databases.
>
> That looks more like an OR mapping framework. I think that is a good
> thing to have also but to me it is separate from having bind variables
> in the core nsdb api. I would expect any OR mapping tool to build on
> it, rather than emulate it.
You are right this is not bind variables, but is isn't an OR mapping. It is
more similar to a stored procedure, but at the Tcl level.
Notice that the OpenACS solution is very compact, as it follows the Oracle
type of bind variables, that is, the bind variables are 'named'. Because they
are named, there is no need to bind the variables in order. Besides requiring
more user level code, you would also have to be careful every time you change
the query text: if the order of the parameters changes, you have to re-order
other parts of your code. Also, if the placeholders are anonymous tags
like '?' or '$1', you could actually have trouble figuring out the
relationship between placeholder and a local variable.
My particular code uses named queries (just like OpenACS, but grouped into
namespaces) and explicit parameters. My code requires more user level code,
but is slightly more flexible, for instance, you could switch from a static
data source to an SQL data source. But, it is also easier to manage
transactions. Most important to me is that adding a new database type doesn't
require editing the code, you just add a new implementation.