Shulou(Shulou.com)06/02 Report--

Ranking key is a unique data type of SPL, which is suitable to replace multi-level and discontinuous key values, such as × × number, contract number, product number, organization code and so on. The positioning speed of ranking keys is fast, and it is often used to optimize in-memory index query and foreign key association calculation.

In-memory index query

CardNormal.btx is a set file format × × information table, the amount of data is 1 million, the fields are: cardNo (× × ×, primary key), name (name), gender (gender), province (province), email (e-mail), mobile (mobile phone), address (residential address). CardK.btx and cardNormal.btx are identical in structure and data, except that the cardNo field is a row key.

This case performs millions of index queries on cardNormal.btx and cardK.btx, respectively, and compares their performance.

Where cardNo is the simplified × ×, and the format is as follows:

Number of digits 12345678910111213141516 rules for birthday verification of administrative divisions example of flow verification in provinces and regions 100319801213023x

Provinces and regions: their respective values range from 1 to 10.

Birthday: value from "1980-06-01" to "1981-01-01".

Running water: value is 1-100.

Parity: based on the redundancy bits calculated from the first 15 bits, the values range from 0 to 10, where 10 is represented by x.

To convert the above × × into ranking keys, the following ideas can be taken:

1. Provinces and regions: each layer of the ranking key only supports integers from 1 to 255, so provinces and regions are converted to integers as layers 1 and 2.

two。 Birthday: the ranking key starts with 1, which can achieve better performance, so the interval between the original birthday and 1980-06-01 can be calculated as the third layer ranking key.

3. Pipelined: converted to an integer as the ranking key of the fourth layer.

4. Parity bits: redundant data, can be deleted.

The specific conversion script is as follows:

K (int (mid (cardNo,1,2)

Int (mid (cardNo,3,2))

Interval ("1980-06-01", date (mid (cardNo,5,8), "yyyyMMdd")

Int (mid (cardNo,13,3))

)

Let's perform millions of index queries on cardNormal.btx and cardK.btx respectively.

ABC1=cardNormal=file ("d:\\ temp\\ cardNormal.btx"). Import@b (). Keys (cardNo). Index () / read cardNormal.btx into memory 2=paramList=cardNormal. (cardNo) .sort (rand ()) .to (100000) / randomly take 10, 000 × × 3

4=now ()

5for 100=paramList. (cardNormal.find (~)) / query 6=interval@ms for millions of times (A4Jing now ())

/ string key performance: 5537ms7

8=cardK=file ("d:\\ temp\\ cardk.btx") .import@b () .keys (cardNo) .index@s ()

/ read cardk.btx into memory 9=paramListK=paramList. (K (int (mid (~, 1)), int (mid (~, 3)), interval ("1980-06-01", date (mid (~, 5)), "yyyyMMdd"), int (mid (~, 13)

/ change the string parameter to the ranking key parameter 10=now ()

11for 100=paramListK. (cardK.find (~)) / query 12=interval@ms for millions of times (A10Regent now ())

/ ranking key performance: 1977ms

A8: to set up a memory hash index on the numbering key, use the function option @ s.

As you can see, indexing string keys takes 5547 milliseconds to query, while ranking keys take only 1977 milliseconds, which is significantly faster.

Foreign key association query

TaxNormal.btx is a tax return information table in file format with 10 million pieces of data. The fields are: serial (primary key, serial number), cardNo (foreign key, × ×), tax (tax return), area (region), declaretype (declaration type), unit (declaration unit), declareTime (declaration time), network (processing network). TaxK.btx and taxNormal.btx are identical in structure and data, except that the cardNo field is a row key.

The algorithm for this case:

1. Calculate the foreign key association between taxNormal.btx and cardNormal.btx, eliminate the influence of operating system cache and hard disk IO, and only take the time consumed by the associated action.

two。 Calculate the foreign key association between taxK.btx and cardK.btx, eliminate the influence of operating system cache and hard disk IO, and only take the time consumed by the associated action.

3. Compare the difference between 1 and 2.

The specific algorithm is as follows:

ABC1=file ("d:\\ temp\\ taxNormal.btx") .cursor@b () for A1 Magazine 10000 / Open the tax return and pre-traverse the cursor 2=A1.reset ()

/ reset the cursor to the starting point 3=file ("d:\\ temp\\ cardNormal.btx") .import@b () .keys (cardNo) .index ()

/ Open × × table 4=now ()

5for A1,10000

/ formally traversing the cursor 6=interval@ms (A4MagneNow ()) = A1.reset () / it takes time to traverse the cursor 3748ms7=A1.switch (cardNo,A3:cardNo) for A1 instruction 10000 / establish the association, pre-traverse the cursor 8=A1.reset ()

/ reset the cursor to the starting point 9=now ()

10for A7,10000

/ formally traversing the associated cursor 11=interval@ms (A9 MagneNow ()) = A11-A6/ takes time to establish an association: above 7553ms12/: string key association. The following: ranking key association

13=file ("d:\\ temp\\ taxK.btx"). Cursor@b () for A13 Magi 10000 / Open the tax return and pre-traverse the cursor 14=A13.reset ()

/ reset the cursor to the starting point 15=file ("d:\\ temp\\ cardK.btx") .import@b () .keys (cardNo) .index@s ()

/ Open × × table 16=now ()

17for A13,10000

/ formally traversing cursors 18=interval@ms (A16 cardNo,A15:cardNo now ()) = A13.reset () / traversing cursors takes time 2884ms19=A13.switch (cardNo,A15:cardNo) for A19recovery10000 / establishing associations, pre-traversing cursors 20=A19.reset ()

/ reset the cursor to the starting point 21=now ()

22for A19,10000

/ formally traversing the associated cursor 23=interval@ms (A21 MagneNow ()) = A23-A18/ takes time to establish the association: 966ms

B2, B7, B13, B19: pre-traverse cursors to avoid the impact of operating system caching. After traversing, you need to reset the cursor with the reset function so that you can traverse again later.

B11, B23: subtract the traversal time from the total time to obtain the time consumed by the associated action.

As you can see, using ordinary keys to do foreign key association calculation, the association action takes 7553ms; when numbering keys do foreign key association calculation, it only takes time 966ms, the latter is obviously fast. It can also be seen that it takes less time to use the typesetting key to read out the record (fetch) than to use the string (other fields and record rows are the same), indicating that the generation performance of the typesetting key object is better.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.