RaqForum 25 No.
249 View  • 
     ...

1.4 Conversion from enumerated string to small integer

 
performance optimization practice(38)

We can convert an enumerated string that may have a limited range of values to integers so that better storage and computing performance can be achieved.

1.4.1 Conversion during data dump

We can replace an enumerated field with the corresponding ordinal number in the sequence of values. Take ShipVia as an example:

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))
3 =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia)
4 =A3.append(A2)
5 =A3.close()

A1 Import the sequence of unique values of ShipVia field. Generally, the values are already ordered in the source file. @i option enables reading them as a sequence.

We can also write the sequence of unique values directly in the SPL script:

A
1 [Federal Shipping,Speedy Express,United Package]

A1 Convert ShipVia field to ordinal number of the corresponding value in the sequence. As the sequence of unique values are ordered, we can use @b option to perform a binary search.

1.4.2 Computation using converted data

SQL

SELECT sum(Orders.Quantity) AS Quantity,ShipVia
FROM Orders
WHERE ShipVia='Federal Shipping' or ShipVia='Speedy Express'
GROUP BY ShipVia

SPL

A
1 >arg1=“Federal Shipping”,arg2=“Speedy Express”
2 =file(“ShipVia.btx”).import@ib()
3 =arg1=A2.pos@b(arg1),arg2=A2.pos@b(arg2)
4 =file(“Orders.ctx”).open().cursor@mx(Quantity, ShipVia; ShipVia==arg1 || ShipVia==arg2)
5 =A4.groups(ShipVia;sum(Quantity):Quantity)
6 =A5.run(ShipVia=A2(ShipVia))

A3 Calculate the small integer corresponding to each parameter value according to the order of sequence of unique values.
A6 Convert ShipVia in the result set back to corresponding string value.

SQL

SELECT sum(Orders.Quantity) AS Quantity,ShipVia
FROM Orders
WHERE ShipVia in ('Federal Shipping','Speedy Express')
GROUP BY ShipVia

SPL

A
1 >arg=[“Federal Shipping”,“Speedy Express”]
2 =file(“ShipVia.btx”).import@ib()
3 =A2.(arg.contain@b(~))
4 =file(“Orders.ctx”).open().cursor@mx(Quantity, ShipVia;A3(ShipVia))
5 =A4.groups(ShipVia;sum(Quantity):Quantity)
6 =A5.run(ShipVia=A2(ShipVia))

A3 The filtering parameter is a set. According to order of the sequence of values, get the corresponding sequence of booleans that denote whether the current member is contained in the parameter set.
A4 To filter records, get a value from A3 according to the ordinal number and judge whether the current ShipVia field value is contained in the parameter set.
A6 Convert ShipVia in the result set back to corresponding string value.

performance optimization practice(38)
 
 •  249 View  •