Sqlite3 of tree-shaped structure traverse efficiency control test

Sqlite3 of tree-shaped structure traverse efficiency control testfirst, the Origin

Project data structure: I engaged in security industry, video surveillance field. The project will encounter monitoring point of the Organization Division, temporarily divided patrol points and so on. These camera points, together with the organization, form a tree structure on a logical relationship.

and because no matter what a point belongs to an organization, it may also belong to a temporarily created video patrol plan, so it can be seen that no matter what node, including the camera node and the organization node, there may be at least one parent node, And no matter what an organization node will have more than one subordinate child node. This logical relationship also constitutes a diagram.
Data Volume Scale: A city-level management platform with a point number of 100,000 to hundreds of thousands of levels. A provincial level of management platform, the point of access to the scale of millions.
Problem: The monitoring platform often needs to use the function, is to be high-speed query out of a node of all the child nodes, child nodes, such as the tree structure.

Our database is now using Sqlite3. Now it is necessary to optimize the performance of the Query Traversal section, which I will take over. I have no experience in database development. It took just a day to try, for example, the following 4 scenarios . Maybe there's the best. Dear reader, you see this post, if you know there is a better way, please enlighten me, I appreciate it.

II, scheme from the above data structure can be seen, To query all the child nodes of a node, the usual practice is that you must use a recursive method. This has the option 1, 2, 3. Let's say switch to ideas . Then a scenario 4 is generated.
schema 1:multimap recursive query

Details such as the following:(1) query all data from the database. (2) Insert the result set of the query into the Multimap (insert).

(3) The recursive method is used to traverse the query to the nodes in the Multimap and all the tree sub-nodes.
Scenario 2: function recursive query Scenario 1 is to query all the data and then recursively find it in Multimap. The idea of Scenario 2 is. Find the child node of this node, then find child nodes of child node, then find child nodes of child nodes ... (The infinite Chamber of posterity). The implementation method is to write a recursive function, recursively query the database, that is, recursive select. (1) Querying the entire parent node from the database is the node record of the incoming node. (2) from the result set in (1), take out the query to the node ID, the node as the "parent node", and then run again (1). (3) by (1) and (2), assuming that the result set of a traversal is empty. Indicates that the node currently traversed is a leaf node, and that it has no child nodes.

The recursive function exits.

Scenario 3:sql Statement Recursive query

The idea of Scenario 1 and 2 is to recursively query outside of SQL statements.

Suppose you can write a recursive SQL statement, can the effect be better? So there was the plan 3. In simple terms. Scenario 3 is the "child node of a child node of a child node that is implemented with a function in Scenario 2". Replace with the SQL statement to implement.

For a recursive statement of Sqlite3, please refer to my other blog post, "sqlite3-recursive query".

Note here that Sqlite3 's recursive syntax with recursive may not be supported in its 3.7.X and the following version numbers, and may prompt for syntax errors syntaxerror. After upgrading to 3.8.x, my sqlite3 library will be able to query the results.

Scenario 4: Introducing a relational table

Now the structure of the database table is, for example, seen.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

It is a structured database table structure .

The ID and parent node ID of the node are stored in a single record.

The advantage is that the development time is high. The downside is that it's not easy to expand and change.

Say it is not easy to expand, because if a node has two parent nodes. One of the fields is not satisfied. Do you want to add a father_id field? Obviously unrealistic, because I don't know how many parent nodes there will be.

Said it is not easy to change, is due to. Assume that multiple parent node IDs are formatted with the father_id field filled in. In the maintenance of records will bring "string and parse string" steps, resulting in maintenance problems.

So. Can you change the way you think about creating database tables with object-oriented thinking ? The following table structure and table relationships are thought of.

What you see. Add a relational table that is specifically used to store the relationships between nodes.

Use father_id and son_id as the Federated primary key.

As a result, the relationship between nodes and nodes is, in fact, a record in the relational table ! How many child nodes a node has, and how many records are in the relational table. How many parent nodes a node has. It is also true.

After you have modified the database table. The advantages are obvious.

The first is the promotion of maintainability.

Delete one or more records from the previous parsing table field, to the current insert.

Secondly, the relationship between the development maintainer and the data will be more deeply and clearly understood.

But inevitably, there are shortcomings.

The first is the cost of development. This table structure and table relationships. Not conducive to high-speed development.

The second is that today's software systems have been used for several years. Sudden changes may result in a sudden increase in the pressure on the field maintenance.

Thirdly, this structure. Whether to meet the performance requirements of business functions, is still an unknown.

Iii. Comparison of results

The test comparison table for the above scenarios 1, 2 and 3 is given below.

Scenario 1

Scenario 2

Scenario 3

Theoretically speaking. The efficiency of the query to get Results is scenario 3 > Scenario 1 > Scenario 2.

Judging from the above 3 tables, the results are indeed as expected.

However, some of the surprises are. Taking the next record from the result set in Scenario 3 (that is, next) is too time-consuming to achieve a 97% percentage.

In terms of overall efficiency, program 1 is the fastest. The second is scenario 3, and the slowest is scenario 2.

Because Scenario 2 is running a large number of recursive calls to functions. function Stack switching. This is the most time-consuming.

Sqlite3 of tree-shaped structure traverse efficiency control test