Thread

1. update by one transaction

   gyurika <gyurika@prolan.hu> — 1998-06-11T13:26:57Z

   BEGIN isn't the solution, just I forgot to write it. So I repeat my
   problem again:
    
   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");
   
   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!!!
   
   

2. Re: [SQL] update by one transaction

   dave madden <dhm@paradigm.webvision.com> — 1998-06-11T17:06:19Z

    =>From: Lendvary Gyorgy <gyurika@prolan.hu>
    =>...
    =>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");
    =>
    =>This program is very, very slow.
   
   The first thing I'd check is that you have an index on
   boci(row_number).  If you do, or if adding one doesn't help, then
   perhaps you could split boci into two tables:
   
   		boci1( row_number int, col2 float )
   		boci2( row_number int, col3 float )
   
   Then you could use the COPY command to load data from your array.  To
   select things, just join boci1 and boci2 on row_number (or create a
   view, if PostgreSQL supports 'em).
   
   d.