Performance Optimization - 6.6 [Foreign key association] Aligned sequence
Performance Optimization - 6.5 [Foreign key association] Index reuse
For the foreign key that has been sequence-numberized, we can also use the aligned sequence to process the filtering 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)) |
In this code, A3 will produce a sequence having the same length as dimension table A1. The members of this sequence are all boolean value, and the dimension table record that meets the condition corresponds to true, otherwise it corresponds to false. Then, in A4, as long as the member of the aligned sequence is taken out with the sequence-numberized primary key, we can judge whether the dimension table record has been filtered, so as to quickly decide whether to filter the fact table record.
Because there is no need to do a substantial search, the performance of aligned sequence is quite good, and is very effective in processing the filter of dimension table.
For composite table, the pre-cursor filtering will be more advantageous:
| A | |
|---|---|
| 1 | =file(“product.btx”).import@b() |
| 2 | =A1.(state==“CA”) |
| 3 | =file(“orders_new.ctx”).cursor@b(p_id,quantity;A2(p_id)) |
| 4 | =A4.groups(A1(p_id).vendor;sum(p_id.price*quantity)) |
For foreign keys not sequence-numberized, we can also use the aligned sequence to filter in a disguised form:
| 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 has only one more action of taking members with sequence number than that without filtering, thus the performance is not much different.
Performance Optimization - 6.7 [Foreign key association] Big dimension table search
Performance Optimization - Preface
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
