mORMot Open Source

sgavrilov

Member

Registered: 2019-05-24

Posts: 25

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