谁能帮我看看这个sql query的优化 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

谁能帮我看看这个sql query的优化

谁能帮我看看这个sql query的优化# Database - 数据库

s*n2008-11-06 08:11

1 楼

想要查找一个目录的某个级的子目录, 返回在一个表中.
看看我写的, 挺傻的, 不知道怎么可以优化下
目录关系存在proj_tab里面
create function getSubFolder(@level int, @folderId int)
returns @retab table(folderId int)
as
begin
declare @temp table(folderId int)
insert into @temp select folderId from proj_tab where parentno = @
folderId
select @level = @level -1
while @level <> 0
begin
insert into @retab select folderId from proj_tab where parentno in (
select folderId from @temp)
delete from @temp
insert into @temp

m*02008-11-06 08:11

2 楼

1) use truncate instead of del
2) depends on depth and size of ur tree, if it can go up to 5 or event more, and you do reads more than writes, you might want to change the logic completely, add one more column called depth, and change column parent to column ancestor, and each node will have all the info about all of its ancestor, if a node in lvl 5 is to be inserted, then it should also insert 4 more rows for each of its ancestor.
in this way, u will have instant results back when u do select.

【在 s******n 的大作中提到】

: 想要查找一个目录的某个级的子目录, 返回在一个表中.
: 看看我写的, 挺傻的, 不知道怎么可以优化下
: 目录关系存在proj_tab里面
: create function getSubFolder(@level int, @folderId int)
: returns @retab table(folderId int)
: as
: begin
: declare @temp table(folderId int)
: insert into @temp select folderId from proj_tab where parentno = @
: folderId