Thread

1. update by one transaction

   gyurika <gyurika@prolan.hu> — 1998-06-11T08:16:31Z

   Hello,
   
   I have tried to write my problem, but I think nobody couldn,t understand
   it (because of my bad English, I think).
   
   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:
   
   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, "COMMIT");
   
   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!
   
   

2. Re: [SQL] update by one transaction

   Aleksey Dashevsky <postgres@luckynet.co.il> — 1998-06-11T08:54:19Z

   
   On Thu, 11 Jun 1998, Lendvary Gyorgy wrote:
   
   > Hello,
   > 
   > I have tried to write my problem, but I think nobody couldn,t understand
   > it (because of my bad English, I think).
   > 
   > 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:
   > 
   > 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, "COMMIT");
   > 
   > 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!
   > 
   It seems you simply forgot to start transaction before the loop. So you
   need to prepend this code with begin statement:
   
   PQexec(conn, "BEGIN");
   
   Hope, this will help!
   
   Aleksey.