#1 2020-05-17 15:30:04

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Performance tests are not comparable

Hello Arnaud, just a note from me:
In current state of mORMot am not able to compare SynDBOracle vs. Zeos, same to SynDBPostgreSQL vs. Zeos on my system (Delphi(D7..XE13.3+Windows).
SynDBOracle returns fails,  SynDBPostgreSQL crashs with the batch inserts of timestamp fields. Could plz check it?

Michael

Last edited by EgonHugeist (2020-05-17 15:57:58)

Offline

#2 2020-05-17 18:24:44

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Performance tests are not comparable

About SynDBPOstgres timestamp insertion (I not test datetime in hi-level case):
TSQLDBStatementWithParams.BindArrayDateTime use strict ISO8601 format with T as time separator, but Postgres expects space instead of T in ISO8601 expanded format.

@ab - most likely we need a dateTimeFirstChar: AnsiChar = 'T' flag in ConnectionProperties, override it for Postgres into space and use everywere in SynDB.pas

DateTimeToIso8601Text(.., Connection.Properties.dateTimeFirstChar)

instead of

DateTimeToIso8601Text(..)

Offline

#3 2020-05-17 18:27:09

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Performance tests are not comparable

@EgonHugeist - what program you use to compare? As far as I know we do not touch a SynDBOracle for a long time, so this is strange what SynDBOracle returns fails....

Offline

#4 2020-05-18 04:52:17

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

@Pavel,

i'm using Synopse\SQLite3\Samples\15 - External DB performance\PerfMain.pas from trunk.
According oracle: I get

Oracle read failure: Value.Amount=(i+1)*0.01 0<>0.02  (D:\Synopse\SQLite3\Samples\15 - External DB performance\PerfMain.pas, Line 513)

According postgres: It's about the the behavior of DateTimeToIso8601Text, sometimes the datepart is not written (if Trunc() returns zero) and sometimes the timepart is not written (if Frac() returns a zero value). Postgres expects consistent values (just the second fractional part might be vary withing microsecond precision range). And of course the 'T' delimiter is desturbing pg too.

Offline

#5 2020-05-29 15:48:16

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

Hi Arnaud,
ping.., could you please have a look to my reported regressions? IIRC the oci issue did start with introducing the new float-conversion.
btw: 32-Bit delphi is used, dosn't matter which one. All of them do fail on my side. Just get it running would be nice, it dosn't matter about results.
Michael

Offline

#6 2020-05-29 16:10:48

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

@Michael
Is it still the case?
https://synopse.info/fossil/info/7cedb85058e010d1 tried to restore the previous float conversion.

Offline

#7 2020-05-30 04:22:22

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

@Arnaud, yup the oracle tests are passing now, thank you.
Hope you find time to fix the postgres batches too? You just need to use full timestamp strings using space instead of the 'T' delimiter. That would fix the batches for timestamp fields (but it would not work for time or date fields sad ).

Last edited by EgonHugeist (2020-05-30 04:23:10)

Offline

#8 2020-05-30 14:00:20

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

Offline

#9 2020-06-09 04:17:04

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

@Arnaud

nope that's not working with your tests. If i do it like this:

Index: SynDBPostgres.pas
===================================================================
--- SynDBPostgres.pas	(revision 9453)
+++ SynDBPostgres.pas	(working copy)
@@ -779,9 +779,10 @@
 
 procedure TSQLDBPostgresStatement.ExecutePrepared;
 var
-  i: PtrInt;
+  i, N: PtrInt;
   p: PSQLDBParam;
   c: TSQLDBPostgresConnection;
+  pStart, pEnd: PUTF8Char;
 begin
   SQLLogBegin(sllSQL);
   if fParsedSQL = '' then
@@ -799,10 +800,36 @@
     p := @fParams[i];
     if p^.VArray <> nil then
     begin
-      if not (p^.VType in [ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8]) then
-        raise ESQLDBPostgres.CreateUTF8('%.ExecutePrepared: Invalid array type % ' +
-          'on bound parameter #%', [Self, ToText(p^.VType)^, i]);
-      p^.VData := BoundArrayToJSONArray(p^.VArray);
+      if p^.VType = ftDate then begin
+        N := Length(p^.VArray);
+        SetLength(p^.VData, N*26+1);
+        PStart := Pointer(p^.VData);
+        PStart^ := '{';
+        PEnd := PStart + 1;
+        for N := 0 to N-1 do
+          if P^.VArray[N] = 'null' then begin
+            PCardinal(PEnd)^ := PCardinal(P^.VArray[N])^;
+            (PEnd+4)^ := ',';
+            Inc(PEnd, 5);
+          end else begin
+            PEnd^ := '"';
+            PDateTime(@p^.VInt64)^ := Iso8601ToDateTimePUTF8Char(
+                    PUTF8Char(pointer(P^.VArray[N]))+1, Length(P^.VArray[N])-2);
+            DateToIso8601PChar(PDateTime(@p^.VInt64)^,PEnd+1,true);
+            inc(PEnd,11);
+            TimeToIso8601PChar(PDateTime(@p^.VInt64)^,PEnd,true,' ',True);
+            (PEnd+13)^ := '"';
+            (PEnd+14)^ := ',';
+            Inc(PEnd, 15);
+          end;
+        (PEnd-1)^ := '}'; //replace last comma and close the json obj
+        SetLength(p^.VData, PEnd-PStart);
+      end else begin
+        if not (p^.VType in [ftInt64, ftDouble, ftCurrency, ftUTF8]) then
+          raise ESQLDBPostgres.CreateUTF8('%.ExecutePrepared: Invalid array type % ' +
+            'on bound parameter #%', [Self, ToText(p^.VType)^, i]);
+        p^.VData := BoundArrayToJSONArray(p^.VArray);
+      end;
     end
     else
     begin

then it works. Means i convert the inconsistend array items into it's TDateTime value, and write a full timestamp string..
@pavel feel free to test or implement it different.. (plz think about time/date oid's the patch would break compatibility for such oid's)

Last edited by EgonHugeist (2020-06-10 05:35:46)

Offline

#10 2020-07-30 04:30:20

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

Ping..

Offline

#11 2020-07-30 07:36:53

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

TSQLDBConnectionProperties.DateTimeFirstChar should be equal to ' ' for PostgreSQL so the date/time in the array should already be correct.
TSQLDBStatementWithParams.BindArray() should take care of it.
Perhaps https://synopse.info/fossil/info/7e01c6b597 may help.

Can you see what is wrong?

Offline

#12 2020-07-30 07:58:13

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

@ab see attached patch above.

The proplem is how BoundArrayToJSONArray works with the timestamps. Postgres simply raises and error if no full timestamp string is send.
As i said BoundArrayToJSONArray simply works wrong for postgres:

EgonHugeist wrote:

It's about the the behavior of DateTimeToIso8601Text, sometimes the datepart is not written (if Trunc() returns zero) and sometimes the timepart is not written (if Frac() returns a zero value). Postgres expects consistent values (just the second fractional part might be vary withing microsecond precision range). And of course the 'T' delimiter is desturbing pg too.

the more if the value is zero just the quotes are written. It's about this inconsistency. I never did understand why DateTimeToIso8601Text is working that way, propably because your framework is not able to differentiate Time/Date/DateTime types, is guess.

Last edited by EgonHugeist (2020-07-30 09:43:28)

Offline

#13 2020-07-30 08:14:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

DateTimeToIso8601Text() was JSON focused, not SQL focused.
So it tried to minimize the amount of text written.

Make sense now!
I will look into it.

Offline

#14 2020-07-30 10:24:07

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

I refactored the date/time process.
Check https://synopse.info/fossil/info/0b7c5ed5b1

Offline

#15 2020-07-30 16:07:51

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

@ab thanks for the affort. What can i say, i give up.
Nope it still doesn't work. Still the same issue i reported about postgresql. Did you ever test the patch using your own performance tests?

PS:

function TTimeLogBits.FullText(Expanded: boolean; FirstTimeChar,QuotedChar: AnsiChar): RawUTF8;
var tmp: array[0..31] of AnsiChar;
begin
  if Value=0 then
    if QuotedChar<>#0 then begin
      FastSetString(result,nil,2);
      result[1] := QuotedChar;
      result[2] := QuotedChar;
    end else
      result := '' else
    FastSetString(result,@tmp,FullText(tmp,Expanded,FirstTimeChar,QuotedChar)-@tmp);
end;

still returs an empty quoted string for a zero value whereas the pascal integral date is always: Year: 1899; Month: 12; Day: 30. It may be different to the SQL servers you're connection against- JFYI.

Last edited by EgonHugeist (2020-07-31 06:38:01)

Offline

#16 2020-07-31 10:04:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

I have fixed FullText.
https://synopse.info/fossil/info/40466c1e3d060d50

I will still continue looking into it.

Offline

#17 2020-07-31 15:56:45

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

After updating to latest trunk, i have an internal Compiler error: URW1136 in SynDB.pas now. Current compiler is an old Delphi XE (code is better than 10.3 yet). Ideas about? Do you support Delphi in future? I'll check which commit did kill the compiler next days. Sadly there is just a useless git hash and no revision number available..

Last edited by EgonHugeist (2020-07-31 15:59:04)

Offline

#18 2020-07-31 16:14:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Performance tests are not comparable

It was an inlining bug of Delphi 2010 and later.
Should be fixed by https://synopse.info/fossil/info/80cad9a07f

There is a revision number for each commit in https://github.com/synopse/mORMot/blob/ … Commit.inc

Offline

#19 2020-07-31 16:52:08

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

You're fast! I lke that! :kiss:
According the toppic: the returned and character fixes Postgres error is (German):

"Name    Wert
errMsg    'FEHLER:  Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »0000-01-01 00:00:00«'#$A

Sadly SynDbPostgres is'nt able to transalate the Exception to a native OS errer. However (i'm feeling like users who are asking me!) the reported issue remains, Arnaud. Plz consider using you own tests including Pavel's SynDbPostgres. Btw. repeating the "read all rows"[virtual/native] reading test,  10x, making an avg of 7... who will win the performance challange?
Regards, Michael

Last edited by EgonHugeist (2020-07-31 17:04:46)

Offline

#20 2020-09-13 14:55:22

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

Ping, Ping..

Offline

#21 2020-09-13 20:15:39

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Performance tests are not comparable

Michael, the problem is only with TDateTime=0 ?
BTW by default postgres error messages language depends on lc_locale parameter in postgres.conf (sets to OS locale during posrgres setup) Also can be changed for connection using
SET lc_messages TO 'en_US.UTF-8';

Offline

#22 2020-09-15 18:24:55

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

mpv wrote:

Michael, the problem is only with TDateTime=0 ?

Yet, yes for sure. The test (yet) don't pass and get's abortet, except i apply my patch as provided. Propaply Armaud has a nother change in mind, but i would be lucky to get his test running somehow on different instances and with the current expected values..

mpv wrote:

BTW by default postgres error messages language depends on lc_locale parameter in postgres.conf (sets to OS locale during posrgres setup) Also can be changed for connection using
SET lc_messages TO 'en_US.UTF-8';

Yup i know that, however, that's not my main goal here, just try to run AB's tests.

Offline

#23 2020-10-28 05:27:12

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Performance tests are not comparable

ping, ping, ping..

Offline

#24 2020-10-29 20:07:31

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Performance tests are not comparable

I verify a current trunc. The following minimal reproducible example works as expected. Michael, I think you use BindArray instead of BindArrayDateTime

program pg_date_bind;
uses
  {$i SynDprUses}
  SysUtils,SynCommons,SynDB,SynDBPostgres;

var
  cPool: TSQLDBConnectionPropertiesThreadSafe;
  conn: TSQLDBConnection;
  Query: ISQLDBStatement;
  json: RawUTF8;
  DT1, DT2: TDateTime;
begin
  cPool := TSQLDBPostgresConnectionProperties.Create('', 'postgresql://localhost:5432/postgres', 'benchmarkdbuse', 'benchmarkdbuser');
  conn := cPool.ThreadSafeConnection;
  query := conn.NewStatementPrepared('select unnest(?::DATE[]) as a , unnest(?::TIMESTAMP[]) as b', true);
  DT1 := 0; DT2 := Now;
  query.BindArrayDateTime(1, [DT1, DT2]);
  query.BindArrayDateTime(2, [DT1, DT2]);
  query.ExecutePrepared;
  json := query.FetchAllAsJSON(true);
  writeln(json); // output [{"a":"1899-12-30","b":"1899-12-30T00:00:00"},{"a":"2020-10-29","b":"2020-10-29T22:04:19"}]
  query := nil;
  cPool.Free;
  exit;
end.

Last edited by mpv (2020-10-30 08:29:56)

Offline

Board footer

Powered by FluxBB