Re: [GENERAL] slow inserts and updates on large tables

> At 16:10 +0200 on 17/2/99, Jim Mercer wrote:
> You probably didn't understand me. If you convert it to tab delimited text
> and then use COPY table_name FROM filename/stdin instead of INSERT, it will
> be much faster, because you don't have to do the parsing and planning on
> each line, but only on the whole copy.
>
> I didn't tell you to use the data directly from those text files...
>
> PQexec( con, "COPY table1 FROM stdin" );
>
> while (data_still_coming) {
>
>    sprintf( line, "%s\t%s\t%s\n" , item1, item2, item3 );
>    PQputline( con, line );
>
> }
>
> PQputline( con, ".\n" );
> PQendcopy(con);

i will test this with my insertama program, but i see some problems with this.

firstly, it assumes that all of your applications programs are updated each
time you modify the structure of the table.

i am using "insert into testtable (fieldname1, fieldname2) values ('1', '2');"

this allows the applications to remain unchanged if new fields are added.

also, it doesn't seem to address the issue of updates, which suffer from worse
performance than inserts.

what is a realistic number of inserts per second under postgresql, with or
without an index?

--
[ Jim Mercer    Reptilian Research      jim@reptiles.org   +1 416 410-5633 ]
[ The telephone, for those of you who  have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail.            ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]