...

3.1 Storing data in time order

 
performance optimization practice(38)

Generally, business data has a time attribute. When time span is long, the query usually involves a filtering according to time intervals. Storing data in time order can speed up the filtering process.

3.1.1 Bin file

Stocked data: Import data while sorting it in time order and write data to a binary file.

A
1 =file(“ShipVia.btx”).import@bi()
2 =file(“Orders.txt”).cursor@mt(CustomerID:string, OrderDate:datetime, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int,EmployeeName:string,ShipVia:string).run(OrderDate=days@o(OrderDate), ShipVia=A1.pos@b(ShipVia)).sortx(OrderDate;1000000)
3 =file(“Orders_Time.btx”).export@b(A2)

A1 Import data from Orders table while sorting it in time order.

Newly increased data: Generally, the newly increased data is stored in a separate file, like Orders_add.txt:

A
1 =file(“ShipVia.btx”).import@bi()
2 =file(“Orders_add.txt”).cursor@t(CustomerID:string, OrderDate:datetime, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int, EmployeeName:string, ShipVia:string).run(OrderDate=days@o(OrderDate), ShipVia=A1.pos@b(ShipVia)).sortx(OrderDate;1000000)
3 =file(“Orders_Time.btx”).export@ba(A2)

A2 Import the newly increased data from the corresponding file and sort it by OrderDate.
A3 Fetching A2’s data through the cursor and append it to bin file Orders.btx. Since data is already ordered by OrderDate and the newly added data is naturally generated later, there isn’t a sort-merge to think about. We can just perform the append directly.

3.1.2 Composite table

A composite table stores data in blocks. Each block records the maximum value and the minimum value of the corresponding dimension field (the minmax index). Blocks without eligible values according to the filtering condition on dimension field can be quickly skipped.

The order-based storage scheme can further increase compression ratio and reduce accesses to the external storage device.

Stocked data: Import data while sorting it in time order and write data to a composite table file.

A
1 =file(“ShipVia.btx”).import@bi()
2 =file(“Orders.txt”).cursor@mt(OrderDate:datetime, CustomerID:string, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int,EmployeeName:string,ShipVia:string).run(OrderDate=days@o(OrderDate), ShipVia=A1.pos@b(ShipVia)).sortx(OrderDate;1000000)
3 =file(“Orders_Time.ctx”).create@y(#OrderDate, CustomerID, ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia)
4 =A3.append(A2)
5 >A3.close()

A2 Sort data by OrderDate.
A3 create() function generates structure of the composite table file while specifying OrderDate as dimension field through #OrderDate, according to which data is stored in order. #OrderDate must be written at the beginning, and accordingly A2 needs to adjust the order of fields to write OrderDate in the first place.

Newly increased data:

A
1 =file(“ShipVia.btx”).import@bi()
2 =file(“Orders_add.txt”).cursor@mt(OrderDate:datetime, CustomerID:string, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int, EmployeeName:string, ShipVia:string).run(OrderDate=days@o(OrderDate), ShipVia=A1.pos@b(ShipVia)).sortx(OrderDate;1000000)
3 =file(“Orders_Time.ctx”).open()
4 =A3.append(A2)
5 >A3.close()

A2 Import the newly added data file and sort it by OrderDate.
A4 Fetch A2’s data through the cursor and append it to composite table file Orders.ctx. Since data is already ordered by OrderDate and the newly added data is naturally generated later, there isn’t a sort-merge to think about. We can just perform the append directly.

 

performance optimization practice(38)
 
 •  141 View  •