Switch to: V9V8V7V6V5

ObjectPtr vs FK Join Speed

Let's compare:

  • regular Relational Model with two tables linked with help of Foreign Key based on T1.ID (ULONG) and T2.Ptr (ULONG)
  • and Valentina Model with two tables linked using ObjectPtr T2.ObjPtr pointing T1.RecID.

Foreign Key

We know that T1.ID field, which is a primary key or at least a unique one, should be indexed. T2.Ptr also is indexed.

Join algorithm will use both indexes to build join result. Tables T1 and T2 are not touched usually.

ObjectPtr

With ObjectPtr link, we have to work only with its index, because T1.RecID is virtual field, it does not exist itself on disk, and there is no index for RecID.

Bench

Simple bench with 100K records in T1 linked at rate 1:10 to million records in T2, showed that join by ObjectPtr is about 4 times faster.