Re: [AOLSERVER] Compression
On Tuesday 15 April 2008 18:22, Tom Jackson wrote: I'll try to combine Daniel's code with my filter to turn on the switch for ns_return. I have a test release of the nszlib module, which handles ADP compression, and static files (taken from Daniel's example filters), plus dynamic data returned via ns_return. There is also an example configuration section: http://rmadilo.com/files/nszlib/ Feedback needed! I also discovered a strange feature that I don't think is documented anywhere. Server modules are added like this: ns_section ns/server/server1/modules ns_param mymodule /path/to/bin/mymodule.so # Tcl module (in server1/modules/tcl/mytclmodule) ns_param mytclmodule tcl With nszlib, there is now both a shared and Tcl module, However if the Tcl module is in server1/modules/tcl/nszlib, you only need one entry to cover both: ns_section ns/server/server1/modules ns_param nszlib /path/to/bin/nszlib.so AOLserver automatically looks in the private tcl directory for nszlib! 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] Compression
I don't know where it's documented, but that's been the behavior for a long time; we've relied on it since 3.4.2, and I think Alex Hisen showed me it working in 2.0. I wanted to remember reading it in the Tcl modules section or the server startup section of the old 3.0 manual that's still @ tcl.tk.org, but a reread doesn't find it. Reconstructing the history of our use of the pattern suggests we never did see it documented. I think I'll go try to find a wiki page to update :-) It's super handy when you want to have a very simple C API that can leave most of its argument parsing, integrity checking, or even its complete data initialization to a set of Tcl wrappers. It's the same design pattern (though it uses init.tcl and doesn't package its own tcl library to do it) that's allowed nsperm to remain somewhat usable all these years. In fact, we took our original use of the pattern from nsperm, and it took us another year or so to discover we didn't have to package things quite so badly (i.e., by putting all the tcl files that supported a C API directly in the modules/tcl directory). -- ReC -Original Message- From: AOLserver Discussion [mailto:[EMAIL PROTECTED] On Behalf Of Tom Jackson Sent: Wednesday, April 16, 2008 9:57 AM To: AOLSERVER@LISTSERV.AOL.COM Subject: Re: [AOLSERVER] Compression On Tuesday 15 April 2008 18:22, Tom Jackson wrote: I'll try to combine Daniel's code with my filter to turn on the switch for ns_return. I have a test release of the nszlib module, which handles ADP compression, and static files (taken from Daniel's example filters), plus dynamic data returned via ns_return. There is also an example configuration section: http://rmadilo.com/files/nszlib/ Feedback needed! I also discovered a strange feature that I don't think is documented anywhere. Server modules are added like this: ns_section ns/server/server1/modules ns_param mymodule /path/to/bin/mymodule.so # Tcl module (in server1/modules/tcl/mytclmodule) ns_param mytclmodule tcl With nszlib, there is now both a shared and Tcl module, However if the Tcl module is in server1/modules/tcl/nszlib, you only need one entry to cover both: ns_section ns/server/server1/modules ns_param nszlib /path/to/bin/nszlib.so AOLserver automatically looks in the private tcl directory for nszlib! 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 - 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] aolserver and Pgtcl
I think the ns_odbc driver can survive from one connection to another. When I 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 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. -- 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_share (was Active participation)
Hi, Brent Welch wrote some ns_share code which worked with Tcl variable traces to emulate the original code. It works except for a few edge cases but is generally considered less efficient and flexible than the new nsv_* commands. So, when I say we gave up on ns_share I mean we stopped applying our patches to the Tcl core for ns_share, allowing us to use the Tcl source un-altered. -Jim On Apr 14, 2008, at 1:37 PM, Brett Schwarz wrote: True -- the original threading in AOLserver 2.0 way back in 1995 (yes, 1995) was based on a thread-safe hacked Tcl 7.3. I can't recall where I got it -- I think someone at UCB did the work. We later hacked Tcl 7.4 and 8.1 on our own before enough support was in the Tcl core and we were ready to give up on ns_share which was a significant hack that couldn't reasonably be brought forward. I was just looking at file.tcl, and it still uses ns_share. Is that ok? Or should that be changed? 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. -- 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_QueueWait
There is also the Ns_Task API which is more flushed out, and is used by the new ns_http command. But each Ns_Task implementation requires coding in C, which is probably okay for those needing high performance. It is still difficult to figure out exactly how it should be used. Anyway, for those who haven't looked at ns_http or Ns_Task, what happens is you create a queue, in a new thread. You can add 'tasks', A task as a series of states, like sending data, receiving data, and each task has an id. So one thread can add tasks, and a different thread can 'wait', you just have to us nsv_ to store the task id. I'm not sure that it could be useful from the driver thread... tom jackson On Wednesday 16 April 2008 11:19, Jim Davidson wrote: Hi, I'm glad you noticed Ns_QueueWait. It was designed to address one of the most common scalability problems we had noticed at AOL where threads get backed up waiting for a response from a remote resource (e.g., REST or SOAP result). The idea (as mentioned in the change note below) is that you wait for I/O events to complete, accumulate all the potentially slow remote stuff before queuing the request for processing, using connection local storage as needed to hold the remote data. Sadly, only the low level API was written. Adding a more convenient HTTP interface that you could then add REST or SOAP things on top would make sense but wasn't done. It's possible you could write a simple Tcl wrapper -- the callbacks would look like Tk callbacks. However, you'd need to be careful using it and understand that the Tcl interp used for the queue wait callbacks would not be the same as the Tcl interface used later plus there's the trouble mapping Tcl I/O objects to something you can wait on with a call to select() or poll(). Thinking about it now as I'm typing, perhaps a smarter solution would have been to use the Tcl event loop in a 2nd dedicated thread: Requests would be read with the current AOLserver I/O event loop and then passed to a Tcl event loop if needed before being queued for execution by a dedicated thread. Hmm -Jim On Apr 14, 2008, at 8:15 PM, Tom Jackson wrote: Since Jim Davidson is somewhat monitoring the newsgroup at the moment, I want to take advantage... One new feature of AOLserver 4.5 is the Ns_QueueWait API: Ns_QueueWait: New interface to enable event-driven callbacks in the driver thread before dispatching to pools for processing. This allows drivers to augment data received from the client (headers, request, content) with additional data fetched over the network (likely stored in connection-local storage via Ns_Cls). An example would be to add certain personalization data received via a Web service. The benefit of this approach is to accumulate additional data efficiently with driver-thread based event callbacks instead of through potentially blocking calls within connection threads. My first question is if there are any examples of using this interface? Second is just a note to Jeff Rogers: this looks like a plugin interface that could work for your application. 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 - 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] aolserver and Pgtcl
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.
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.
--
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] aolserver and Pgtcl
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. 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_share (was Active participation)
On 2008.04.16, Jay Rohr [EMAIL PROTECTED] wrote: I am using the tsv (from the thread package) functions within aolserver and, at least for my uses, it is faster than the nsv equivalents. [...] Can we please see the benchmarks (code and results) you used to demonstrate this? -- 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] aolserver and Pgtcl
On Wednesday 16 April 2008 15:11, Don Baccus wrote:
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
--
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] aolserver and Pgtcl
On Apr 16, 2008, at 3:44 PM, 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. If you do, it needs to be a config option, because with Oracle you do *not* want to do this in ns_db. 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] 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. -- 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] aolserver and Pgtcl
On Apr 16, 2008, at 4:20 PM, Bas Scheffers wrote: 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. 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] aolserver and Pgtcl
On Wednesday 16 April 2008 16:20, Bas Scheffers 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 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 -- 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] aolserver and Pgtcl
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. Bas. -- 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] aolserver and Pgtcl
This is how pgtcl actually does bind variables... - Original Message From: Bas Scheffers [EMAIL PROTECTED] To: AOLSERVER@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. -- 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] aolserver and Pgtcl
The other is performance. Jade On Wed, Apr 16, 2008 at 5:40 PM, Bas Scheffers [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] tel (503)285-4963 fax (707)671-1333 www.UNITEDeWAY.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, 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] aolserver and Pgtcl
On Wed, Apr 16, 2008 at 5:20 PM, Bas Scheffers [EMAIL PROTECTED] wrote:
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.
--
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.
