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

On Tue, 22 Jun 2004, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > 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.
>
> You can lose if the prepared statements are parameterized in such a way
> that the planner can't see critical information. This has been hashed
> over many a time in the pgsql-performance archives (though mostly in the
> context of queries in plpgsql functions). Try EXPLAIN EXECUTE and see
> if you get a materially different plan than with the straight query.

Cheeers Tom, I must admit that it only fleetingly crossed my mind that it could
be a server/planning issue. However, I did go away and check; eventually. As
the two plans are boringly similar, only difference being the param vs const
parts in verbose info with both showing type 25 (text I hope), I'll not even
attach them here. Also the timings were much the same.

I did then go and do some checks running from the TCL client
app; eventually. These results I show below:

% DBCore::_Prepare contriblog aaa {text} {select current_timestamp, $1}
0
% time {DBCore::_ExecPrepared contriblog aaa hello} 100
39370 microseconds per iteration

% time {DBCore::_Execute contriblog {select current_timestamp, 'hello'}} 100
1250 microseconds per iteration

% time {pg_exec_prepared $DBCore::_contriblog_conn aaa T {T} {hello}} 100
39220 microseconds per iteration

[I used only 100 iterations since using 1000 for the prepared statements tends
to block the application main loop long enough for the other components to be
affected, which means there's a big nasty delay as they reinitialise after the
test finishes and I wanted to sure of not including that time in the test
results.]

This is not one of the queries the app. is using, surprisingly, but the results
are in line with the results from a real query. The _ExecPrepared behaves a
little like pg_execute and _Execute is just a small wrapper around
pg_execute. Just to check that the extra code in _ExecPrepared isn't
significant I ran the pg_exec_prepared directly, and although it seems it adds
200usec to the execution time it's not really significant.

Obviously the prepared query execution is an order of magnitude slower and it's
the TCL interface that's making it so.

This is still the older version of pgintcl I hasten to add, took me this long
to run these few simple tests no chance of updating the package yet! Also, it
doesn't detract from the usefulness of this pure TCL interface for me, I've
switched the code path taken back to using plain SQL via pg_execute and it's
not noticable in the application once more.

--
Nigel Andrews