Forum > Databases
Inserting rows via TSQLScript is getting slower and slower
antispam88:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
--- Quote from: antispam88 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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
--- End quote ---
Try to increase the number of inserts among commits from 98 to 1000 or to 10000, etc:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- for i := 0 to 98 do[/codel]
antispam88:
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:
Does that also happen when you COMMIT; in the script itself?
Add
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
--- Quote from: antispam88 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.
--- End quote ---
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.
Navigation
[0] Message Index
[#] Next page