#1 2018-07-01 02:04:25

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Delete retrieve all records ids

Hi guys,

I'm testing the TClassNameToBeDeletedID  feature and noticed in the logs that ORM performs an additional query of all associated IDs in the related table.

Is there a way to disable this behavior?

I tested with up to 10000 records and it returned all in a single query (SELECT ID FROM ...)

This not causes memory problems in a table with millions of records?

Last edited by macfly (2018-07-02 01:11:53)

Offline

#2 2018-07-01 18:46:43

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

Re: Delete retrieve all records ids

It will work with anykind of database (even NoSQL) with thousands of records.
For millions of records, don't use TClassNameToBeDeletedID but a trigger at the database level, or -even better- data sharding, i.e. no need to delete associated content since you would have everything in a single document/TSQLRecord.

Offline

#3 2018-07-01 21:57:51

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: Delete retrieve all records ids

I'm using sharding but not for everything.

For example, the Products table has only the product ID, the User, and a Data column with all product related data in a JSON structure.

But... It does not really matter if TClassNameToBeDeletedID is being used.

Apparently any deletion in a field other than the ID generates the select of all ids of the table.

  TSQLProduct = class(TSQLRecord)
  private
    FTest: Integer;
    FData: Variant;
    FCreatedAt: TCreateTime;
    FModifiedAt: TModTime;
  published
    property Test: Integer FTest write FTest;
    property Data: Variant read FData write FData;
    property CreatedAt: TCreateTime read FCreatedAt write FCreatedAt;
    property ModifiedAt: TModTime read FModifiedAt write FModifiedAt;
  end;
for I := 0 to 10 do begin
AProduct := TSQLProduct.Create;
AProduct.Test = 5;
...
AClient.Add(AProduct,...);
...
AProduct.Free;
end;
Client.Delete(AProduct, 'Test= ?',[5]);...

This deletion produces the Select query of All ids....

mORMotSQLite3.TSQLRestServerDB(01A1DF40) 214us returned 11 rows as 103 bytes SELECT RowID FROM Product WHERE Test = 5
SynSQLite3.TSQLDatabase(01ABA6D0) [{"ID":1},{"ID":2},{"ID":3},{"ID":4},{"ID":5},{"ID":6},{"ID":7},{"ID":8},{"ID":9},{"ID":10},{"ID":11}] 
mORMotSQLite3.TSQLRestServerDB(01A1DF40) 14.51ms  DELETE FROM Product WHERE Test = 5

Is this right?

Last edited by macfly (2018-07-02 00:53:31)

Offline

#4 2018-07-02 01:25:49

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: Delete retrieve all records ids

Well, this actually has no connection with TClassNameToBeDeletedID.

IDS are loaded for cache notification, check if RecordCanBeUpdated, etc.

Since I am using Services, I will implement deletion with lower level access in the database, to avoid this.

Server.DB.Execute (FormatUTF8 ('DELETE FROM % WHERE Test =%', [TSQLProduct.SQLTableName, 5]));

Last edited by macfly (2018-07-02 01:40:53)

Offline

#5 2018-07-02 20:32:14

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

Re: Delete retrieve all records ids

Use at least a parameter:

Server.DB.ExecuteFmt('DELETE FROM % WHERE Test =?', [TSQLProduct.SQLTableName], [5]); 

Offline

#6 2018-07-02 20:43:03

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: Delete retrieve all records ids

Thanks

Offline

Board footer

Powered by FluxBB