Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: REPEATED INSERT INTO ... 2nd thread

Re: REPEATED INSERT INTO ... 2nd thread

From
Christoph Haller
Date:
>
> On Mon, 2003-02-24 at 07:22, Christoph Haller wrote:
> > I've noticed subsequent executions of the same insert command are
slow.
> > I've searched the list archives for this matter and found several
> > entries
> > related, including suggestions how to speed up.
> > The standard answer from the core team is, use COPY.
> > Sorry, but this is from an application point of view not really an
> > option
> > if you're dealing with program variables.
>
> What do you mean by "program variables"? If you're just referring to
> variables in the programming language the DB client is written in, I
see
> no reason why you couldn't use COPY to input those.
>
Interesting. Suppose you have a C application fragment like
for (CmdIndex = start_index; CmdIndex < nRows; CmdIndex++){   sprintf(CmdLine,    "INSERT INTO AArray_Values (
Primary_Key,List_Pointer,\      Parameter_Name, Parameter_Code,\     Dim_Pointer, File_Pointer, Source_Type )\
VALUES( %d,%d,'%s',%d,%d,%d,'%s' );",    AArray_Values[CmdIndex].primary_key,
AArray_Values[CmdIndex].value_list_ptr,   AArray_Values[CmdIndex].parameter_name,
AArray_Values[CmdIndex].parameter_code,   AArray_Values[CmdIndex].dim_pointer,    AArray_Values[CmdIndex].file_pointer,
  AArray_Values[CmdIndex].source_type);   /*   call PQexec() to process   */}
 
How would you replace this by COPY?
Hoping I'm not partially out of order but I cannot see how to achieve
that.
Thanks for your time.

Regards, Christoph

Re: REPEATED INSERT INTO ... 2nd thread

From
Neil Conway
Date:
On Tue, 2003-02-25 at 03:59, Christoph Haller wrote:
>  for (CmdIndex = start_index; CmdIndex < nRows; CmdIndex++)
>  {
>     sprintf(CmdLine,
>      "INSERT INTO AArray_Values ( Primary_Key, List_Pointer,\
>        Parameter_Name, Parameter_Code,\
>       Dim_Pointer, File_Pointer, Source_Type )\
>       VALUES ( %d,%d,'%s',%d,%d,%d,'%s' );",
>      AArray_Values[CmdIndex].primary_key,
>      AArray_Values[CmdIndex].value_list_ptr,
>      AArray_Values[CmdIndex].parameter_name,
>      AArray_Values[CmdIndex].parameter_code,
>      AArray_Values[CmdIndex].dim_pointer,
>      AArray_Values[CmdIndex].file_pointer,
>      AArray_Values[CmdIndex].source_type);
>     /*
>     call PQexec() to process
>     */
>  }
> How would you replace this by COPY?

something like ...

PQexec("COPY xxx FROM stdin");
for (...)
{   sprintf(buf, "%d\t%d\t...\n",           values[0], values[1], ... values[n]);   PQputline(conn, buf);
}
PQputline(conn, "\\.\n");
PQendcopy(conn);

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC