Performance tests are not comparable

EgonHugeist · 2020-05-17 15:30:04

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)

mpv · 2020-05-17 18:24:44

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

mpv · 2020-05-17 18:27:09

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

EgonHugeist · 2020-05-18 04:52:17

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

EgonHugeist · 2020-05-29 15:48:16

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

ab · 2020-05-29 16:10:48

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

EgonHugeist · 2020-05-30 04:22:22

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

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

ab · 2020-05-30 14:00:20

Please check https://synopse.info/fossil/info/f6ffae1346

EgonHugeist · 2020-06-09 04:17:04

@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)

EgonHugeist · 2020-07-30 04:30:20

Ping..

ab · 2020-07-30 07:36:53

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?

EgonHugeist · 2020-07-30 07:58:13

@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)

ab · 2020-07-30 08:14:47

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.

ab · 2020-07-30 10:24:07

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

EgonHugeist · 2020-07-30 16:07:51

@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)

ab · 2020-07-31 10:04:23

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

I will still continue looking into it.

EgonHugeist · 2020-07-31 15:56:45

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)

ab · 2020-07-31 16:14:56

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

EgonHugeist · 2020-07-31 16:52:08

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)

EgonHugeist · 2020-09-13 14:55:22

Ping, Ping..

mpv · 2020-09-13 20:15:39

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';

EgonHugeist · 2020-09-15 18:24:55

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.

EgonHugeist · 2020-10-28 05:27:12

ping, ping, ping..

mpv · 2020-10-29 20:07:31

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)

mORMot Open Source

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

Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

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

Re: Performance tests are not comparable

Board footer