Recent

Author Topic: Inserting rows via TSQLScript is getting slower and slower  (Read 2317 times)

antispam88

  • New member
  • *
  • Posts: 47
Inserting rows via TSQLScript is getting slower and slower
« on: March 05, 2019, 10:49:15 pm »
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]
  1. CREATE TABLE public.measurementdata
  2. (
  3.     time timestamp without time zone NOT NULL,
  4.     channel integer NOT NULL,
  5.     val real NOT NULL,
  6.     CONSTRAINT measurementdata_pkey PRIMARY KEY (time, channel)
  7. )
  8. WITH (
  9.     OIDS = FALSE
  10. )
  11. TABLESPACE pg_default;
  12.  
  13. var
  14.   PQConnection1: TPQConnection;
  15.   SQLScript1: TSQLScript;
  16.   SQLTransaction1: TSQLTransaction;
  17.   Label1: TLabel;
  18.   curDate: TDateTime;
  19.   dateStr: string;
  20.   i, j: string;
  21.   startTc, endTc: int64;
  22. begin
  23.   curDate := EncodeDate(2010, 1, 1);
  24.   PQConnection1.Open;
  25.   for i := 0 to 999999 do
  26.   begin
  27.     DateTimeToString(dateStr, 'yyyy-mm-dd hh:nn:ss', curDate);
  28.     SQLScript1.Script.Clear;
  29.     SQLScript1.Script.Add('INSERT INTO measurementdata VALUES ');
  30.     for i := 0 to 98 do
  31.     begin
  32.       SQLScript1.Script.Add(Format('(''%s'', %d, %f),', [dateStr, j, 1]));
  33.     end;
  34.     SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99, 1]));
  35.     startTc := GetTickCount64;
  36.     SQLTransaction1.StartTransaction;
  37.     SQLScript1.Execute;
  38.     SQLTransaction1.Commit;
  39.     endTc := GetTickCount64;
  40.     if SecondOfTheMinute(curDate) mod 60 = 0 then
  41.     begin
  42.       Label1.Caption := IntToStr(endTc - startTc);
  43.       Application.ProcessMessages;
  44.     end;
  45.     curDate := IncSecond(curDate);
  46.   end;
  47. 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

  • Hero Member
  • *****
  • Posts: 735
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #1 on: March 06, 2019, 01:16:10 am »
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]
  1. CREATE TABLE public.measurementdata
  2. (
  3.     time timestamp without time zone NOT NULL,
  4.     channel integer NOT NULL,
  5.     val real NOT NULL,
  6.     CONSTRAINT measurementdata_pkey PRIMARY KEY (time, channel)
  7. )
  8. WITH (
  9.     OIDS = FALSE
  10. )
  11. TABLESPACE pg_default;
  12.  
  13. var
  14.   PQConnection1: TPQConnection;
  15.   SQLScript1: TSQLScript;
  16.   SQLTransaction1: TSQLTransaction;
  17.   Label1: TLabel;
  18.   curDate: TDateTime;
  19.   dateStr: string;
  20.   i, j: string;
  21.   startTc, endTc: int64;
  22. begin
  23.   curDate := EncodeDate(2010, 1, 1);
  24.   PQConnection1.Open;
  25.   for i := 0 to 999999 do
  26.   begin
  27.     DateTimeToString(dateStr, 'yyyy-mm-dd hh:nn:ss', curDate);
  28.     SQLScript1.Script.Clear;
  29.     SQLScript1.Script.Add('INSERT INTO measurementdata VALUES ');
  30.     for i := 0 to 98 do
  31.     begin
  32.       SQLScript1.Script.Add(Format('(''%s'', %d, %f),', [dateStr, j, 1]));
  33.     end;
  34.     SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99, 1]));
  35.     startTc := GetTickCount64;
  36.     SQLTransaction1.StartTransaction;
  37.     SQLScript1.Execute;
  38.     SQLTransaction1.Commit;
  39.     endTc := GetTickCount64;
  40.     if SecondOfTheMinute(curDate) mod 60 = 0 then
  41.     begin
  42.       Label1.Caption := IntToStr(endTc - startTc);
  43.       Application.ProcessMessages;
  44.     end;
  45.     curDate := IncSecond(curDate);
  46.   end;
  47. 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
Try to increase the number of inserts among commits from 98 to 1000 or to 10000, etc:
Code: Pascal  [Select]
  1.     for i := 0 to 98 do[/codel]

antispam88

  • New member
  • *
  • Posts: 47
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #2 on: March 06, 2019, 09:46:39 am »
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

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #3 on: March 06, 2019, 10:38:02 am »
Does that also happen when you COMMIT; in the script itself?
Add

Code: Pascal  [Select]
  1. SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99, 1]));
  2. SQLScript1.Script.Add('COMMIT;');
  3. startTc := GetTickCount64;
  4. // SQLTransaction1.StartTransaction; // not sure if this is needed, auto-start?
  5. SQLScript1.Execute;
  6. // 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).
« Last Edit: March 06, 2019, 10:44:07 am by rvk »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 735
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #4 on: March 06, 2019, 07:03:46 pm »
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.
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

  • Hero Member
  • *****
  • Posts: 735
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #5 on: March 06, 2019, 07:15:28 pm »
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.
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

  • New member
  • *
  • Posts: 47
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #6 on: March 10, 2019, 11:03:50 pm »
Does that also happen when you COMMIT; in the script itself?
Add

Code: Pascal  [Select]
  1. SQLScript1.Script.Add(Format('(''%s'', %d, %f);', [dateStr, 99, 1]));
  2. SQLScript1.Script.Add('COMMIT;');
  3. startTc := GetTickCount64;
  4. // SQLTransaction1.StartTransaction; // not sure if this is needed, auto-start?
  5. SQLScript1.Execute;
  6. // 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 ...

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.
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

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #7 on: March 10, 2019, 11:10:01 pm »
As 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

  • New member
  • *
  • Posts: 47
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #8 on: March 13, 2019, 02:05:26 am »
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

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #9 on: March 13, 2019, 10:58:28 am »
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]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   curDate: TDateTime;
  4.   dateStr: string;
  5.   startTc: int64;
  6.   SQLScript2: TSQLScript;
  7. begin
  8.   StartTest;
  9.   try
  10.     curDate := EncodeDate(2010, 1, 1);
  11.     while (curDate < EncodeDate(2010, 5, 1)) and run do
  12.     begin
  13.  
  14.       SQLScript2 := TSQLScript.Create(nil);
  15.       try
  16.         SQLScript2.DataBase := SQLConnector1;
  17.         SQLScript2.Transaction := SQLTransaction1;
  18.         SQLScript2.AutoCommit := False;
  19.  
  20.         BuildStatement(curDate, dateStr, SQLScript2.Script, startTc);
  21.         SQLScript2.Execute;
  22.         LogTime(curDate, dateStr, startTc);
  23.  
  24.       finally
  25.         SQLScript2.Free;
  26.       end;
  27.  
  28.     end;
  29.   finally
  30.     SQLConnector1.Close(True);
  31.   end;
  32. end;

When I get some time I will investigate further.

rvk

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #10 on: March 13, 2019, 11:32:53 am »
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]
  1.       curDate := EncodeDate(2010, 1, 1);
  2.       while (curDate < EncodeDate(2010, 5, 1)) and run do
  3.       begin
  4.         // Showmessage(SQLScript2.Directives.Text);
  5.         SQLScript2.Directives.Clear;
  6.         BuildStatement(curDate, dateStr, SQLScript2.Script, startTc);
  7.         SQLScript2.Execute;
  8.         LogTime(curDate, dateStr, startTc);
  9.       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).
« Last Edit: March 13, 2019, 11:54:37 am by rvk »

antispam88

  • New member
  • *
  • Posts: 47
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #11 on: March 13, 2019, 05:32:04 pm »
@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

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #12 on: March 13, 2019, 05:36:16 pm »
We 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.

Quote
Is 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

  • Hero Member
  • *****
  • Posts: 3745
Re: Inserting rows via TSQLScript is getting slower and slower
« Reply #13 on: March 27, 2019, 03:49:22 pm »
I'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.