Thread: update by one transaction

Hi,

I have a table for instance boci (row_number int, col2 float, col3
float). The boci table has about 10000 rows.
I want to update col3 column of the table in every row with different
values. Desired values are in an array has 10000 elements. I want to be
the value of the col3 column in the first row equal to the first element
of the array and so on.
I've done it with a 'for' cycle, but it is very slow. I want to do
theese updates with one transaction.

Here is a little piece of my program:

PQexec(conn, "BEGIN");
for (i=0; i<10000; i++)
{
    sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
array[i], i);
    PQexec(conn, buff);
}
PQexec(conn, "END");

I can't solve this problem with COPY command becouse I have to update
col3 column in every minute, but I don't want to change row_number and
col2 columns. My problem is the updating and not the creation of the
table. Creation is fast enough.
This program is very, very slow. Is there any way making this program
much faster (for instance with CURSOR or 'block write' or something
else)? Please write me a little program that describes your ideas!

Thanks for your help in advance!

Please help me!!!

> I have a table for instance boci (row_number int, col2 float, col3
> float). The boci table has about 10000 rows.
> I want to update col3 column of the table in every row with different
> values. Desired values are in an array has 10000 elements. I want to be
> the value of the col3 column in the first row equal to the first element
> of the array and so on.
> I've done it with a 'for' cycle, but it is very slow. I want to do
> theese updates with one transaction.
>
> Here is a little piece of my program:
>
> PQexec(conn, "BEGIN");
> for (i=0; i<10000; i++)
> {
>     sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
> array[i], i);
>     PQexec(conn, buff);
> }
> PQexec(conn, "END");
>
> I can't solve this problem with COPY command becouse I have to update
> col3 column in every minute, but I don't want to change row_number and
> col2 columns. My problem is the updating and not the creation of the
> table. Creation is fast enough.
> This program is very, very slow. Is there any way making this program
> much faster (for instance with CURSOR or 'block write' or something
> else)? Please write me a little program that describes your ideas!

Try creating an index on row_number. Right now to do the update the whole
table has to be scanned. With an index only the matching rows will be
scanned.

-dg

David Gould           dg@illustra.com            510.628.3783 or 510.305.9468
Informix Software                      300 Lakeside Drive   Oakland, CA 94612
 - A child of five could understand this!  Fetch me a child of five.