在树型结构表中增加一个完整路径意义的栏位将使得查询性能明显提高

 

 

       在MIS系统开发过程中经常使用树型结构表，如人力资源模块的部门表、财务模块的会计科目表等，传统的做法是从编码上体现一种树型父子关系，并约定每节的字长，这种做法强制用户按照程序既定的规则，用户没有选择编码规则的自由，扩展性差。为了解决这个问题，一般会在表中建两个栏位（或类似栏位）：id和parent_id，用于表述两条数据间的树型父子关系，但这带来的负作用是查询非直接父项或子项时需要使用递归算法或循环，性能上并不令人满意。如果在此基础上增加一个完整路径的栏位(能够完整描述一个项目的路径的栏位，类似操作系统的文件全路径)，则查询性能会明显提高。

       我们以一个部门表为例。

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 Chinese_PRC_CI_AS NULL,

       [dept_path] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,

       CONSTRAINT [PK_hrm_dept] PRIMARY KEY CLUSTERED

       (

              [dept_code]

       ) ON [PRIMARY]

) ON [PRIMARY]

其中：dept_code表示部门编号，dept_name表示部门名称，parent_dept_code表示父部门编号，dept_path表示部门路径（由触发器维护）。

       用户的需求是根据输入的部门编号获取该部门的所有子部门（直接和间接）。

如果没有dept_path栏位或不利用dept_path栏位，我们所创建的获取指定部门的所有子部门的函数可能会这样写。

CREATE FUNCTION udf_hrm_getchilddept (@dept_code varchar(20))

RETURNS @tab_childdept TABLE (parent_dept_code varchar(20),child_dept_code varchar(20))

AS

/*

名称:              udf_hrm_getchilddept

功能描述：     取指定部门的所有子部门

涉及对象：     hrm_dept 部门表

实现方法简述：递归取子部门

输入参数：     @dept_code 部门编号

输出内容：     指定部门的所有子部门

创建人员：     康剑民           

创建日期：  2006-06-06       

*/

BEGIN

---定义临时表以存储子部门数据

declare @temp_childdept table

       (row_id int IDENTITY(1, 1) not null

       ,parent_dept_code varchar(20) not null

       ,child_dept_code varchar(20) not null)

 

declare @child_dept_code varchar(20),--子部门编号

       @max_row_id int,---插入的最大行号

       @loop_row_id int---循环行号

 

---插入直接子部门数据

insert into @tab_childdept

       (parent_dept_code

       ,child_dept_code)

select @dept_code

       ,dept_code

from hrm_dept

where parent_dept_code = @dept_code

 

---插入直接子部门数据到临时表

insert into @temp_childdept

       (parent_dept_code

       ,child_dept_code)

select @dept_code

       ,dept_code

from hrm_dept

where parent_dept_code = @dept_code

 

---取最大行号和最小行号

select @max_row_id = max(row_id),

       @loop_row_id = min(row_id)

from @temp_childdept

 

if @loop_row_id is null or @loop_row_id = 0 select @loop_row_id=1

---循环取子部门数据

while @loop_row_id <= @max_row_id

       begin

       select @child_dept_code = child_dept_code

       from @temp_childdept

       where row_id = @loop_row_id

 

       ---插入子部门的子部门数据

       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)

 

       ---循环变量步进自增

       select @loop_row_id = @loop_row_id + 1

       end

return

end

 

如果使用部门路径栏位，则该函数可以按如下改造。

CREATE FUNCTION udf_hrm_getchilddept2 (@dept_code varchar(20))

RETURNS @tab_childdept TABLE (parent_dept_code varchar(20),child_dept_code varchar(20))

AS

/*

名称:              udf_hrm_getchilddept

功能描述：     取指定部门的所有子部门

涉及对象：     hrm_dept 部门表

实现方法简述：根据部门路径取子部门

输入参数：     @dept_code 部门编号

输出内容：     指定部门的所有子部门,返回表栏位含义:

              parent_dept_code 父部门编号

              child_dept_code 子部门编号

创建人员：     康剑民           

创建日期：  2006-06-06       

*/

BEGIN

declare @dept_path varchar(8000)--部门路径

 

--取部门路径

select @dept_path = dept_path

from hrm_dept

where dept_code = @dept_code

--插入子部门数据(直接和间接)

insert into @tab_childdept

       (parent_dept_code

       ,child_dept_code)

select parent_dept_code

       ,dept_code

from hrm_dept

where dept_path like @dept_path + '/%'

 

在此我们假设dept_path使用’/’分隔部门编号。

经测试，方法二明显性能提高。以笔者的测试样例数据，获取子部门数同样为727条，方法一需要3646毫秒，方法二只需要20毫秒，方法一所用时间是方法二的182倍。如果dept_path栏位长度可以控制在900个字节以内（MSSQLSERVER限制）并在其上加索引，则速度更快。注：不同的测试环境，样例测试数据可能不同，但结论是相同的，那就是方法二性能好。

根据指定的项目取所有父项目的方法类似。

当然，如果使用完整路径，应该在表的插入、修改、删除触发中维护完整路径的值，维护的代价不高，但查询速度得到大幅度提高，如果这个树型结构表不是频繁地插入、修改、删除数据而是查询的机会多的话，这种优化方法是值得去做的。另外还要注意几点：如果生成完整路径的字符过长，可以采用text类型；完整路径所依赖的栏位不能使用程序所使用的分隔符。

 

 

 

 写作日期:2006-06-06