postgresql updates very slow

[BigChimp]

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.

Have you looked at ludob's questions/remarks? It's a bit hard to discuss performance problems without knowing the details.

[99Percent]

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.

Have you looked at ludob's questions/remarks? It's a bit hard to discuss performance problems without knowing the details.

I did respond. Its really quite simple. I have a large stringlist (~1000 lines) of non parameterized insert statements like this:

Code: [Select]

INSERT INTO pedidos (tiendaid,tipo,fecha,usuario,pendiente) SELECT id,'S', now(),'Juanb', 't' FROM tiendas WHERE lugarid=5;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D12100033',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D05080185',3,3);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D10080199',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'BDK10TP',3,3);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D12060242',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D11070245',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D07110226',5,5);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D10110225',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D12010305',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D04073278',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D07110056',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D04062322',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'CANDHAUT',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D11030066',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D11100056',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D11071144',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D12021318',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D07023859',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D06063489',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D07050049',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D10050032',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT last_value FROM "tblPedidos_id_seq"),'D12010091',1,1);
....
If I use SQLDB either by TSQLQuery.ExecSQL, TSQLScript..Execute or TPQConnection.ExecuteDirect regardless of how I set the transaction blocks, it updates very slowly.

If I copy and paste the statements in a PGAdmin3 query window and hit F5 the statemens are executed almost instantly.

I have Linux Mint Nadia x64 Lazarus 1.0.8

[mirce.vladimirov]

First, I see you have "SELECT" statement in every line, if the last_value from tblPedidos is not changed then you should consider of selecting it once and using it in your insert statement.
Second, instead of 1000 "INSERT" try this way :

Code: [Select]

INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
or as others have said :

Code: [Select]

BEGIN ;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
COMMIT ;

and see if that changes something

[BigChimp]

Just guessing then:
- there are no other processes that touch the tables involved
- there are no differences in triggers between the 2 methods

If you're indeed using non-parametrized statements inside sqldb that's probably the bottleneck: these statements have to be prepared each time.

How pgadmin manages to be quicker: no idea. Perhaps it parses the entire statement and parametrises/prepares the queries behind the scenes?

[goodname]

Instead of using SELECT you can try the sequence functions.

Code: [Select]

INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
...

INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
(lastval('"tblPedidos_id_seq"'),'ALMWC001',2,2),
(lastval('"tblPedidos_id_seq"'),'D13010486',2,2),
...
http://www.postgresql.org/docs/8.4/static/functions-sequence.html

A comma separated list of values as mirce.vladimirov suggests should make the query run in a single commit and should not have to be explicitly stated.

As for PGAdminIII doing it faster that does not surprise me. The PGAdminIII authors have a very good knowledge of PostgreSQL and know all of the best tricks. If anyone can figure out the trick they are using that would be great.

[99Percent]

First, I see you have "SELECT" statement in every line, if the last_value from tblPedidos is not changed then you should consider of selecting it once and using it in your insert statement.
Second, instead of 1000 "INSERT" try this way :

Code: [Select]

INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
or as others have said :

Code: [Select]


BEGIN ;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
COMMIT ;

and see if that changes something

That indeed made a difference. Apparently having many insert statements slows SQLDB down considerably. What could it be? I am using PQConnection.ExecuteDirect

[99Percent]

Just guessing then:
- there are no other processes that touch the tables involved
- there are no differences in triggers between the 2 methods

If you're indeed using non-parametrized statements inside sqldb that's probably the bottleneck: these statements have to be prepared each time.

How pgadmin manages to be quicker: no idea. Perhaps it parses the entire statement and parametrises/prepares the queries behind the scenes?

Is there a way to disable the preparation of statements? I really just want to deal with the postgresql directly with no overhead involved.

[BigChimp]

Is there a way to disable the preparation of statements? I really just want to deal with the postgresql directly with no overhead involved.

I suspect no.
There is also the low level postgresql library if you want to deal with things at that level:
http://wiki.lazarus.freepascal.org/postgresql#PostgreSQL_package:_the_low_level_units

[goodname]

It may be possible to figure out what is going on using the EXPLAIN command. Not sure that explain will work with FPC 2.6.2 but it should work with FPC 2.7.

http://wiki.postgresql.org/wiki/Using_EXPLAIN

[99Percent]

I think I am going to use the low level posgres unit for all inserts and updates. Doesnt seem to be complicated at all.

[99Percent]

I was able to quickly hack up a unit that does updates to my server very fast. Here is the code

Code: [Select]

unit pqdirect;

//super simple postgresql direct execution of update and insert querys because de SQLDB implementation is unfortunate very slow
//TODO: add ssl

{$mode objfpc}{$H+}

interface

uses
  postgres3,
  pqconnection, db; // needed only for the EPQDatabaseError class

type
  EPQDatabaseError = class(EDatabaseError)
    public
        SEVERITY:string;
        SQLSTATE: string;
        MESSAGE_PRIMARY:string;
        MESSAGE_DETAIL:string;
        MESSAGE_HINT:string;
        STATEMENT_POSITION:string;
    end;

function ExecPQ(Query : String) : Boolean;

implementation

var
  ASQLDatabaseHandle    : PPGConn;
  res                   : PPGresult;
  msg                   : String;

function GetPQDatabaseError(res: PPGresult): EPQDatabaseError;
var
  serr:string;
  E: EPQDatabaseError;
  SEVERITY:string;
  SQLSTATE: string;
  MESSAGE_PRIMARY:string;
  MESSAGE_DETAIL:string;
  MESSAGE_HINT:string;
  STATEMENT_POSITION:string;
begin
  SEVERITY:=PQresultErrorField(res,ord('S'));
  SQLSTATE:=PQresultErrorField(res,ord('C'));
  MESSAGE_PRIMARY:=PQresultErrorField(res,ord('M'));
  MESSAGE_DETAIL:=PQresultErrorField(res,ord('D'));
  MESSAGE_HINT:=PQresultErrorField(res,ord('H'));
  STATEMENT_POSITION:=PQresultErrorField(res,ord('P'));
  serr:=PQresultErrorMessage(res)+LineEnding+
    'Severity: '+ SEVERITY +LineEnding+
    'SQL State: '+ SQLSTATE +LineEnding+
    'Primary Error: '+ MESSAGE_PRIMARY +LineEnding+
    'Error Detail: '+ MESSAGE_DETAIL +LineEnding+
    'Hint: '+ MESSAGE_HINT +LineEnding+
    'Character: '+ STATEMENT_POSITION +LineEnding;
  E:=EPQDatabaseError.CreateFmt('ExecPQDirect: %s)', [serr]);
  E.SEVERITY:=SEVERITY;
  E.SQLSTATE:=SQLSTATE;
  E.MESSAGE_PRIMARY:=MESSAGE_PRIMARY;
  E.MESSAGE_DETAIL:=MESSAGE_DETAIL;
  E.MESSAGE_HINT:=MESSAGE_HINT;
  E.STATEMENT_POSITION:=STATEMENT_POSITION;
  result:=E;
end;

procedure DisconnectPQ;
begin
  if Assigned(ASQLDatabaseHandle) then PQFinish(ASQLDatabaseHandle);
  ASQLDatabaseHandle:=Nil;
end;

function ConnectPQ : Boolean;
var
  FConnectString : String;
begin
  Result:=False;
//enter your connection parameters here
  FConnectString := 'user=[user] password=[password] host=[host] dbname=[database]';
  ASQLDatabaseHandle := PQconnectdb(pchar(FConnectString));
  if (PQstatus(ASQLDatabaseHandle) = CONNECTION_BAD) then begin
    msg := PQerrorMessage(ASQLDatabaseHandle);
    DisconnectPQ;
  end
  else Result:=True;
end;

function ExecPQ(Query : String) : Boolean;
var
  E : EPQDatabaseError;
begin
  Result:=False;
  if not assigned(ASQLDatabaseHandle) then if not ConnectPQ then exit;
  res := PQexec(ASQLDatabaseHandle,pchar(query));
  try
    if (PQresultStatus(res) <> PGRES_COMMAND_OK) then begin
      E:=GetPQDatabaseError(res);
      DisconnectPQ;
      raise E;
     end else Result:=True;
  finally
    PQclear(res);
  end;
end;

end.

Why cant the ExecuteDirect of the PQConnection Class be like this?

[snorkel]

Postgresql will autocommit each time you do a insert in a loop, like was said earlier you can simply add a counter and then use the MOD operator on the count, something like if count mod 1000 = 0 then commit.

So instead of doing a commit per insert you do 1000 inserts at a time, this will give a huge performance boost.

The downside is if you need to rollback it will rollback 1000 rows instead of 1.