Pages: 1

#1 2019-11-02 03:12:53

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Batch insert with TDateTime property, ORM, Zeos-Firebird

I faced a problem with batch inserting records, which have TDateTime field (Timestamp in Firebird):

20191102 02555149 trace uNextDB.TNextDB(053c2a10) BatchSend {"TSQLRestBatch(04d0d840)":{"Count":2,"SizeBytes":1074}}
20191102 02555149  +    uNextDB.TNextDB(053c2a10).EngineBatchSend TSQLPerson inlen=1075
20191102 02555149  +    	SynDBZeos.TSQLDBZEOSStatement(04d125f0).Prepare
20191102 02555149  -    	00.001.458
20191102 02555149  +    	SynDBZeos.TSQLDBZEOSStatement(04d125f0).ExecutePrepared
20191102 02555149 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d125f0) execute block as begin INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (7039890342,'07039890342','TOMMY','','BERGENE',null,timestamp '1998-03-07',timestamp '1998-08-11',1,1,'','','','','0 ','1311','HØVIKODDEN','0219','BÆRUM','','',467,1,1,timestamp '2019-11-02T05:55:51'); INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (9060097620,'09060097620','LINDA','','ERIKSEN',timestamp '1999-08-10',timestamp '2000-06-09',timestamp '2000-08-15',1,1,'','','','','VEI 31 ','1311','HØVIKODDEN','0219','BÆRUM','0419','',null,1,0,timestamp '2019-11-02T05:55:51'); end
20191102 02555149 EXC   		EZIBSQLException ("SQL Error: Dynamic SQL Error; conversion error from string \"2019-11-02T05:55:51\"") [] at cc4cbc 
20191102 02555149  -    	00.002.038
20191102 02555149 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at db3db7 
20191102 02555149 trace 	uNextDB.TNextDB(053c2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson
20191102 02555149 warn  	uNextDB.TNextDB(053c2a10) {"EORMException(052e9af0)":{"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"}} -> PARTIAL rollback of latest auto-committed transaction data={"Person~~["automaticTransactionPerRow",10000,"POST~~{"RowID":7039890342,"SSN":"07039890342","GivenName":"TOMMY","MiddleName":"","Surname":"BERGENE","DateAdrFrom":null,"DateOfBirth":"1998-03-07","DateStatus":"1998-08-11","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"0 ","PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":1,"LastUpdate":"2019-11-02T05:55:51"},"POST~~{"RowID":9060097620,"SSN":"09060097620","GivenName":"LINDA","MiddleName":"","Surname":"ERIKSEN","DateAdrFrom":"1999-08-10","DateOfBirth":"2000-06-09","DateStatus":"2000-08-15","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"VEI 31 ","PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"0419","Constituency":"","BasicStatisticalUnit":null,"CurrentAddress":true,"Gender":0,"LastUpdate":"2019-11-02T05:55:51"}]}
20191102 02555149 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at db3db7

Without TDateTime property inserting works fine. Inserting without batch also works.

At the same time, batch updating with the same data also works fine:

20191102 03032817 trace uNextDB.TNextDB(054b2a10) BatchSend {"TSQLRestBatch(04d8d840)":{"Count":2,"SizeBytes":1072}}
20191102 03032817  +    uNextDB.TNextDB(054b2a10).EngineBatchSend TSQLPerson inlen=1073
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).Prepare
20191102 03032817  -    	00.001.419
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).ExecutePrepared
20191102 03032817 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d925f0) update PREG_PERSON set SSN='07039890342',GivenName='TOMMY',MiddleName='',Surname='BERGENE',DateAdrFrom=NULL,DateOfBirth='1998-03-07',DateStatus='1998-08-11',RegStatus=1,MaritalStatus=1,MothersNIN='',FathersNIN='',OldNIN='',SpouseNIN='',PostalAddress='0 ',PostalCode='1311',PostalPlace='HØVIKODDEN',St='0219',Municipality='BÆRUM',SchoolDistrict='',Constituency='',BasicStatisticalUnit=467,CurrentAddress=1,Gender=1,LastUpdate='2019-11-02 06:03:28' where ID=7039890342
20191102 03032817  -    	00.002.038
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).ExecutePrepared
20191102 03032817 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d925f0) update PREG_PERSON set SSN='09060097620',GivenName='LINDA',MiddleName='',Surname='ERIKSEN',DateAdrFrom='1999-08-10',DateOfBirth='2000-06-09',DateStatus='2000-08-15',RegStatus=1,MaritalStatus=1,MothersNIN='',FathersNIN='',OldNIN='',SpouseNIN='',PostalAddress='VEI 31 ',PostalCode='1311',PostalPlace='HØVIKODDEN',St='0219',Municipality='BÆRUM',SchoolDistrict='0419',Constituency='',BasicStatisticalUnit=NULL,CurrentAddress=1,Gender=0,LastUpdate='2019-11-02 06:03:28' where ID=9060097620
20191102 03032817  -    	00.000.329
20191102 03032817 trace 	uNextDB.TNextDB(054b2a10) EngineBatchSend json=1 KB add=0 update=2 delete=0 TSQLPerson

mORMot (fresh), Zeos (tried several latest versions from master branch), Firebird 2.5

Offline

#2 2019-11-02 08:06:59

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

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Normally, TSQLDBConnectionProperties.MultipleValuesInsert() is supposed to set the field type as TimeStamp in line 6157 of SynDB.pas.

Then, SynDBZeos should detect the ftDate field type, and call fStatement.SetTimestamp() as expected.

Could you please use the debugger to find out why it is not the case?

Offline

#3 2019-11-02 16:36:44

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Trying to debug for a rather long time. Will continue doing that, just decided to share some new information. Maybe you'll have some idea and I would be able to stop breaking my mind wink

TSQLDBConnectionProperties.MultipleValuesInsert() never starts in my project.
Instead of it TSQLRestStorageExternal.InternalBatchStop by running fProperties.OnBatchInsert() goes to TSQLDBConnectionProperties.MultipleValuesInsertFirebird() procedure.
As far as I could see, LastUpdate field type (FieldTypes array) is founded to be correct: ftDate. Although in FieldValues I have the same ISO datetime value (don't know so far, whether it is good or not).

One more thing, which confuses me much. Rarely, during testing I get another error in my batch sending (without any code changing):

20191102 16074830 trace uNextDB.TNextDB(050b2a10) BatchSend {"TSQLRestBatch(04e2dcc0)":{"Count":2,"SizeBytes":1077}}
20191102 16074830  +    uNextDB.TNextDB(050b2a10).EngineBatchSend TSQLPerson inlen=1078
20191102 16074830 EXC   	EORMException {"Message":"TSQLPropInfoList.IndexByNameOrExcept(SSN): unkwnown field in TSQLPerson"} [] at 8b9466 
20191102 16074830 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at dc19e7 
20191102 16074830 trace 	uNextDB.TNextDB(050b2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson
20191102 16074830 warn  	uNextDB.TNextDB(050b2a10) {"EORMException(04eb1260)":{"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"}} -> PARTIAL rollback of latest auto-committed transaction data={"Person~~["automaticTransactionPerRow",10000,"POST~~{"RowID":7039890342,"SSN":"07039890342","GivenName":"TOMMY","MiddleName":"","Surname":"BERGENE","DateAdrFrom":null,"DateOfBirth":"1998-03-07","DateStatus":"1998-08-11","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"0 ","PostalCodeInt":null,"PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":1,"LastUpdate":""},"POST~~{"RowID":9060097620,"SSN":"09060097620","GivenName":"LINDA","MiddleName":"","Surname":"ERIKSEN","DateAdrFrom":"1999-08-10","DateOfBirth":"2000-06-09","DateStatus":"2000-08-15","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"VEI 31 ","PostalCodeInt":1311,"PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"0419","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":0,"LastUpdate":""}]}
20191102 16074830 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at dc19e7

It is completely undetermined and can complain about other fields (Surname f.ex).

I guess I need to create a simpler testing project. Maybe there is some problem with my bigger project...

upd: using latest Zeos from master branch, now (587fa5e 2019-11-01)

Last edited by Vitaly (2019-11-02 16:51:36)

Offline

#4 2019-11-02 20:12:40

ttomas
Member
Registered: 2013-03-08
Posts: 135

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Datetime field in insert batch is: '2019-11-02T05:55:51'
In Update batch is: '2019-11-02 06:03:28' without T

Offline

#5 2019-11-02 21:32:22

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Yes, true. That's the point. As I supposing:
Batch is forming in JSON, where TDateTime value is in ISO format (with T). Then SQL should be transformed into external SQL and processed. But somehow it doesn't happen in my case with insert in a batch.
There are different ways of batch processing ways in TSQLRestServer.EngineBatchSend and other following methods and I hardly can understand the magic (so far at least). Will continue trying wink

Offline

#6 2019-11-02 22:15:00

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Using TSQLDBConnectionProperties.MultipleValuesInsert() works fine, btw.
Tested that way:

procedure TSQLDBConnectionProperties.MultipleValuesInsertFirebird(
  Props: TSQLDBConnectionProperties; const TableName: RawUTF8;
  const FieldNames: TRawUTF8DynArray; const FieldTypes: TSQLDBFieldTypeArray;
  RowCount: integer; const FieldValues: TRawUTF8DynArrayDynArray);
var W: TTextWriter;
    maxf,sqllenwitoutvalues,sqllen,r,f: Integer;
begin
  MultipleValuesInsert(Props, TableName, FieldNames, FieldTypes, RowCount, FieldValues);
  Exit;

  maxf := length(FieldNames);     // e.g. 2 fields
  if (Props=nil) or (FieldNames=nil) or (TableName='') or (length(FieldValues)<>maxf) or
...

Result:

20191102 22114918 trace uNextDB.TNextDB(053c2a10) BatchSend {"TSQLRestBatch(04cfcdc0)":{"Count":2,"SizeBytes":1115}}
20191102 22114918  +    uNextDB.TNextDB(053c2a10).EngineBatchSend TSQLPerson inlen=1116
20191102 22114918  +    	SynDBZeos.TSQLDBZEOSStatement(04d02a10).Prepare
20191102 22114918  -    	00.001.669
20191102 22114918  +    	SynDBZeos.TSQLDBZEOSStatement(04d02a10).ExecutePrepared
20191102 22114918 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d02a10) execute block( i1 BIGINT=7039890342,i2 VARCHAR(11) CHARACTER SET UTF8='07039890342',i3 VARCHAR(5) CHARACTER SET UTF8='TOMMY',i4 VARCHAR(1) CHARACTER SET UTF8='',i5 VARCHAR(7) CHARACTER SET UTF8='BERGENE',i6 CHAR(1)=NULL,i7 TIMESTAMP='1998-03-07',i8 TIMESTAMP='1998-08-11',i9 BIGINT=1,i10 BIGINT=1,i11 VARCHAR(1) CHARACTER SET UTF8='',i12 VARCHAR(1) CHARACTER SET UTF8='',i13 VARCHAR(1) CHARACTER SET UTF8='',i14 VARCHAR(1) CHARACTER SET UTF8='',i15 VARCHAR(2) CHARACTER SET UTF8='0 ',i16 CHAR(1)=NULL,i17 VARCHAR(4) CHARACTER SET UTF8='1311',i18 VARCHAR(11) CHARACTER SET UTF8='HØVIKODDEN',i19 VARCHAR(4) CHARACTER SET UTF8='0219',i20 VARCHAR(6) CHARACTER SET UTF8='BÆRUM',i21 VARCHAR(1) CHARACTER SET UTF8='',i22 VARCHAR(1) CHARACTER SET UTF8='',i23 BIGINT=467,i24 BIGINT=1,i25 BIGINT=1,i26 TIMESTAMP='2019-11-03 01:11:49' ,i27 BIGINT=9060097620,i28 VARCHAR(11) CHARACTER SET UTF8='09060097620',i29 VARCHAR(5) CHARACTER SET UTF8='LINDA',i30 VARCHAR(1) CHARACTER SET UTF8='',i31 VARCHAR(7) CHARACTER SET UTF8='ERIKSEN',i32 TIMESTAMP='1999-08-10',i33 TIMESTAMP='2000-06-09',i34 TIMESTAMP='2000-08-15',i35 BIGINT=1,i36 BIGINT=1,i37 VARCHAR(1) CHARACTER SET UTF8='',i38 VARCHAR(1) CHARACTER SET UTF8='',i39 VARCHAR(1) CHARACTER SET UTF8='',i40 VARCHAR(1) CHARACTER SET UTF8='',i41 VARCHAR(7) CHARACTER SET UTF8='VEI 31 ',i42 BIGINT=1311,i43 VARCHAR(4) CHARACTER SET UTF8='1311',i44 VARCHAR(11) CHARACTER SET UTF8='HØVIKODDEN',i45 VARCHAR(4) CHARACTER SET UTF8='0219',i46 VARCHAR(6) CHARACTER SET UTF8='BÆRUM',i47 VARCHAR(4) CHARACTER SET UTF8='0419',i48 VARCHAR(1) CHARACTER SET UTF8='',i49 BIGINT=467,i50 BIGINT=1,i51 BIGINT=0,i52 TIMESTAMP='2019-11-03 01:11:49' ) as begin INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCodeInt,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (:i1,:i2,:i3,:i4,:i5,:i6,:i7,:i8,:i9,:i10,:i11,:i12,:i13... (truncated) length=2568
20191102 22114919  -    	00.003.345
20191102 22114919 trace 	uNextDB.TNextDB(053c2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson

I know that it is not a decision, but the problem became a bit more localized smile

Offline

#7 2019-11-02 22:43:08

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

I ran several tests (for about 100K records) and I didn't find that MultipleValuesInsert is anyhow slower than MultipleValuesInsertFirebird for my project.
So if there an idea to purge MultipleValuesInsertFirebird will appear, I'm totally for it wink

Offline

#8 2019-11-05 13:32:29

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Arnaud, could you confirm or disprove, please, that there is a problem with TSQLDBConnectionProperties.MultipleValuesInsertFirebird() exist?
I would like to know, which way I should close this issue for my project. Thanks!

Offline

#9 2019-11-05 14:43:01

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

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

I guess the correct way to handle this issue is to fix MultipleValuesInsertFirebird().
Please check https://synopse.info/fossil/info/3b3546d959

I think TSQLDBConnectionProperties.MultipleValuesInsertFirebird() will be noticeably faster than TSQLDBConnectionProperties.MultipleValuesInsert() only if ZEOS is not used.
In fact, Zeos appears to apply the same process than MultipleValuesInsertFirebird on its side.

Offline

#10 2019-11-05 16:08:48

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Yes, it's working perfectly now! Thank you Very much! smile

Offline

#11 2019-11-06 08:54:45

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

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

I have added some fix to the function as https://synopse.info/fossil/info/960d54caea

Offline

#12 2019-11-06 15:02:14

Vitaly
Member
From: UAE
Registered: 2017-01-31
Posts: 168
Website

Re: Batch insert with TDateTime property, ORM, Zeos-Firebird

Ran several tests. Everything works well and fast! smile Thanks!

Offline

Pages: 1

Board footer

Powered by FluxBB