mORMot Open Source

hieroly

Member

Registered: 2024-09-07

Posts: 3

How to efficiently obtain 'dataset' using internal SQLite3

Hello AB,

I've read many responses regarding whether to use SQL JOIN. You suggested using ORM concepts as much as possible and minimizing the use of relational database concepts. I've been striving to work in this manner in my code.

However, I've found that it's unavoidable to end up with one-to-many or one-to-one relationships, and there's no escaping the need to use reporting components like Fastreport, which require associating a Datasource or Dataset.

After reading numerous posts, I feel that using virtual tables could solve this issue, as they allow for the implementation of ORM while also providing the contiguous nature of databases.

constructor TEquipResourceDB.Create(const aDBFileName: TFileName);
var
  uriStr: string;
begin
  inherited Create;
  FOrmModel := CreateModel;

  uriStr := TSqlDBZeosConnectionProperties.URI('sqlite:', '', 'SQLite3.dll');
  FConnProps := TSqlDBZeosConnectionProperties.Create(uriStr, aDBFileName, '', '');

  VirtualTableExternalRegisterAll(FOrmModel, FConnProps, [regMapAutoKeywordFields]);
  FRestServer := TRestServerDB.Create(FOrmModel, SQLITE_MEMORY_DATABASE_NAME);

  // FRestServer := TRestServerDB.Create(FOrmModel, aDBFileName); //***

  FRestServer.Model.Owner := FRestServer;
  FRestServer.DB.Synchronous := smFull;

  FRestServer.DB.LockingMode := lmExclusive;
  FRestServer.Server.CreateMissingTables(0, [itoNoAutoCreateGroups, itoNoAutoCreateUsers]);
  FOrmServer := FRestServer.Server;  // IRestOrmServer
  // FHttpServer := TSQLHttpServer.Create('8080', [FOrmServer], '+', HTTP_DEFAULT_MODE, 5);
  FHttpServer := TSQLHttpServer.Create('8080', FRestServer);
  FHttpServer.AccessControlAllowOrigin := '*';

  FLoadEquipEvent := TSynBackgroundThreadEvent.Create(LoadEquipEvent, nil, 'Background_LoadEquipEvent');
end;

But based on your replies in the posts, it seems that this approach may not yield better performance.

Therefore, it would be ideal if using `TRestServerDB.Create(FOrmModel, aDBFileName)` directly could provide the connection properties of `TSqlDBConnectionProperties`. Unfortunately, in the ORM context, it equals nil if the table is internal to SQLite3.

How to efficiently obtain 'dataset' using internal SQLite3 through TRestServerDB‘？

Is there a possibility to achieve both efficiency and the capability to provide a Dataset?

Or, when a Dataset is required, is the only option to synthesize the necessary JSON through ORM and then temporarily convert it using `JsonToDataSet`?

Last edited by hieroly (2024-09-08 04:41:42)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: How to efficiently obtain 'dataset' using internal SQLite3

Hello, and welcome.

You are right: for complex hand-made SQL queries, the virtual tables will be involved, so there will be some performance lost.
Usually, this performance lost is small. For some very complex SQL, the SQLite3 virtual table engine may endup reading a lot more data than it should, because it lacks the knowledge of the database details (e.g. it may don't know that there is an index on some table).
So I always recommend to enable the logs, and look at the SQL log entries: you will see the SQlite3 virtual table Query Plan, and also the exact SQL statements executed by the end database, with the corresponding timing. It is very useful to identify performance penalties.

But my main reaction is that, since you are using hand-made SQL to ask the SQLite3, why don't just write hand-made SQL directly to the external DB?
Zeos should be able to make it work from several database engines.

The ORM will only return JSON, and you can create an UI TDataSet using mormot.db.rad.ui.orm.pas.
But if you use directly the external DB, via an hand-made SQL over TSqlDBConnectionProperties, you could return a JSON result but you could also create a TDataSet directly mapping the TSqlDBStatement result, via ToDataSet() from mormot.db.rad.ui.sql.pas.

Last advice: if you use JSON, ensure you emit it in the "non-expanded" format, which is shorter in memory, and faster to emit and parse.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: How to efficiently obtain 'dataset' using internal SQLite3

..., and there's no escaping the need to use reporting components like Fastreport, which require associating a Datasource or Dataset.

This is not absolutely necessary. I have written an article about it. Here is the announcement in this forum.

With best regards
Thomas

Junior/RO

Member

Registered: 2011-05-13

Posts: 210

Re: How to efficiently obtain 'dataset' using internal SQLite3

..., and there's no escaping the need to use reporting components like Fastreport, which require associating a Datasource or Dataset.

This is not absolutely necessary. I have written an article about it. Here is the announcement in this forum.

With best regards
Thomas

Can't find your article.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: How to efficiently obtain 'dataset' using internal SQLite3

Can't find your article.

You must follow the link in the announcement. I write my posts in Delphi-Praxis Forum.

With best regards
Thomas

hieroly

Member

Registered: 2024-09-07

Posts: 3

Re: How to efficiently obtain 'dataset' using internal SQLite3

Hello AB and TDO, thank you very much!

Weighing the development efficiency, I ultimately decided to implement it using SQL's join. Actually, at the beginning, I also planned to study methods like TOrmMany and FillMany, but didn't understand them.

function TEquipResourceDB.GetCheckResults(out aJsonResult: RawUtf8): integer;
var
  vSQL: TStringList;
begin
  Result := 0;
  aJsonResult := '[]';
  vSQL := TStringList.Create;
  try
    with vSql do
    begin
      add('SELECT t1.ID,');
      add('t1.IODEVICE,');
      add('t1.PrimaryIPAddress,');
      add('t1.PrimaryEquipmentAddress,');
      add('t1.CircuitName,');
      add('t1.CheckPoint,');
      add('t2.TestPassed,');
      add('t2.TestCount,');
      add('t2.Health');
      add('FROM EquipBase t1 JOIN EquipHealth t2 ON t1.ID = t2.EquipBaseID');
      add('LIMIT 2048');
    end;
    // FOrmServer: IRestOrm, saved when building TRestServerDB as FOrmServer := FRestServer.Server;
    aJsonResult := FOrmServer.ExecuteJson([TEquipBase, TEquipHealth], vSql.Text, False, @Result);
  finally
    vSql.Free;
  end;
end;

Considering the retrieval efficiency, the "InitializeTable" method is used to index the relevant fields.

class procedure TEquipHealth.InitializeTable(const Server :IRestOrmServer; const FieldName :RawUtf8;
  Options :TOrmInitializeTableOptions);
begin
  inherited InitializeTable(Server, FieldName, Options);

  if (FieldName = '') or (FieldName = 'EquipBaseID') then
    Server.CreateSQLIndex(TEquipHealth, 'EquipBaseID', False);
end; 

Regarding the question, why does the first parameter of `ExecuteJson` need to be associated with an ORM array like `[TEquipBase, TEquipHealth]`? Although I haven't figured this out yet, I still included the associated ORM in it.

Last edited by hieroly (2024-09-15 07:05:57)