Re: Released updated Tcl interfaces: pgin.tcl-2.1.0, - Mailing list pgsql-interfaces

Whoa, I'll be off getting that [pgin.tcl] soon, I've been playing with the
previous version a little.

However, since making some changes to my code I've not had time to do the
investigation so I just thought I'd ask if there's been any reports on the
relative speed of normal sql execution against prepared statements.

The situation is that I had a proof of concept type implementation of a db
interface in an application that used plain SQL execution with pg_execute (I
think that's the one) and taking it closer to a production level system I
restructured my code a little and switched to using prepared statements. As I
say I haven't had time to investigate if it's my code changes or the use of
prepared statements but I think my newer version runs noticibly slower. I've
got this idea in my head that it's the binary packing that's slowing things,
coupled with the fact that the statements are pretty simple insert
commands. Transactions shouldn't be an issue as at worst this newer version
uses the same number of inserts per transaction as the older version and at
best uses twice the number (basically I can't remember if the earlier version
used transactions at all).

So to summarise, I'm thinking the packing into the prepared statement execution
protocol is perhaps taking significantly longer than the db server is to parse
the plain SQL for such small, simple inserts but do not have any experimental
results to actually show that yet.

Other information: the db server runs on a separate box on a gigabit network,
the client is a TCL app with some extensions, which may have an effect on the
main TCL loop (takes 5 minutes to start with 100% (near enough) cpu usage to
load data from another remote system (probably not more than 100MB) and this
data load definitely could not be IO bound.

BTW, thanks a bunch for this interface. Because of the above extensions I
really wasn't happy about pluging in another extension for PG but knew of this
TCL only implementation so went straight for it. It proved the concept and it's
only not in a financial data system now because my interface was under-designed
for a production system and there's a perception that the PgSQL idea needs to
be proved capable of handling the load. Which is fair enough, although I do
keep saying that I see no reason for it not to handle a couple of dozen
clients logging user interactions, even running on the play linux box it's
currently running on. Trouble with that is this noticible slow down (noticed
when doing quite a few inserts in response to a single user action).

Hmmm...someone should shoot me for writing long rambling emails to these lists.

--
Nigel Andrews


On Sun, 20 Jun 2004, L J Bayuk wrote:

> Updated versions of these two implementations of the Tcl interface to
> PostgreSQL were released recently:
> 
>   pgin.tcl, the "pure-TCL" implementation, at:
>        http://gborg.postgresql.org/project/pgintcl/
>      Currently available for download:
>      + pgintcl-2.1.0.tgz
> 
>   pgtcl-ng, the libpq-based "next generation" implementation, at:
>        http://gborg.postgresql.org/project/pgtclng/
>      Currently available for download:
>      + pgtcl1.5.1.tar.gz            (Source distribution)
>      + pgtcl1.5.1-win32.zip         (Binary distribution for Windows)
>      + pgtcldocs-20040609.zip       (A Pgtcl Reference Manual)
>        You can look at the manual online, until it finds a better home, at:
>            http://lbayuk.home.mindspring.com/pgtcldocs/
> 
> Pgtclng is the "next generation" libpq-based Tcl interface to PostgreSQL.
> It adds many new commands and features which take advantage of new
> capabilities in the PostgreSQL-7.4 protocol and libpq client library.
> Pgin.tcl is a Tcl interface to PostgreSQL which is written entirely in Tcl.
> It is mostly but not entirely command-compatible with pgtclng.  Both of
> these implementations are only for use with PostgreSQL 7.4 and up.
> 
> New features in these releases include:
>   * Get command status tag with: pg_result -cmdStatus (suggested by levanj)
>   * Parse, bind, and execute SQL statement with: pg_exec_params,
>     complements pg_exec_prepared; both are binary safe.
>   * Extended pg_exec to support optional parameters (idea from karl's
>     implementation in Gborg pgtcl CVS); this is a text-format-only version
>     of pg_exec_params.
>   * Bug fix (pgin.tcl) and improvement (pgtclng) to pg_execute error handling.