100分求帮忙优化SQL,多谢!!!
初学SQL, 为了达到项目目的,写了以下SQL,用了4个游标嵌套,忘大哥们帮忙优化一下以提高性能,谢谢!
代码如下:
说明:用到的主表:ORGGroupDeptH,存储的是人事组织架构.

最后达到的目的是要这样的结构:
第二第三 第四 第五 序 名
fn0101000000 系系
fn0101000001 1 董事 高理
fn0101000002 件部
fn0101000000 1 集董事 理
fn0101000000 2 集董事 系分析
fn0101000000 3 集董事 高用程序
fn0101000000 4 集董事 用程序
fn0101000000 系部
fn0101000000 1 集董事 系部

谢谢大家!
Create table #OrgInfo
(
intNo smallint IDENTITY(1,1), nvrFunctionNo nvarchar(12),twiceLvl nvarchar(100),thirdLvl nvarchar(100),
forthLvl nvarchar(100),fifthLvl nvarchar(100),orderNo varchar(10),titleClass nvarchar(100),funcTitle nvarchar(100)
)
--drop table #OrgInfo
--select * from #OrgInfo
SET NOCOUNT ON

--定义变量
DECLARE @functionNo varchar(12),@twiceLvl varchar(100),@thirdLvl varchar(100),
@forthLvl varchar(100),@fifthLvl varchar(100),@orderNo int,@titleClass varchar(100),@funcTitle varchar(100)

--定义第二层游标
DECLARE twiceLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE intDeptLv=2 ORDER BY bitIsDept,nvrInterface,intOrderNo
OPEN twiceLvlCursor

FETCH NEXT FROM twiceLvlCursor
INTO @functionNo

WHILE @@FETCH_STATUS = 0
BEGIN
print '第二functionNo '+@functionNo
--查询出相关值
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo

--插入第二层部门
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo,@funcTitle, ' ', ' ', ' ', ' ', ' ', ' ')

--定义第三层游标值
DECLARE thirdLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=3 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN thirdLvlCursor
FETCH NEXT FROM thirdLvlCursor INTO @functionNo

WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第三functionNo '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=3 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN

SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass
WHERE h.nvrFunctionNo=@functionNo

--插入第三层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END

--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ',@funcTitle, ' ', ' ', ' ', ' ', ' ')
--************************
print '第三部 ' + @functionNo
--定义第四层游标值
DECLARE forthLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=4 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN forthLvlCursor
FETCH NEXT FROM forthLvlCursor INTO @functionNo

WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第四层functionNo '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=4 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN

SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass
WHERE h.nvrFunctionNo=@functionNo

--插入第四层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END

--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ',@funcTitle, ' ', ' ', ' ', ' ')

--************************
--定义第五游标值
DECLARE fifthLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=5 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN fifthLvlCursor
FETCH NEXT FROM fifthLvlCursor INTO @functionNo

WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第五 '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=5 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN

SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass

WHERE h.nvrFunctionNo=@functionNo

--插入第五层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END

--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ',@funcTitle, ' ', ' ', ' ')
END
FETCH NEXT FROM fifthLvlCursor INTO @functionNo
END
CLOSE fifthLvlCursor
DEALLOCATE fifthLvlCursor
--************************
END
FETCH NEXT FROM forthLvlCursor INTO @functionNo
END
CLOSE forthLvlCursor
DEALLOCATE forthLvlCursor
--*********************

END
FETCH NEXT FROM thirdLvlCursor INTO @functionNo
END
CLOSE thirdLvlCursor
DEALLOCATE thirdLvlCursor
FETCH NEXT FROM twiceLvlCursor INTO @functionNo

END
CLOSE twiceLvlCursor
DEALLOCATE twiceLvlCursor

select * from #orginfo
drop table #OrgInfo


[解决办法]
你的原是怎的？
[解决办法]
hehe ...

lz，你没了解鱼兄跟你说的，你要把你基础数据贴出来一些，别人才好给你看问题啊。
另外你写的存储过程太长了，一般都会自己重写一个的。
[解决办法]
俺来学习顺便接点分！
[解决办法]
都是高手
[解决办法]
可以把#OrgInfo表做成 实际表而不是临时表
使用临时表速度太慢（也许是没有主键索引的关系）