mORMot Open Source

factor200x

Member

Registered: 2017-05-23

Posts: 15

General question

Hello, everyone,

is it possible to create a join of two tables if the tables are located in two different SQLITE3 databases?

Thank you very much in advance,
Olaf

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: General question

It will, if you define the 2nd table as SynDBSQLite3 external table.
It will work, thanks to virtual tables, but it may not be very optimized in practice: one of the table may be running SELECT on rows one by one.
Enable the logs and check the logs to find out what is actually executed.

So in production code, in such case we usually stay at ORM level, caching a lookup table, i.e. either
- make explicit ORM Retrieve per ID of one table (the smaller one, or the less modified one), enabling cache on it (so, most of the time, it will come directly from in-memory JSON)
- pre-load one of the two tables in memory (if it doesn't change often), then again make the join at object/ORM level

factor200x

Member

Registered: 2017-05-23

Posts: 15

Re: General question

Great, thank you very much!!!!

Olaf