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

转载 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

向SQL Server 数据库中使用SQL Script向表添加列并指定添加列的位置

使用SQL语句向数据表中添加列,我想大家都清楚该如何做,示例如下: use test go alter table testadd add testid int not null defau...
  • weizhiai12
  • weizhiai12
  • 2012年06月06日 22:24
  • 7062

oracle添加栏位

给大表添加栏位时遇到表有死锁而添加失败,需要kill掉死锁进程 1.先查SID和serial#  SELECT object_name, machine, s.sid, l.SESSION_I...
  • liujunzxcv
  • liujunzxcv
  • 2017年06月07日 16:21
  • 163

如何在ORACLE中实现将查询结果存到一个新表中,包括表结构和查询结果?

问题的确切描述应该是这样的:FLWINRTCHNUMTCHINRTCHNAMCALMODSUBFLWINRNXTTCHNUM7601C02分配专业室Flw12上面这个表(表名为S_TCHSET,其中关...
  • huanglc1980
  • huanglc1980
  • 2004年10月29日 11:00
  • 1706

如何修改Oracle数据表的栏位顺序

在开发过程中,遇到过很多次需要新增栏位但是不想将栏位放在最后的情况~ 以后的做法都是比较烂的啦:备份数据-->Drop Table -->新建Table,再导入数据。。。 有时候实在不想折腾,或者数...
  • u012523524
  • u012523524
  • 2014年03月01日 23:20
  • 3303

SQL查询一个表中类别字段中最大值对应的值

SQL查询一个表中类别字段中最大值对应的值 一、SQL查询一个表中类别字段中最大值对应的值(对一张表的操作): 问题:根据教师id查询checkOutAutoID最大的一条记录里的上期余额? 探...
  • hongwei15732623364
  • hongwei15732623364
  • 2017年01月22日 15:51
  • 2224

SQL中如何将表的查询结果纵向显示(个人思路总结)

SQL中如何将表的查询结果纵向显示(初学者个人思路总结)下面看例子: OOrders表结构是这样的 现在要查询Customer的不同名字分别有多少个,并且要如下显示: 1、于是我...
  • meloyi
  • meloyi
  • 2015年03月25日 17:32
  • 1333

sql中把一个查询结果当做一个新表来查询

sql中把一个查询结果当做一个新表来查询 2009-11-18 15:09 举例: 1、select a.stk_c,b.name,cat_c3 from (select...
  • wocjj
  • wocjj
  • 2012年04月01日 22:50
  • 16566

Oracle中把一个查询结果插入到一张表中

一、Oracle数据库中,把一张表的查询结果直接生成并导入一张新表中。  例如:现有只有A表,查询A表,并且把结果导入B表中。使用如下SQL语句:  create table b as s...
  • zhengbo0
  • zhengbo0
  • 2013年09月25日 19:17
  • 2986

XML 查询、添加、修改、删除

Query:XmlDocument doc = new XmlDocument();   doc.Load(Server.MapPath(".//db//dbGuest.xml"));        ...
  • dz45693
  • dz45693
  • 2010年04月30日 10:01
  • 2633

mysql学习- 将查询结果加上其他键值插入到表中

好吧。。发现一不错的功能, 其实这个功能用到的时候应该不是特别多, 但是用起来会方便 ! 例如我有一个表格,里面是所有客户的地址信息。 客户有个订单,需要送到某个地址上,那么我就直接select出来,...
  • chenfs1992
  • chenfs1992
  • 2014年03月10日 14:04
  • 817
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:在树型结构表中增加一个完整路径意义的栏位将使得查询性能明显提高
举报原因:
原因补充:

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