Delete retrieve all records ids

macfly · 2018-07-01 02:04:25

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)

ab · 2018-07-01 18:46:43

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.

macfly · 2018-07-01 21:57:51

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)

macfly · 2018-07-02 01:25:49

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)

ab · 2018-07-02 20:32:14

Use at least a parameter:

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

macfly · 2018-07-02 20:43:03

Thanks

mORMot Open Source

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

Delete retrieve all records ids

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

Re: Delete retrieve all records ids

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

Re: Delete retrieve all records ids

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

Re: Delete retrieve all records ids

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

Re: Delete retrieve all records ids

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

Re: Delete retrieve all records ids

Board footer