[odb-users] Foreign key - Query executes multiple times on parent table

[Lloyd]

We are using ODB (2.4.0) with sqlite-3 (3.41.2)

We have two tables connected using foreign key relationship. The child
table has 3 columns and 0.25 million records. The parent table has 4
columns and 2 records. In our C++ program, I have given a "select *" query
without any condition on the child table and the results are copied in an
iterative loop. 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?). This makes the query execution very
slow (around 45 minutes).

The table structure and the code fragment is given below. How can I avoid
the "select query" execution on the parent table?

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
);


Following is the Implementation of the SQLite-specific Tracer

#include <odb/sqlite/tracer.hxx>
#include <odb/sqlite/database.hxx>
#include <odb/sqlite/connection.hxx>
#include <odb/sqlite/statement.hxx>

class sqlitetracer : public odb::sqlite::tracer
{
    virtual void prepare(odb::sqlite::connection& c, const
odb::sqlite::statement& s) override
    {
        cerr << c.database().name()/*.db()*/ << ": PREPARE " << s.text()
/*<< " AS " << s.text()*/;
    }

    virtual void execute(odb::sqlite::connection& c, const
odb::sqlite::statement& s) override
    {
        cerr << c.database().name() << ": EXECUTE " << s.text();
    }

    virtual void execute(odb::sqlite::connection& c, const char* statement)
override
    {
         cerr << c.database().name() << ": " << statement;
    }

    virtual void deallocate(odb::sqlite::connection& c, const
odb::sqlite::statement& s) override
    {
        cerr << c.database().name() << ": DEALLOCATE " << s.text();
    }
};

The above tracer returns 2 SQL statements :
1. SELECT "Child"."Id", "Child"."N", "Child"."IId" WHERE "Child"."Id" = 2;
2. SELECT "Parent"."Id", "Parent"."F", "Parent"."S", "Parent"."TVS" WHERE
"Parent"."Id"=1;

The first query is executed once and the second query is executed for 0.25
million times. The following is the C++ code for reading the data

typedef unsigned long long C_UINT64;
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; }
};


typedef odb::result<Child> result;
typedef odb::query<Child> query;

transaction t(DBPtr.begin());
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
}
t.commit();