Performance Optimization - 6.7 [Foreign key association] Aligned sequence
Performance Optimization - 6.6 [Foreign key association] Index reuse
For the sequence-numberized foreign key, we can also use the aligned sequence to handle the filter on the dimension table.
| A | |
|---|---|
| 1 | =file(“product.btx”).import@b() |
| 2 | =file(“orders_new.btx”).cursor@b(p_id,quantity) |
| 3 | =A1.(state==“CA”) |
| 4 | =A2.select(A3(p_id)) |
| 5 | =A4.groups(A1(p_id).vendor;sum(p_id.price*quantity)) |
A3 generates a sequence with the same length as the dimension table in A1, with each member of the sequence being a boolean value. Records in the dimension table that meet the condition correspond to true, otherwise false. Then in A4, as long as the sequence-numberized primary key is used to take out the member of the aligned sequence, it can be determined whether the dimension table record has been filtered, thereby quickly deciding whether to filter out the fact table record.
Because no actual search is required, the performance of aligned sequence is very good and it is very effective in handling dimension table filtering.
For composite table, pre-cursor filtering will be more advantageous:
| A | |
|---|---|
| 1 | =file(“product.btx”).import@b() |
| 2 | =A1.(state==“CA”) |
| 3 | =file(“orders_new.ctx”).cursor(p_id,quantity;A2(p_id)) |
| 4 | =A4.groups(A1(p_id).vendor;sum(p_id.price*quantity)) |
For foreign keys that have not been sequence-numberized, we can indirectly use the aligned sequence for filtering:
| A | |
|---|---|
| 1 | =file(“product.btx”).import@b().keys@i(id) |
| 2 | =file(“orders.btx”).cursor@b(p_id,quantity) |
| 3 | =A1.(state==“CA”) |
| 4 | =A2.run(p_id=A1.pfind(p_id))).select(A3(p_id) ) |
| 5 | =A4.groups(A1(p_id).vendor;sum(p_id.price*quantity)) |
This operation only adds one action of taking members with sequence number compared to that without filtering, so the performance difference is not significant.
Performance Optimization - 6.8 [Foreign key association] Big dimension table search
Performance Optimization - Preface
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
