pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@yahoo.com>
To: Kyle <kyle@actarg.com>
Cc: pgsql-sql@postgresql.org
Cc: JanWieck@yahoo.com
Subject: Re: Strange slow behavior in backend
Date: Thu, 14 Dec 2000 08:08:48 -0500 (EST)
Message-ID: <200012141308.IAA04143@jupiter.jw.home> (raw)
In-Reply-To: <3A3795BC.44C91514@actarg.com>

Kyle wrote:
> I'm using 7.0.1 with a TCL frontend.
>
> I have a schema that splits large files into tuple-sized bites and
> stores them in a table.  This was done before TOAST in order to store
> large files.
>
> I have a backend TCL function that re-assembles the file like this:
>
> -- Fetch the specified document data, reassembling the bits back
> together
> -- in the right order.
> -- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype)
> create function cont_doc_fetch(int4,timestamp,varchar) returns text as '
>
>     set odata {}
>     spi_exec -array d "select data from cont_doc_data where crt_by =
> \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\' order by seq" {
>         append odata $d(data)
>     }
>     return $odata
>     ' LANGUAGE 'pltcl';
>
> This worked great until I put a real big file in (about 5M).  Then, when
> I tried to fetch the file, it seemed really slow (about 60 seconds).  I
> tried reassembling the file in the frontend instead and my time dropped
> to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> to pg_exec that returns a list of tuples):
>
>     set data {}
>     set tuple_list [mpg::qlist "select data from $ca(prefix)_doc_data
> where crt_by = $crt_by and crt_date = '$crt_date' and ctype = '$ctype'
> order by seq"]
>     foreach rec $tuple_list {
>         append data [lindex $rec 0]
>     }
>
> The only difference I can identify is whether the re-assembly TCL code
> is running as a procedural language (backend) or in the frontend.
> Anyone have any idea why the difference is so dramatic?
>
> Jan:
> Is this the difference between old TCL and new TCL (with multi-port
> objects)?  Or is there something else about the way the backend handles
> large chunks of data that would mark the difference?

    That's  it.  It  shouldn't have to do with the amount of data
    invoked, but with the number of tuples returned  by  spi_exec
    and spi_execp commands.

    Due  to  backwards  compatibility, all commands in the PL/Tcl
    handler  still  use  the  old  string  interface.  Thus,  the
    procedure  text  for  each  tuple (in your case "append odata
    $d(data)") is evaluated from it's string representation again
    and  again,  needs to be interpreted and precompiled for each
    single tuple by the Tcl interpreter. I think using  Tcl_Obj's
    here would cause a substantial improvement.

    I  plan  to  do  a  major overhaul of PL/Tcl after we have an
    interface for  functions  returning  tuple  sets.  This  will
    include   loosing   the  backward  compatibility  to  pre-8.0
    releases of Tcl because of using the Tcl_Obj interface  only.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





view thread (4+ messages)  latest in thread

Message-ID: <200012141308.IAA04143@jupiter.jw.home>
Permalink:  ../200012141308.IAA04143@jupiter.jw.home/
Also on:    postgresql.org/message-id/200012141308.IAA04143@jupiter.jw.home

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: janwieck@yahoo.com, kyle@actarg.com
  Subject: Re: Strange slow behavior in backend
  In-Reply-To: <200012141308.IAA04143@jupiter.jw.home>
  References: <3A3795BC.44C91514@actarg.com> <200012141308.IAA04143@jupiter.jw.home>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox