Performance Optimization - 5.7 [Traversal technology] Index sorting

 

We know that the index is essentially the sorting. If we want to sort the data table by the to-be-searched key (TBS key), is it possible to take advantage of the index that has already been built?

Unfortunately, this method has no effect in most cases.

If the original data table is not sorted by the TBS key, even if the index has been built, and the physical location of each record can be read out in order from the index, and no sorting operation is required, the index sorting algorithm will not have much advantage over directly performing the big sorting algorithm. The reason is that although the big sorting algorithm needs to perform sort operation, its access to external storage data tables is basically continuous, and a large number of data can be read and written each time; while if the index is used to read data in order, although the sort operation does not need to be performed, the physical position of the data in external storage is often discontinuous, which may lead to a large amount of unnecessary reading actions. On a whole, the index sorting is very likely to perform worse than big sorting.

In fact, the index has no effect on most traversal-pattern operations, and it is mainly used for situations where there are very few returned result sets.

However, although the overall sorting time with a ready-made index is not necessarily faster than that of big sorting algorithm, it has one advantage that can quickly start data output. On the contrary, the big sorting algorithm needs to traverse all the data and generate the buffer files before it starts outputting data, resulting in a longer waiting time.

Which application scenarios will use index sorting to quickly start data output?

For example, when the big data needs to be transmitted in order, the remote transmission itself is very slow and often not much faster than big sorting. If the data can be transmitted earlier, a lot of time will be saved. To achieve it, using the index sorting algorithms will be more advantageous, which can immediately start returning data for transmission without waiting.

A
1 =file(“data.ctx”).open()
2 =A1.index(IDS;ID;…)
3 =A1.icursor@s(…;,IDS)

The icursor@s()will ensure that the data sorted by specified index is returned (the order will not be ensured when there is no option, but it is generally sorted by physical location, which is faster). The data will also be returned as a cursor, and taken out by fetch()(for transmission).