ObjectPtr vs FK Join Speed
- 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.
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.
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.
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.