Folder Hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Folder Hierarchy

Author

Topic

Zun
Starting Member

4 Posts

Posted - 2004-08-17 : 12:52:53

I am trying to make a sp run faster. By giving a folder id, SP returns all the subfolders underneath it. I was hoping the later will run faster, but it does. Can anyone tell me why??
Thanks in advance

===========================

CREATE TABLE #stack (item INT, lvl INT)
CREATE TABLE #stack2 (i_ID INT,i_sID INT, i_Parent INT,i_Flag CHAR(1))
INSERT #stack2
SELECT ID,SECURITY_ID, PARENTFOLDER, 'S'
FROM dv_folders
WHERE ID = @i_StartFolderID

INSERT INTO #stack VALUES (@i_StartFolderID, 1)
SELECT @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN

SELECT @i_StartFolderID = item
FROM #stack
WHERE lvl = @lvl
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @i_StartFolderID
INSERT #stack
SELECT ID, @lvl + 1
FROM dv_folders
WHERE parentfolder = @i_StartFolderID
INSERT #stack2
SELECT ID, SECURITY_ID, PARENTFOLDER, 'S'
FROM dv_folders
WHERE parentfolder = @i_StartFolderID

IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE

select * from #stack2

======================

======================

CREATE TABLE #stack (i_ID INT, i_Flag CHAR(1),lvl INT)
INSERT #stack
SELECT ID, 'S',0
FROM dv_folders
WHERE ID = @i_StartFolderID

SELECT @lvl = 0
SELECT @cExist = 1

WHILE @cExist > 0
BEGIN
INSERT #stack
SELECT ID, 'S',@lvl+1
FROM dv_folders
WHERE parentfolder in (SELECT i_ID from #stack where lvl=@lvl)

SELECT @cExist = @@ROWCOUNT
SELECT @lvl = @lvl + 1
print @cExist
END -- WHILE

select * from #stack

============================

Zun

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-17 : 12:55:20

Its slow because your using a loop. Try looking at this article (http://www.sqlteam.com/item.asp?ItemID=8866) and if you set up your table in the manner it suggests you can get everything in one query.

Zun
Starting Member

4 Posts

Posted - 2004-08-17 : 13:11:23

Thanks for the prompt reply. I have thought about changing the table structure as you mentioned. But it will require lots of change. I was wondering, if I could modify the existing sp and make it faster. First version of the sp loops 1200 times (number of subfolders) and the second one loops only 6 times but takes thrice as much time as the first one.

Thanks again.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-17 : 18:34:52

The only reason the second version would be slower is the PRINT statement. You can make a few small changes below, but make sure you have an index on dv_folders.parentfolder


SET NOCOUNT ON
declare @i_StartFolderID int, @lvl int
SET @i_StartFolderID = 3
SELECT @lvl = 0

CREATE TABLE #stack (i_ID INT, i_Flag CHAR(1),lvl INT)

--This might help, it might hurt. Try it both ways
CREATE INDEX stack_id_idx on #stack (i_ID)

INSERT #stack 
	SELECT ID, 'S',0 
	FROM dv_folders
	WHERE ID = @i_StartFolderID

--Move @@ROWCOUNT to here and increment lvl before insertion. Get rid of @cExists 
WHILE @@ROWCOUNT > 0 
BEGIN
	SET @lvl = @lvl + 1
--Remove WHERE IN
	INSERT #stack
		SELECT ID, 'S',@lvl
		FROM dv_folders, #stack
		WHERE parentfolder = #stack.i_ID and lvl=@lvl - 1
-- Remove 
print @cExist
END -- WHILE

select * from #stack

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources