Application of full path in tree structure table

tree structure tables are often used during MIS system development, for example, the Department table of the human resources module and the accounting department of the financial module. The traditional approach is to embody a tree-type parent-child relationship in code and specify the length of each section, this method forces users to follow the Program rules. users do not have the freedom to select encoding rules, and the scalability is poor. To solve this problem, we usually create two columns (or similar columns): ID and parent_id in the table to express the tree parent-child relationship between the two data, however, the negative effect is that recursive algorithms or loops are required when querying non-direct parent or subitem, Which is unsatisfactory in performance. If you add a full path field (which can fully describe the path of a project, similar to the file full path of the operating system), the query performance will be significantly improved.

Take a department table as an example. Create Table [hrm_dept] ([dept_code] [varchar] (20) Collate chinese_prc_ci_as not null, [dept_name] [varchar] (40) Collate chinese_prc_ci_as not null, [parent_dept_code] [varchar] (20) Collate primary null, [dept_path] [varchar] (8000) Collate primary null, constraint [pk_hrm_dept] primary key clustered ([dept_code]) on [primary]) on [primary] Where: dept_code indicates the Department number and dept_name table Department name. parent_dept_code indicates the parent department number, and dept_path indicates the Department path (maintained by the trigger ). The user needs to obtain all sub-departments (direct and indirect) of the department according to the entered Department number ). If there is no dept_path field or the dept_path field is not used, the function we created to obtain all sub-departments of the specified department may be written in this way. Create Function partition (@ dept_code varchar (20) returns @ tab_childdept table (parent_dept_code varchar (20), child_dept_code varchar (20) As/* Name: Partition Function Description: retrieve all sub-departments of a specified department. related objects: hrm_dept Department Table Implementation Method Description: recursively retrieve sub-departments. input parameters: @ dept_code Department number output content: All sub-departments of a specified department. creators: kang Jianmin creation date: 2006-06-06 */begin --- defines a temporary table to store the sub-door data declare @ temp_childdept table (row_id int identity (1, 1) not null, parent_dept_code varch AR (20) not null, child_dept_code varchar (20) not null) Declare @ child_dept_code varchar (20), -- Sub-Door number @ max_row_id int, --- Insert the largest row number @ loop_row_id int --- loop row number --- Insert the direct sub-door data insert into @ tab_childdept (parent_dept_code, child_dept_code) Select @ dept_code, dept_codefrom hrm_deptwhere parent_dept_code = @ dept_code --- insert direct sub-data to the temporary table insert into @ temp_childdept (parent_dept_code, child_dept_code) Select @ dept_code, Dept_codefrom hrm_deptwhere parent_dept_code = @ dept_code --- Select @ max_row_id = max (row_id), @ loop_row_id = min (row_id) from @ override if @ loop_row_id is null or @ loop_row_id = 0 select @ loop_row_id = 1 --- cyclically retrieve sub-data while @ loop_row_id <= @ max_row_id begin select @ child_dept_code = response from @ override where row_id = @ loop_row_id --- Insert the data of the sub-door into the insert into @ tab_childdept (Parent_dept_code, child_dept_code) Select parent_dept_code, child_dept_code from DBO. udf_hrm_getchilddept (@ child_dept_code) --- cyclically Variable Step-by-Step auto-increment select @ loop_row_id = @ loop_row_id + 1 endreturnend if the Department path field is used, this function can be transformed as follows. Create Function partition (@ dept_code varchar (20) returns @ tab_childdept table (parent_dept_code varchar (20), child_dept_code varchar (20) As/* Name: Partition Function Description: retrieve all sub-departments of a specified department. related objects: hrm_dept Department table implementation method brief: obtain sub-departments based on the department path. input parameter: @ dept_code Department number. Output content: All sub-departments of a specified department, meaning of the returned table field: parent_dept_code parent department no. child_dept_code sub-department no. Creator: Kang Jianmin creation date: 2006-06-06 */begindeclare @ dept_path varchar (8000) -- department path Select @ dept_path = dept_pathfrom hrm_deptwhere dept_code = @ dept_code -- insert sub-door data (direct and indirect) insert into @ tab_childdept (parent_dept_code, child_dept_code) Select partition, dept_codefrom hrm_deptwhere dept_path like @ dept_path + '/%' Here we assume that dept_path uses '/' to separate department numbers. After testing, method 2 significantly improves the performance. Based on the test sample data of the author, the number of sub-departments obtained is also 727. method 1 takes 3646 milliseconds, and method 2 only takes 20 milliseconds. method 1 takes 182 times of method 2. If the length of the dept_path field can be limited to less than 900 bytes (MSSQLServer limit) and the index can be added to it, the speed is faster. Note: The test data in the sample may be different in different test environments, but the conclusion is the same, that is, method 2 has good performance. The method for retrieving all parent projects based on the specified project is similar. Of course, if you use the full path, you should maintain the full path value in the table insertion, modification, and deletion triggers. The maintenance cost is not high, but the query speed is greatly improved, this optimization method is worth doing if the tree structure table does not frequently insert, modify, or delete data but has many opportunities for query. In addition, pay attention to the following points: If the characters in the generated complete path are too long, the text type can be used. The field on which the complete Path depends cannot use the Separator Used by the program.