mORMot Open Source

DonAlfredo

Member

Registered: 2023-10-31

Posts: 30

Slow step speed

I am encountering a strange speed problem when retrieving data from a table.
Below the table definition. BatteryData is a huge TPersistent (TCollection)  with lots of fields and data.

  TOrmRunData = class(TOrmCaseSensitive)
  published
    property ProductCode  : RawUTF8 index 50 read fProductCode write fProductCode;
    property BatteryData  : TBatteryDataCollection read fBatteryData write fBatteryData;
    property Version      : TRecordVersion read fVersion write fVersion;
  end;

If I perform a SELECT with fields ProductCode and Version, the results are slow (1000ms).
If I perform a SELECT with field ProductCode, the results are fast (20ms).

Now I copy the data into a table with the following definition.

  TOrmRunData = class(TOrmCaseSensitive)
  published
    property ProductCode   : RawUTF8 index 50 read fProductCode write fProductCode;
    property Version       : TRecordVersion read fVersion write fVersion;
    property BatteryData   : TBatteryDataCollection read fBatteryData write fBatteryData;
  end;

If I perform a SELECT with fields ProductCode and Version, the results are fast (20ms).

In both cases, I do NOT request the BatteryData. But any field below BatteryData will slow down the query.
If the same Query-SQL is used in an external app (sqlitebrowser), the results are fast on any table (23ms).

Any ideas ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,367

Website

Re: Slow step speed

Which DB engine are you using?

IMHO the field order should not matter.
Just copying the data may explain a better performance (less fragmentation)?
Did you check the indexes? Did you check the generated execution plan (in the logs when the DB SELECT statement is prepared)?

DonAlfredo

Member

Registered: 2023-10-31

Posts: 30

Re: Slow step speed

The database is a simple TRestServerDB.

  NewRestServerDB := TRestServerDB.CreateWithOwnModel([TOrmProduct,TOrmRunData], 'new.db3');
  NewRestServerDB.DB.Synchronous := smNormal;
  NewRestServerDB.DB.LockingMode := lmExclusive;
  NewRestServerDB.CreateMissingTables;
  NewRestServerDB.CreateSqlIndex(TOrmProduct,'ProductCode',true);
  NewRestServerDB.CreateSqlIndex(TOrmProduct,'Version',false);
  NewRestServerDB.CreateSqlMultiIndex(TOrmProduct,['ClientCode','Brand','Model','Batch'],false);
  NewRestServerDB.CreateSqlMultiIndex(TOrmRunData,['ProductCode','DischargeType','DischargeValue','DataInvalid','SampleNumber'],false);
  NewRestServerDB.CreateSqlIndex(TOrmRunData,'Version',false);

Nothing special AFAIK.

Did you check the generated execution plan

No. But the same SQL is used for both tables. Do you expect any changes ?

Just copying the data may explain a better performance (less fragmentation)

I did not know about this. I will look into this. That might be a possible cause, as fields have been added during the past few years.

DonAlfredo

Member

Registered: 2023-10-31

Posts: 30

Re: Slow step speed

I have defragmented both tables. Table with BatteryData in front of other fields still slower, but not as much as before (lookup now 100ms).
With both tables now defragmented, the field-position is still important: BatteryData in front = slow. This might just be a business-logic issue.

Last edited by DonAlfredo (2026-01-05 19:04:45)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,367

Website

Re: Slow step speed

From the SQLite3 point of view, data locality may have some performance impact, especially if BatteryData is huge, and split to another page, so accessing Version needs an additional read.

What you could try is to make a multi index on ProductCode + Version instead of only ProductCode.
It could be used for ProductCode only (as currently) but also in your specific case, when both fields are needed.
Performance could dramatically increase, with minimal storage inflation (since Version is an integer).
See https://sqlite.org/queryplanner.html#_m … mn_indices

Also check the generated indexes, because AFAIR the "Version" field has already its index generated by the ORM.

Hope it would give your battery project more power!

DonAlfredo

Member

Registered: 2023-10-31

Posts: 30

Re: Slow step speed

See https://sqlite.org/queryplanner.html#_m … mn_indices

Thanks for the advice and this link. Very helpful for now and other projects !!

For now, it seems that placing the huge data-collection-field as last field gives huge speed benefits.
I use 2 tables, one for speedy lookup of products and one that contains the huge test-data. A kind of master-detail.
And using the mORMot[2] for all of this makes database-programming a party on its own ... ;-)
Thanks for all !

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,367

Website

Re: Slow step speed

Perhaps a separated table with just ID + BinaryData could make it even more efficient.

But if it is fast enough yet, may be not worth it.

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 497

Re: Slow step speed

I have the same problem with mariadb as external database and 2-3 table with 2-7 fields, where one is just big.

DonAlfredo

Member

Registered: 2023-10-31

Posts: 30

Re: Slow step speed

I like the fact that the batterydata is json inside the database: readable, checkable, easy for future use.
Played a bit with the indexes with a small bit of improvement also.
For now its fast enough. Me happy.

zed

Member

Registered: 2015-02-26

Posts: 116

Re: Slow step speed

If the same Query-SQL is used in an external app (sqlitebrowser), the results are fast on any table (23ms).

A very interesting effect. What could explain this?