[odb-users] Foreign key - Query executes multiple times on
parent table
Boris Kolpackov
boris at codesynthesis.com
Mon Sep 4 05:39:46 EDT 2023
Lloyd <lloydkl.tech at gmail.com> writes:
> We have two tables connected using foreign key relationship[:]
>
> CREATE TABLE "Parent" (
> "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "F" TEXT NOT NULL,
> "S" INTEGER NOT NULL,
> "TVS" TEXT NOT NULL);
>
> CREATE TABLE "Child" (
> "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "N" TEXT NOT NULL,
> "IId" INTEGER NOT NULL,
> CONSTRAINT "IId_fk" FOREIGN KEY("IId") REFERENCES "Parent"("Id") ON DELETE
> CASCADE DEFERRABLE INITIALLY DEFERRED
> );
>
> PRAGMA_DB(object)
> class Parent
> {
> private:
> Parent(){}
> friend class odb::access;
> PRAGMA_DB(id auto)
> C_UINT64 Id;
> String F;
> CC_UINT64 S;
> std::string TVS;
> };
>
> PRAGMA_DB(object)
> class Child
> {
> private:
> Child(){}
> friend class odb::access;
> PRAGMA_DB(id auto)
> CC_UINT64 Id;
> CC_UINT64 IId;
> std::string N;
>
> PRAGMA_DB(not_null)
> PRAGMA_DB(on_delete(cascade))
> PRAGMA_DB(index)
> std::shared_ptr<Parent>IId;
> public:
> ...
> inline C_UINT64 GetIId() { return IId; }
> };
>
>
> When tracing the query execution using ODB tracer, we observe that In
> each iteration a "select query" is executed on the parent table also
> (Isn't it unnecessary?).
The default semantics of loading an object in ODB is to also load all
its relationships (Child::IId in this case). There are various ways
to avoid this (lazy pointers, sections, etc) but they don't really
apply to your case (see below).
> How can I avoid the "select query" execution on the parent table?
>
> result r = DBPtr.query<Child>( query::ID == id);
> for (result::<Child>iterator fdt(r.begin());fdt != r.end(); ++fdt)
> {
> fdt->GetIId();//Too slow. Executes a select query on parent
> }
You cannot really avoid touching both tables because you need
information from both. But you can make it a single query using
a view:
https://www.codesynthesis.com/products/odb/doc/manual.xhtml#10.1
More information about the odb-users
mailing list