Lazarus
Programming => Databases => Topic started by: 99Percent on August 22, 2013, 05:26:37 pm
-
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.
-
If you do bulk updates, commit the transaction every 1000 tuples.
-
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:
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) :D
So I recomend using SQL instead of the programing language whenever it's possible.
-
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.
-
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?
-
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:
INSERT into foo(field1, field2)
VALUES ('value11', 'value12'),
('value21', 'value22'),
.....
('valueN1, valueN2);That should increase the speed of your program a lot.
Good luck!
-
@Julius, yes, that, or a parametrized query like mirce.vladimirov showed.
-
I'm not a PostgreSQL user, Firebird and MSSQL is my daily job.
With Firebird best performance code is:
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
-
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
-
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?
-
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 :
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 :
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.
-
@mirce: yes, obviously running set-based SQL statements is gernally much faster than loops.
Unfortunately, sometimes you cannot avoid running loops in code.
-
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?
-
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 (http://www.pgadmin.org/docs/1.8/index.html)
"Connection to PostgreSQL is made using the native libpq library."
-
Try: http://sourceforge.net/projects/epgsql/
I used SQLdb, so you can see the code and maybe get some ideas...
Regards
-
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.
-
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:
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
-
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 :
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 :
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
-
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?
-
Instead of using SELECT you can try the sequence functions.
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.
-
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 :
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 :
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
-
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.
-
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
-
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
-
I think I am going to use the low level posgres unit for all inserts and updates. Doesnt seem to be complicated at all.
-
I was able to quickly hack up a unit that does updates to my server very fast. Here is the code
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?
-
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.