I'm working on a project which stores data of a tree-structured models like file systems and so on. And in many cases the tree has large number of leaves in it and have unknown depth. My project is about extract data of each node in tree. like number of direct nodes, number of leaves, and some aggregation operation on leaves node data.
Since as i indicated, trees may have large number of leaves with unknow depth, i used the path enumeration technique for assigning a path to each node while inserting. and my table is like below :
| id | path | level | is_laef | tree | attributes |
|---|
** Structure of path is like "10-50-80" which numbers are ancestors ids.
Aggregation operations are done on attributes column of leaf nodes.
But my problem is, when i add several tree with many leaf nodes (like 250 to 1000), extracting data is relatively long (around 2 sec). Even simple queries like
SELECT
n.*,
COUNT(n.id) AS toal_leaves
FROM nodes n
WHERE instr(n.path, '1-') = 1 AND is_leaf
which fetch number of leaf nodes of node with path "1" takes significant time. And for my cases which i need several data, i wrote multi level nested SQL query (subqueries) which absolutely takes more time.
Now my question is, is my approach suitable for large trees? Or is there any better approach with better performance and more optimized ?
NOTE: I'm using SQLite for this project because it is a local and offline software.