mORMot Open Source

sgavrilov

Member

Registered: 2019-05-24

Posts: 27

CreateAndFillPrepareJoined and MS SQL

Hello!

I am puzzled about the way the CreateAndFillPrepareJoined works.

I have 2 tables: TSEMD and TSEMDSigner. TSEMD has 2 references to the TSEMDSigner:

TSEMD = class(TOrmUnicodeNoCase)
...
PersSigner: TSEMDSigner;
OrgSigner: TSEMDSigner;
...
end;

Both tables are mapped to a MS SQL database using the OrmMapExternal.

When I use the CreateAndFillPrepareJoined to get the data, I get the expected results. However, it appears (according to the logs) that instead of a single query with JOINs, mORMot (or SQLite?) executes a simple main query on SEMD, then starts a loop and executes two queries on SEMDSigner (for PersSigner and OrgSigner) for each main record!

20260211 14190513  % DB        mormot.db.sql.oledb.TSqlDBOleDBStatement(01a772f0) Prepare t=12.84ms q=select Created,SEMDState,...,ID from dbo.SEMD
20260211 14190513  % SQL       mormot.db.sql.oledb.TSqlDBOleDBStatement(01a772f0) Execute t=15.66ms q=select Created,...,ID from dbo.SEMD
20260211 14190513  % SQL       mormot.orm.sql.TOrmVirtualTableCursorExternal(03a6c840) Search select Created,...,ID from dbo.SEMD

20260211 14190513  % DB        mormot.db.sql.oledb.TSqlDBOleDBStatement(01a77410) Prepare t=136us q=select UserEID,...,ID from dbo.SEMDSigner where ID=?
20260211 14190514  % SQL       mormot.db.sql.oledb.TSqlDBOleDBStatement(01a77410) Execute t=1.58ms q=select UserEID,...,ID from dbo.SEMDSigner where ID=0
20260211 14190514  % SQL       mormot.orm.sql.TOrmVirtualTableCursorExternal(03a6c820) Search select UserEID,...,ID from dbo.SEMDSigner where ID=?

20260211 14190514  % DB        mormot.db.sql.oledb.TSqlDBOleDBStatement(01a77410) Prepare t=55us q=select UserEID,...,ID from dbo.SEMDSigner where ID=?
20260211 14190514  % SQL       mormot.db.sql.oledb.TSqlDBOleDBStatement(01a77410) Execute t=1.18ms q=select UserEID,...,ID from dbo.SEMDSigner where ID=0
20260211 14190514  % SQL       mormot.orm.sql.TOrmVirtualTableCursorExternal(03a6c800) Search select UserEID,...,ID from dbo.SEMDSigner where ID=?
...

So, it appears that the JOINed query is emulated (with the corresponding time penalties)! Is this the expected behavior? What am I doing wrong?

Best regards,
Sergey

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,406

Website

Re: CreateAndFillPrepareJoined and MS SQL

It switched to SQLite3 virtual tables for this.

Too complex CreateAndFillPrepareJoined() are not supported.

Two seconds search:
https://synopse.info/forum/viewtopic.php?pid=39981

sgavrilov

Member

Registered: 2019-05-24

Posts: 27

Re: CreateAndFillPrepareJoined and MS SQL

Thank you for the hint!

In my opinion, my call to the CreateAndFillPrepareJoined was quite simple: a simple WHERE on the main table only and two JOINs to another table. So, I decided to dig deeper.

I have found that the mORMot generates the correct SQL statement. Since it references two tables, the field names are prefixed by the corresponding table names: SEMD.Created, SEMDSigner.UserEID, etc. I tried this statement (with minor changes) in the Microsoft SQL Server Management Studio and it worked correctly.

But later the TRestStorageExternal.DoAdaptSqlForEngineList is called at some point and it actually decides if the SQL statement is too complex. It calls the TSelectStatement.Create to parse the statement.

The TSelectStatement.Create looks for 'SELECT' (which is there) and then it calls the GetNextSelectField to look for field names. And the GetNextSelectField calls the GetPropIndex, which in turn calls the GetNextFieldProp. And here is the problem: the GetNextFieldProp does not recognize field names prefixed by table names: it does not handle dots!

So, I suspect that either the GetNextFieldProp should be modified to handle compound names or a separate function should be created and used for this. Most likely, the latter is safer.

Am I thinking in right direction? I can try to modify the code.

sgavrilov

Member

Registered: 2019-05-24

Posts: 27

Re: CreateAndFillPrepareJoined and MS SQL

Unfortunately, everything is not so easy. Handling of compound field names and constructions 'AS' has to be added in many other places.

So, I suspect that the CreateAndFillPrepareJoined is always emulated for tables mapped to external databases...