...

4.2 Computations on a dimension table

 
performance optimization practice(38)

SQL

SELECT floor((year(curdate())-year(Employees.Birthday))/10) Age,
    sum(Orders.Amount) Amount
FROM Orders
LEFT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY floor((year(curdate())-year(Employees.Birthday))/10)

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(age(Birthday)\10:Age).keys@i(EmployeeID)
3 =file(“Orders_Time.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=start && OrderDate<=end)
4 =A3.switch(EmployeeID,Employees:EmployeeID)
5 =A4.groups(EmployeeID.Age;sum(Amount):Amount)

A2 Load dimension table data from bin file Employees.btx into memory, compute Age field values, and set primary key and create index on it.

Pre-computing values of the computed column on a dimension table can reduce amount of computations after association.
 

performance optimization practice(38)
 
 •  123 View  •