BinaryLink M:M vs FK Join Speed

Let's compare:

• a regular Relational Model of M:M link between T1 and T2 with help of the third table T12 and two FK-links T1-T12 and T12-T2. Both links are based on ID and PTR fields of ULONG (4 bytes) type.

• and Valentina Model with two tables T1 and T2 linked using BinaryLink of M:M kind.

And let`s assume that we should do logical join of T1 and T2 records. Note, that logical sense of T12 is only implementation of M:M links between records of T1 and T2.

To resolve this task, relational DBMS should execute two joins: 1) join of T1.ID to T12.Ptr1. For this indexes of T1.ID and T12.Ptr1 are used. 2) then join table produced on the step 1 should be joined to T2.

So we have two joins, which may use 4 indexes. Actually things are even harder if go deeply.

With BinaryLink of M:M kind we solve the task accessing effectively a single(!!!) index.

Simple bench have shown near to x8 faster speed of BinaryLink over MM third table.