postgresql updates very slow

[99Percent]

I have been trying for a while to make my updates and inserts to postgresql server faster.

For some reason they are extremely slow. I have tried different components and metthods including TSQLQuery. ExecSQL, TSQLScript, TPQConnection.ExecuteDirect. Also very slow using Devart UniDac components. (They were unable to solve my problem).

I know its not a connectivity issue or a workstation problem r because if I save the SQL statements to a file and then execute them with PGAdmin3 the execution is almost instantaneous (1000x speed atleast).

Please help on how to troubleshoot this.

[marcov]

If you do bulk updates, commit the transaction every 1000 tuples.

[mirce.vladimirov]

I had similar problem very recently. I was doing a massive update thru a "while not query.EOF" loop and that was very very time consuming. Code was like:

Code: [Select]

firstquery.first;
while not firstquery.EOF do begin
        //do something
       secondquery.add('update mytable set field1=:param1 where....');
       secondquery1.parambyname('param1').value:=myvariable;
       secondquery.execSQL;
      firstquery.next;
end;
I consider myself still a newbie to Lazarus/Delphi and also newbie to SQL, so I went to do lots of research and lots of tests, and found a way to do a massive updates thru SQL and i got results >1000 faster. A Lazarus program was working almost 7 hours, the new approach using only the SQL finished the same job within ridiculous 15-20 seconds.
Since I solved this one, I also found a way to solve another huge mega massive time consumig issue which worked about 8 hours (maybe even 8h and 15 minutes) and that one finishes now in exactly 35 seconds.
After I solved these two I'm proud to say that I'm not newbie to SQL anymore, now I'm more like a SQL ninja (on my way to become a SQL jedi, hopefully)   
So I recomend using SQL instead of the programing language whenever it's possible.

[99Percent]

I create a stringlist with many inserts separated with semicolons

Then I submit the stringlist in the objects SQL and execute. It takes a long time.

I have tried many combinations transactions and its the same.

If I save the stringlist as a file and open it with PGAdmin3 it executes it in seconds.

[ludob]

If I save the stringlist as a file and open it with PGAdmin3 it executes it in seconds.

sqldb executes your big query in one transaction. PGAdmin3 will do it in autocommit mode and commit every insert separately. Try surrounding the query with a BEGIN; ... COMMIT; in PGAdmin3 before executing and compare the timings. 
Inserting or updating in transactions covering large blocks of data is normally faster than committing individual rows. Except when there is an interference possible with other transactions that block each other. Are all inserts in the same table (watch out for triggers)? Are other programs writing to the same tables or are setting explicit locks? Are you seeing deadlocks?

[Julius]

Hello 99Percent,

I create a stringlist with many inserts separated with semicolons

Then I submit the stringlist in the objects SQL and execute. It takes a long time.

I have tried many combinations transactions and its the same.

If I save the stringlist as a file and open it with PGAdmin3 it executes it in seconds.

It's just my quess, but I presume the components have to parse every statement first before executing. That means the types of the collumns must be determined and that is done executing other queries, so instead of executing 1 query many more are executed. That takes time of course.
For what you write there may be an easy solution: instead of using many inserts use just one:

Code: [Select]

INSERT into foo(field1, field2)
VALUES ('value11', 'value12'),
  ('value21', 'value22'),
  .....
  ('valueN1, valueN2);That should increase the speed of your program a lot.

Good luck!

[BigChimp]

@Julius, yes, that, or a parametrized query like mirce.vladimirov showed.

[ttomas]

I'm not a PostgreSQL user, Firebird and MSSQL is my daily job.
With Firebird best performance code is:

Code: [Select]

secondquery.add('update mytable set field1=:param1 where....');
secondquery.Prepare;
p1 := secondquery.parambyname('param1');
p2 := secondquery.parambyname('param2');
...
pN := secondquery.parambyname('paramN');

firstquery.first;
f1 := firstquery.fieldbyname('field1');
f2 := firstquery.fieldbyname('field2');
...
fN := firstquery.fieldbyname('fieldN');
while not firstquery.EOF do begin
        //do something
       p1.value := f1.value;
       pX.value := fY.value;
       secondquery.execSQL;

       committrans1000; // commit every 1000 or 500 or 100 inserts

       firstquery.next;
end;

Don't use fieldbyname or parambyname in loop

[mirce.vladimirov]

A Lazarus program was working almost 7 hours, the new approach using only the SQL finished the same job within ridiculous 15-20 seconds.
Since I solved this one, I also found a way to solve another huge mega massive time consumig issue which worked about 8 hours (maybe even 8h and 15 minutes) and that one finishes now in exactly 35 seconds.

Noooo, i was wrong and exaggerated,  that are not the correct timings,  right now I'm runnig the two programs one after another and they complete in total of 7 minutes together (4 min.for first one, and 3 for second)   but that is still a lots of improvement compared to total of >15 hours.

So, previos total timing for the two programs (they depend on each other) used to be >15 hours and now is 7 minutes.
Do it thru SQL it's much faster. Create one single query which will be called once, with one "query.execSQL" instead of repeating a loop for 1000's of times

[BigChimp]

So, previos total timing for the two programs (they depend on each other) used to be >15 hours and now is 7 minutes.
Do it thru SQL it's much faster. Create one single query which will be called once, with one "query.execSQL" instead of repeating a loop for 1000's of times

Posting this without the SQL/code involved is a bit pointless though... Are you talking about insert statements? Did you split out the parameter assignment as ttomas indicated?

[mirce.vladimirov]

Posting this without the SQL/code involved is a bit pointless though... Are you talking about insert statements? Did you split out the parameter assignment as ttomas indicated?

My point is that DB I/O operations are much much faster wher performed thru pure SQL instead doing complicated combinations between the SQL and programing language because it's the programing language that slows it down, specificaly using "for" and "while" loops.
 Do a test: create a SQL query and then go thru it with a lazarus/pascal/delphi for loop in order to calculate the total of some fields :

Code: [Select]

query1.close;
query1.sql.clear;
query1.sql.text:='select id_code, salary from table1';
query1.open;

query1.first;
while not query1.EOF do begin
          the_total:=the_total + query1.fieldbyname('salary').asinteger;
                query2.close;
                query2.sql.clear;
                query2.sql.text:='update table2 set total=:thetotal  where field_this=that and  somtehing_else=the_other_thing  and field_with_param=:param_number_one ';
                query2.Parambyname('thetotal').asinteger:=the_total;
                query2.Parambyname('param_number_one').asinteger:=some_value;
                query2.execSQL;
          query1.next;
end;

Here we are calling "update" query for each record from query1, you can't notice the speed diference if you have small number of records, so to test the speed you need a table with 200 thousend records in table1. That way you are actualy calling query2.execSQL 200 000 times.
Alternative to this but this time done thru one single call to pure SQL would be :

Code: [Select]

query1.close;
query1.sql.clear;
query1.sql.add('update table2, table1 set total=sum(salary) where field_this=something and  somtehing_else=the_other_thing  and field_with_param=:param_number_one ');
query1.Parambyname('param_number_one').asinteger:=some_value;
query1.execSQL;

I believe that this example is quite simple but good enough to see my point.
There is a bond between the programin language and the DB, and in a certain poin the program calls the database passing the contain of the query. My conclusion is that using this bond slows the program, and naturaly slowdown multiplies with repeating this operation.
This is not related only to Postgress but whith calling SQL in generaly.

[BigChimp]

@mirce: yes, obviously running set-based SQL statements is gernally much faster than loops.

Unfortunately, sometimes you cannot avoid running loops in code.

[99Percent]

If I save the stringlist as a file and open it with PGAdmin3 it executes it in seconds.

sqldb executes your big query in one transaction. PGAdmin3 will do it in autocommit mode and commit every insert separately. Try surrounding the query with a BEGIN; ... COMMIT; in PGAdmin3 before executing and compare the timings. 
Inserting or updating in transactions covering large blocks of data is normally faster than committing individual rows. Except when there is an interference possible with other transactions that block each other. Are all inserts in the same table (watch out for triggers)? Are other programs writing to the same tables or are setting explicit locks? Are you seeing deadlocks?

if in PGAdmin3 I enclose my many inserts query with begin; and commit; the query is executed instantly, even faster I think. In lazarus using sqldb it takes several minutes.

I am thinking it could be a libpq problem. I am using linux mint Nadia x64 and the libpq version is 9.1.9-0ubuntu12.10 Ubuntu:12.10

Does anyone know if PGAdmin3 uses libpq or does it have its own internal code to interface to postgres servers?

[Julius]

Does anyone know if PGAdmin3 uses libpq or does it have its own internal code to interface to postgres servers?

pgAdmin uses libpq.
http://www.pgadmin.org/docs/1.8/index.html
"Connection to PostgreSQL is made using the native libpq library."

[exdatis]

Try: http://sourceforge.net/projects/epgsql/
I used SQLdb, so you can see the code and maybe get some ideas...
Regards