Clearing Cell Values

In esProc, the cellset variables for storing values are ubiquitous, and cell values are convenient to reference for computation, but they could occupy too much memory. Data can be cleared from cells to reduce memory footprint after they accomplish their mission in computation. It should be particularly noted that, when the intermediate data is obtained and further complicated computations are needed, useless cell values should be deleted to reduce memory usage in order to effectively avoid memory overflow.

5.3.1 Setting cell values as nulls and resetting a TSeq

In the following task, you are asked to list top 200 transaction records in all household appliances orders and food orders according to total order amount, and sort them by product name. Order records come from two text files: Order_Appliances.txt and Order_Foods.txt. You need to first concatenate the records of the two tables, get top 200 order records by total order amount, and then sort them by product name.

	A
1	=file("Order_Appliances.txt").import@t()
2	=file("Order_Foods.txt").import@t()
3	=A1|A2
4	=A3.top(200;-Amount).new(ID, PName,Type,Date,Amount)
5	=A4.sort(PName)

Below are results of these cells.

The table sequence in A1 contains order records of Order_Appliances.txt:

The table sequence in A2 contains order records of Order_Foods.txt:

A3 concatenates records of the two table sequences for filtering in the next step.A4 first selects top 200 order records according to total sales amount and then generates a new table sequence using certain desired fields. As the records need to be sorted by sales amount in descending order, a negative sign is added before Amount in top() function. This way a result sorted by sales amount in descending order is obtained:

A5 sorts the top 200 order records in terms of sales amount by product names:

Actually it is A5’s data that we really need. After A4 gets all the necessary order records, the original tables in A1 and A2 become useless. Deleting the useless data after getting intermediate data can release memory and make the operation steadier.

Therefore, the above cellset program can be rearranged as follows:

	A	B	C
1	=file("Order_Appliances.txt").import@t()
2	=file("Order_Foods.txt").import@t()
3	=A1|A2	>A1.reset()	>A2=null
4	=A3.top(200;-Amount).new(ID, PName,Type,Date,Amount)	>A3=null
5	=A4.sort(PName)	>A4=null

If the value of a cell is set as null, the data in the cell would be deleted, as the statements in C3 and B4 did. After the statement in C3 clears A2’s cell value and B4 clears the references of food order records from A3, the original food order table will be removed from the memory.

While T.reset() function in B3, which is a little different, will delete all records from the table sequence but retain its data structure. After B3 is executed, A1’s value is as follows:

You can choose either of them to clear cell values for different cases. Setting the cell value as null is more commonly used. T.reset() is used only when the table sequence’s data structure is worth retaining.

Note that though the statement in B5 sets A4’s value as null, memory footprint won’t be reduced. Because the result A5 returns is a record sequence whose records are the references of the table sequence in A4, and these records will still be referenced in A5 and cannot be cleared even if A4 is set as null. Therefore, when the method of setting cell value as null is used to release the memory, you must first find out whether data in the original cell is in use or not.

In addition, A5’s sort operation on A4’s records won’t generate new records. What A5 stores is merely the pointers of the sorted records, which have a limited memory footprint and won’t increase memory usage.

5.3.2 Clearing cell values with clear statement

After a piece of code is executed, probably there is a batch of cells whose values need to be cleared and only the final result is deserved to be retained, like the following cellset:

	A	B
1	=file("Order_Appliances.txt").import@t()
2	=file("Order_Foods.txt").import@t()	=A1|A2
3	=B2.top(200;-Amount).new(ID, PName,Type,Date,Amount)
4	=A3.sort(PName)
5	>A1=null	>A2=null
6	>B2=null	>A3=null

Similar to the previous example, A4 gets the top 200 order records according to the total amount and sorts them by product name. In order to reduce memory usage, A5, B5, A6 and B6 clear values from the useless cells after the computation. Obviously the code is a little tedious. For this esProc specially provides the clear command:

	A	B
1	=file("Order_Appliances.txt").import@t()
2	=file("Order_Foods.txt").import@t()	=A1|A2
3	=B2.top(200;-Amount).new(ID, PName,Type,Date,Amount)
4	=A3.sort(PName)
5	clear A1,A2,B2,A3

This is functionally equivalent to the above method. The clear command in A5 can remove values from multiple cells at a time. Additionally, the clear command can delete cell values by specifying a cell range. For example:

	A	B
1	=file("Order_Appliances.txt").import@t()
2	=file("Order_Foods.txt").import@t()	=A1|A2
3	=B2.top(200;-Amount).new(ID, PName,Type,Date,Amount)
4	=A3.sort(PName)
5	clear A1:B3

In the clear command in A5, A1:B3 represents a rectangular area from A1 to B3, including A1, B1, A2, B2, A3 and B3. The clear command will clear all these cells of values. A single cell and a cell range can appear together in a clear command. For example, A5’s command can be rewritten as clear A1:B2,A3, which has an equivalent function. Another cellset:

	A	B
1	[Order_Appliances.txt,Order_Foods.txt]	=[0]*A1.len()
2	for A1	=file(A2).import@t()
3		=B2.top(100;-Amount)
4		=B3.sort(PName)
5		>B1(#A2)=B4
6	=B1.merge(PName)
7	=A6.new(ID, PName,Type,Date,Amount)

The operation is a little different. It imports data from the two files according to A1’s file names, selects the top 100 records in terms of order amount from each table, sorts them respectively by product name and stores each result as a member of the sequence in B1. As each result has been sorted by product name, they can be merged in A6 by product name. A7 then generates the final table sequence:

In this piece of code, the useless cell values also need to be deleted after A7 gets the final result. Besides by specifying cells or a cell range, you can also clear cell values by specifying a master cell of a code block, as shown in the following cellset:

	A	B
1	[Order_Appliances.txt,Order_Foods.txt]	=[0]*A1.len()
2	for A1	=file(A2).import@t()
3		=B2.top(100;-Amount)
4		=B3.sort(PName)
5		>B1(#A2)=B4
6	=B1.merge(PName)
7	=A6.new(ID, PName,Type,Date,Amount)	clear A1:B1,A2:,A6

In B7’s clear command, the format of A2: is similar to defining a cell range, but it doesn’t specify an ending cell. This means clearing values from all the cells in a code block whose master cell is A2; that is, deleting values from the cells from the second row to the fifth row. After the code is executed, all the cells are cleared of values except A7.