Create a table having fact type, fact hash, ancestor type, ancestor hash. Define a unique index on the Cartesian product of all four columns. This table contains a record for every predecessor, and computes the transitive closure over predecessors.
To migrate, run the recursive query currently used in the load request.
Upon save, execute a self-join to insert all ancestors of all predecessors. Ignore duplicate inserts.
Replace the recursive query in load with a join to this table. Measure performance difference between the two mechanisms, and accept the optimization only if it produces a statistically significant improvement.
Create a table having fact type, fact hash, ancestor type, ancestor hash. Define a unique index on the Cartesian product of all four columns. This table contains a record for every predecessor, and computes the transitive closure over predecessors.
To migrate, run the recursive query currently used in the
loadrequest.Upon
save, execute a self-join to insert all ancestors of all predecessors. Ignore duplicate inserts.Replace the recursive query in
loadwith a join to this table. Measure performance difference between the two mechanisms, and accept the optimization only if it produces a statistically significant improvement.