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

转载 2006年06月09日 12:47:00

 

 

       在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
举报

相关文章推荐

表分区MySQL版本:5.5.11比5.1查询速度明显提高(4倍左右)

实验1   1、建表: create table `t_part_test`( `id` int NOT NULL default 0, `detail` char(32) NO...

java中使用多线程不能明显提高程序效率的一些原因

java中使用多线程不能明显提高程序效率的一些原因. 使用多个线程来处理多任务的时候,效率肯定是有提高的.但是必须要慎用,否则容易出现问题. 1.多线程主要是为了充分利用多核cpu,大内存...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

一个容易扩展的树型结构

需求背景:     最近做了一个项目,需要把电脑中存放的上千个文件夹和对应的文件以页面树型的方式给客户展示出来,便于客户阅读,修改,上传添加等操作,便于管理员对文件的权限访问管理控制,这些文件...

数据库查询出的扁形数据转树型结构数据

扁形数据结构 [ {id:1,pid:2,text:你好}, {id:2,pid:3,text:你好} ] 转换为树型json结构 ...

v$queue中的type栏位的意义

The previous post in this series showed you how v$reqdist summarised the time taken by tasks running...

左右编号树型结构

以下面一个实际的计算机目录结构为例,研究左右值编码存储树形结构的算法 计算机   |----c:   |      |----program     |&#...

【PB】树型结构的遍历

树型结构遍历 作者:郭宝利  来源:原创  日期:2004.06.27  树型结构的遍历通常使用递归程序来实现,所以本节给出一个按照内容查找,对树型结构进行遍历,如...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)