Inserting rows via TSQLScript is getting slower and slower
lazarus / 16-Databases

----------------------------------------------------------------------
antispam88 · 2019-03-05 21:49

Hi,

we are testing timescaledb for our application.
One step is to check insert speed when data table has 100M+ rows.
But while testing I noticed that performance decreased shortly after
start.
In comparison to that I did the same test with the components of
devart without loss of velocity.

Here is my code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE public.measurementdata(    time timestamp without time zone NOT
NULL,    channel integer NOT NULL,    val real NOT NULL,    CONSTRAINT
measurementdata_pkey PRIMARY KEY (time, channel))WITH (    OIDS =
FALSE)TABLESPACE pg_default; var  PQConnection1: TPQConnection; 
SQLScript1: TSQLScript;  SQLTransaction1: TSQLTransaction;  Label1:
TLabel;  curDate: TDateTime;  dateStr: string;  i, j: string; 
startTc, endTc: int64;begin  curDate := EncodeDate(2010, 1, 1); 
PQConnection1.Open;  for i := 0 to 999999 do  begin   
DateTimeToString(dateStr, 'yyyy-mm-dd hh:nn:ss', curDate);   
SQLScript1.Script.Clear;    SQLScript1.Script.Add('INSERT INTO
measurementdata VALUES ');    for i := 0 to 98 do    begin     
SQLScript1.Script.Add(Format('(''%s'', %d, %f),', [dateStr, j, 1])); 
 end;    SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr,
99, 1]));    startTc := GetTickCount64;   
SQLTransaction1.StartTransaction;    SQLScript1.Execute;   
SQLTransaction1.Commit;    endTc := GetTickCount64;    if
SecondOfTheMinute(curDate) mod 60 = 0 then    begin     
Label1.Caption := IntToStr(endTc - startTc);     
Application.ProcessMessages;    end;    curDate :=
IncSecond(curDate);  end;end;
My system:
Win 10 Pro x64
Lazarus 1.8.4 x86_64-win64-win32/win64
FPC 3.0.4

Does someone have same experience, or any advice?

Thank you
antispam88

----------------------------------------------------------------------
valdir.marcos · 2019-03-06 00:16

Quote from: antispam88 on March 05, 2019, 10:49:15 pmHi,

we are testing timescaledb for our application.
One step is to check insert speed when data table has 100M+ rows.
But while testing I noticed that performance decreased shortly after
start.
In comparison to that I did the same test with the components of
devart without loss of velocity.

Here is my code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE public.measurementdata(    time timestamp without time zone NOT
NULL,    channel integer NOT NULL,    val real NOT NULL,    CONSTRAINT
measurementdata_pkey PRIMARY KEY (time, channel))WITH (    OIDS =
FALSE)TABLESPACE pg_default; var  PQConnection1: TPQConnection; 
SQLScript1: TSQLScript;  SQLTransaction1: TSQLTransaction;  Label1:
TLabel;  curDate: TDateTime;  dateStr: string;  i, j: string; 
startTc, endTc: int64;begin  curDate := EncodeDate(2010, 1, 1); 
PQConnection1.Open;  for i := 0 to 999999 do  begin   
DateTimeToString(dateStr, 'yyyy-mm-dd hh:nn:ss', curDate);   
SQLScript1.Script.Clear;    SQLScript1.Script.Add('INSERT INTO
measurementdata VALUES ');    for i := 0 to 98 do    begin     
SQLScript1.Script.Add(Format('(''%s'', %d, %f),', [dateStr, j, 1])); 
 end;    SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr,
99, 1]));    startTc := GetTickCount64;   
SQLTransaction1.StartTransaction;    SQLScript1.Execute;   
SQLTransaction1.Commit;    endTc := GetTickCount64;    if
SecondOfTheMinute(curDate) mod 60 = 0 then    begin     
Label1.Caption := IntToStr(endTc - startTc);     
Application.ProcessMessages;    end;    curDate :=
IncSecond(curDate);  end;end;
My system:
Win 10 Pro x64
Lazarus 1.8.4 x86_64-win64-win32/win64
FPC 3.0.4

Does someone have same experience, or any advice?

Thank you
antispam88Try to increase the number of inserts among commits from 98
to 1000 or to 10000, etc:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     for i
:= 0 to 98 do[/codel]

----------------------------------------------------------------------
antispam88 · 2019-03-06 08:46

I know, that the code is not optimized and the throughput could be
higher when increasing rows per insert statement.
But my concern here is, that executing the first insert lasts 40 ms
but the 500th insert lasts 600 ms.

----------------------------------------------------------------------
rvk · 2019-03-06 09:38

Does that also happen when you COMMIT; in the script itself?
Add

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99,
1]));SQLScript1.Script.Add('COMMIT;');startTc := GetTickCount64;//
SQLTransaction1.StartTransaction; // not sure if this is needed, auto-
start?SQLScript1.Execute;// SQLTransaction1.Commit; // not sure if you
need to commit if already done in script
Otherwise try to do it with TSQLQuery with the individual INSERTs to
see if that makes any difference.

I take it you have set AutoCommit to false (otherwise every statement
is committed).

----------------------------------------------------------------------
valdir.marcos · 2019-03-06 18:03

Quote from: antispam88 on March 06, 2019, 09:46:39 amI know, that the
code is not optimized and the throughput could be higher when
increasing rows per insert statement.
But my concern here is, that executing the first insert lasts 40 ms
but the 500th insert lasts 600 ms.Prepare shell scripts and repeat
tests on server console.
If bad performance over time persists, most probably server hardware
is bad dimensioned, or server is misconfigured, or RDBMS is
misconfigured.
If not, test those shell scripts on your desktop to check some
problems on network or desktop itself.
If not, test those scripts on a database admin tool and compare time
frames with Lazarus' SQLdb.

----------------------------------------------------------------------
valdir.marcos · 2019-03-06 18:15

Quote from: antispam88 on March 06, 2019, 09:46:39 amI know, that the
code is not optimized and the throughput could be higher when
increasing rows per insert statement.
But my concern here is, that executing the first insert lasts 40 ms
but the 500th insert lasts 600 ms.Every RDBMS has its own way to
improve performance on load large amount of data.
You should talk to your SysAdmin and DBA. Those professionals will
help you out to fill in the gaps.

Just as an example:
https://www.postgresql.org/docs/9.2/populate.html
https://www.citusdata.com/blog/2017/11/08/faster-bulk-loading-in-
postgresql-with-copy/
https://stackoverflow.com/questions/3437398/how-to-import-huge-chunks-
of-data-to-postgresql
https://pgxn.org/dist/pg_bulkload/3.1.8/doc/pg_bulkload.html

----------------------------------------------------------------------
antispam88 · 2019-03-10 22:03

Quote from: rvk on March 06, 2019, 10:38:02 amDoes that also happen
when you COMMIT; in the script itself?
Add

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99,
1]));SQLScript1.Script.Add('COMMIT;');startTc := GetTickCount64;//
SQLTransaction1.StartTransaction; // not sure if this is needed, auto-
start?SQLScript1.Execute;// SQLTransaction1.Commit; // not sure if you
need to commit if already done in script
Otherwise try to do it with TSQLQuery with the individual INSERTs to
see if that makes any difference.

I take it you have set AutoCommit to false (otherwise every statement
is committed).


I also tested it also with "COMMIT;" but without a real performance
change.
I will do a test with TSQLQuery ...

Quote from: valdir.marcos on March 06, 2019, 07:15:28 pmQuote from:
antispam88 on March 06, 2019, 09:46:39 amI know, that the code is not
optimized and the throughput could be higher when increasing rows per
insert statement.
But my concern here is, that executing the first insert lasts 40 ms
but the 500th insert lasts 600 ms.Every RDBMS has its own way to
improve performance on load large amount of data.
You should talk to your SysAdmin and DBA. Those professionals will
help you out to fill in the gaps.

Just as an example:
https://www.postgresql.org/docs/9.2/populate.html
https://www.citusdata.com/blog/2017/11/08/faster-bulk-loading-in-
postgresql-with-copy/
https://stackoverflow.com/questions/3437398/how-to-import-huge-chunks-
of-data-to-postgresql
https://pgxn.org/dist/pg_bulkload/3.1.8/doc/pg_bulkload.html

As I noticed in the first post, just using the components of devart
solved the problem (same query statements).
But they are not free to use...

----------------------------------------------------------------------
rvk · 2019-03-10 22:10

Quote from: antispam88 on March 10, 2019, 11:03:50 pmAs I noticed in
the first post, just using the components of devart solved the problem
(same query statements).
If it is the fault of TSQLScript it should be investigated further.
It could help if you create a small selfcontained sample project which
demonstrates the problem.

----------------------------------------------------------------------
antispam88 · 2019-03-13 01:05

Hi,
I wrote an example project.
TSQLScript increases right after the start the execution duration.
But with TSQLQuery (no need to use devart components) the execution
duration is constant.
The zip archive also contains the database script (perftest.sql).
Best regards,
antispam88

----------------------------------------------------------------------
rvk · 2019-03-13 09:58

I don't have postgresql installed so I changed the connector to a
TSQLConnector and tried it with my Firebird engine.
I needed to adjust the scriptbuild because Firebird couldn't handle
multiple VALUES-records in one statement.

But... the TSQLScript also runs slower and slower with Firebird.

I changed the buttonclick so the TSQLScript is created fresh each time
and then the interval seems stable.
So it's defenitly something in TSQLScript that's hanging in there
after execution.

So this works better:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var  curDate: TDateTime; 
dateStr: string;  startTc: int64;  SQLScript2: TSQLScript;begin 
StartTest;  try    curDate := EncodeDate(2010, 1, 1);    while
(curDate < EncodeDate(2010, 5, 1)) and run do    begin      
SQLScript2 := TSQLScript.Create(nil);      try       
SQLScript2.DataBase := SQLConnector1;        SQLScript2.Transaction :=
SQLTransaction1;        SQLScript2.AutoCommit := False;        
BuildStatement(curDate, dateStr, SQLScript2.Script, startTc);       
SQLScript2.Execute;        LogTime(curDate, dateStr, startTc);      
finally        SQLScript2.Free;      end;     end;  finally   
SQLConnector1.Close(True);  end;end;
When I get some time I will investigate further.

----------------------------------------------------------------------
rvk · 2019-03-13 10:32

I found the problem !!!

I tested with a Showmessage(SQLScript2.Directives.Text); in the loop,
that happened to the Directives stringlist. And it seems it doesn't
get cleared and grows bigger and bigger.
Code: [Select]SET TERM
COMMIT WORK
COMMIT RETAIN
COMMIT
#IFDEF
#IFNDEF
#ELSE
#ENDIF
#DEFINE
#UNDEF
#UNDEFINE
SET TERM
COMMIT WORK
COMMIT RETAIN
COMMIT
#IFDEF
#IFNDEF
#ELSE
#ENDIF
#DEFINE
#UNDEF
#UNDEFINE
SET TERM
COMMIT WORK
COMMIT RETAIN
COMMIT
#IFDEF
#IFNDEF
#ELSE
#ENDIF
#DEFINE
#UNDEF
#UNDEFINE
So if you put a SQLScript2.Directives.Clear before the execute it will
run fast again (without the need for recreation).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
curDate := EncodeDate(2010, 1, 1);      while (curDate <
EncodeDate(2010, 5, 1)) and run do      begin        //
Showmessage(SQLScript2.Directives.Text);       
SQLScript2.Directives.Clear;        BuildStatement(curDate, dateStr,
SQLScript2.Script, startTc);        SQLScript2.Execute;       
LogTime(curDate, dateStr, startTc);      end;
I'll submit a bugreport. (done
https://bugs.freepascal.org/view.php?id=35218)

@antispam88, For now you can call the .Directives.Clear yourself in
code.
The fix will only be in newer versions (and I saw you still worked
with 1.8.4).

----------------------------------------------------------------------
antispam88 · 2019-03-13 16:32

@rvk: thank you very much for your effort. Good to hear that there is
a workaround and an upcoming solution.
We have just changed to 2.0.0. Will the fix in 2.0.x or in trunk?

And for now:
Is there a drawback if I use TSQLQuery instead of TSQLScript
(performance or something else)?

Best regards,
antispam88

----------------------------------------------------------------------
rvk · 2019-03-13 16:36

Quote from: antispam88 on March 13, 2019, 05:32:04 pmWe have just
changed to 2.0.0. Will the fix in 2.0.x or in trunk?
It will at least be in trunk. Maybe it will also be applied to fixes
3.2 for the next FPC release.
Lazarus is somewhat behind so I expect it will be a while before it is
in the standard release.

QuoteIs there a drawback if I use TSQLQuery instead of TSQLScript
(performance or something else)?No, TSQLScript also uses TSQLQuery
internally. It will split all the separate statements into chunks and
execute them (although your last example-project made INSERTs with one
statement).

So TSQLScript is just for convenience to execute multiple statements.

----------------------------------------------------------------------
rvk · 2019-03-27 14:49

Quote from: rvk on March 13, 2019, 11:32:53 amI'll submit a bugreport.
(done https://bugs.freepascal.org/view.php?id=35218)
Fixed in trunk rev. 41801 (3.3.1) for release target 3.2.0, by
Michael.

If you still work with an older version you can use the temp-fix
provided earlier by manually clearing the .Directives.