RaqForum 25 No.
285 View  • 
     ...

4.6 Alignment sequence

 
performance optimization practice(38)

For a numberized dimension table, we can handle filtering on it through an alignment sequence.
Here is the work procedure. We generate a sequence having same length as the dimension table and whose members are boolean values. A dimension record meeting the specified condition corresponds to true, and one that is ineligible corresponds to false. Then based on the fact table, get corresponding members of the alignment sequence according to their positions, the numberized foreign key values. This tells us whether the corresponding dimension table record is filtered away and lets us quickly decide if the current fact table record should be discarded.
As no concrete searches are involved, the alignment sequence can effectively boost performance of dimension filtering.

SQL

SELECT sum(Amount)
FROM Orders
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and EmployeeID not in (
    SELECT EmployeeI
    FROM Employee
    WHERE Employees.Country='USA')

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 >Employees=file(“Employees.btx”).import@b()
3 =Employees.(Country!=“USA”)
4 =file(“Orders_Time.ctx”).open().cursor@mx(Amount;OrderDate>=start && OrderDate<=end && A3(EmployeeID))
5 =A4.groups(;sum(Amount))

A3 Get the boolean sequence corresponding to the dimension table to find whether the current member meets the filtering condition Country==“USA”.
A4 Associate with A3 through ordinal numbers within the composite table cursor and keep records making A3 true.

SQL

SELECT a.EmployeeID,concat(a.FirstName,' ', a.LastName) EmployeeName,
    sum(Amount) Amount
FROM Orders
INNER JOIN (
    SELECT *
    FROM Employees
    WHERE Employees.Country<>'USA'
    ) a ON Orders.EmployeeID=a.EmployeeID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY a.EmployeeID,a.FirstName+' '+ a.LastName

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 >Employees=file(“Employees.btx”).import@b().derive(FirstName+" "+LastName:EmployeeName)
3 =Employees.(if(Country!=“USA”,~,null))
4 =file(“Orders_Time.ctx”).open().cursor@mx(EmployeeID,Amount;OrderDate>=start && OrderDate<=end,EmployeeID:A3:#)
5 =A4.groups(EmployeeID.EmployeeID;EmployeeID.EmployeeName,sum(Amount):Amount)

A3 Get the alignment sequence variant corresponding to dimension table. In the sequence, positions where members do not meet the specified condition are given nulls, and those where members are eligible hold original records.
A4 Associate with A3 through ordinal numbers in the composite table cursor and only keep records where A3 is non-null. Once associated, the composite table’s EmployeeID field values are corresponding members of A3, which can be referenced in later computations.
A5 Reference EmployeeID field values to perform aggregation.

 

performance optimization practice(38)
 
 •  285 View  •