mORMot Open Source

jonsafi

Member

Registered: 2011-07-26

Posts: 58

mORMot2: Same SQL query runs considerably slower with Lazarus...

Hello,

The new mORMot2 runs an SQL query with an outer
join probably slightly faster than the earlier version,
just a few seconds for the following query that returns
an answer set of about 180 000 rows and 30 columns.

However, the very same query, when run with the same
code (in the same PC) over the same dataset using Lazarus 2.1 and FPC 3.3.1
takes one whole minute (!) to run...

It would be nice to make use of Lazarus when needed, but am puzzled here,
would greatly appreciate any pointers...
Sami

Var
    GG_Model: TOrmModel;
  GG_globalClient:  TRestClientDB;
  G_Database_FullPath:String;
  Ar_IndexesA,Ar_IndexesB:
Array [1..3] of RawUTF8;        
lang :Boolean;
data1    : TOrmTable; (* was TSQLTable *)

BEGIN
  G_Database_FullPath:= ....

  GG_Model := TOrmModel.Create(
       TOrmTbl1, TOrmTbl2],'root'); // w/o root slower ?

  GG_globalClient :=    TRestClientDB.Create(GG_Model,nil,
      G_Database_FullPath,TRestServerDB, false, '');

  GG_globalClient.Server.Server.CreateMissingTables;
Ar_IndexesA[1] := 'ACompPrimKey1'Ar_IndexesA[2] := 'ACompPrimKey2';
Ar_IndexesA[3] := 'ACompPrimKey3';

Ar_IndexesB[1] := 'BCompPrimKey1'Ar_IndexesB[2] := 'BCompPrimKey2';
Ar_IndexesB[3] := 'ABCompPrimKey3';

// using a composite primary key for both tables
  lang :=   TRestClientDB(GG_globalClient).Server.Server.CreateSQLMultiIndex(
             TOrmTbl1, Ar_IndexesB,True);
  lang :=   TRestClientDB(GG_globalClient).Server.Server.CreateSQLMultiIndex(
             TOrmTbl2, Ar_IndexesB,True);

sql_str :=  'SELECT * FROM Tbl1 T1 LEFT JOIN Tbl2 T2 WHERE T1.FieldX = T2.FieldX';
data1  := GG_globalClient.Orm.ExecuteList([],sql_str);  // very slow on Lazarus

END;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: mORMot2: Same SQL query runs considerably slower with Lazarus...

Some ideas...

Is the SQlite3 content file is the same: isn't an index missing?

Please try with FPC 3.2 stable branch.
But I don't think it is relevant here.

Perhaps the FPC memory manager is involved.
Try to use Win64 and mORMot 2 memory manager.

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: mORMot2: Same SQL query runs considerably slower with Lazarus...

Thanks for your help! Indeed, the index problem first came to mind,
but rechecked it and Delphi is pretty much using the same code,
with both tables having that 3-field composite index.

The Lazarus and the D2009 both read the same DB3 file.

Unless there is some mysterious error preventing the Lazarus application
from making use of the indexes..

The Lazarus application seems to go into intensive processing during the
query, so could also be the memory manager as you suggest, will try it out
and then finally FPC 3.2 if nothing else helps.

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: mORMot2: Same SQL query runs considerably slower with Lazarus...

Thanks for pointing me in the right direction, the culprit was
indeed here, the missing index name with Delphi did not give any problems.
But with Lazarus, apparently the multi-index was not being created.

However, the following code only works fast once, the 2nd time,
it acts as if there is no index, until I change again the name of the index.
Am I missing something, perhaps having been using CreateSQLMultiIndex  wrongly all along...?

  lang :=   TRestClientDB(GG_globalClient).Server.Server.CreateSQLMultiIndex(
             TOrmTbl1, Ar_IndexesB,True,'IndexName1'); // quick only if IndexName change for each run

P.S.
Tried using the memory manager mormot.core.fpcx64mm with FPC 3.3.1, it compiled,
but at run time got a mysterious segmentation fault.

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: mORMot2: Same SQL query runs considerably slower with Lazarus...

Did some further checking, and it would seem that on the Lazarus side, any  existing composite primary keys
are not found/are not used, hence the delay. Creating new composite keys each time produces a fast query though.
Noticed that there was long ago a bug in this issue regarding mormot (1) and
Delphi, could it be that the bug is now coming up with Lazarus..