Here is a sp I used for a forum application. It doesn't require nested
loops and is VERY fast. You will have to adjust it for your needs, but it
should be pretty easy.
--CREATE PROCEDURE expand (@current int) as
DECLARE @current int
DECLARE @level int, @line char(20)
DECLARE @Pos int
DECLARE @PrevReplyTo int
CREATE TABLE #stack (item int, Thislevel int)
CREATE TABLE #x (ID int, Name varchar(256), DateCreated datetime, InReplyTo
int, Topic int, TotalReply int, Creator int, IsTop bit, Root int, TopDate
datetime, _USER_ID int, MessageFrom varchar(256),ThisLevel int, Position
int)
CREATE TABLE #y (ID int, InReplyTo int, Root int)
SET @current = 94
SET @Pos = 0
SET @PrevReplyTo = @current
INSERT #y
SELECT ID,InReplyTo, Root
FROM Threads
WHERE Root = @current
ORDER BY DateCreated DESC
INSERT #x
SELECT Threads.ID AS "ID",
Threads.Name AS "Name",
Threads.DateCreated AS "DateCreated",
Threads.InReplyTo AS "InReplyTo",
Threads.Topic AS "Topic",
Threads.TotalReply AS "TotalReply",
Threads.Creator AS "Creator",
Threads.IsTop AS "IsTop",
Threads.Root AS "Root",
Threads.TopDate AS "TopDate",
Users.ID AS "_USER_ID",
Users.Userid AS "MessageFrom",
0,
0
FROM Threads,Users
WHERE Threads.Root = @current AND Threads.Creator = Users.ID
SET NOCOUNT ON
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Thislevel = @level)
BEGIN
-- If an item on the stack exists at this level, grab it
SELECT @current = item FROM #stack WHERE Thislevel = @level
-- Increment the position
SET @Pos = @Pos + 1
-- Update the table with the new position
UPDATE #x SET Position = @Pos, ThisLevel = @level - 1 WHERE ID =
@current
-- Delete this entry from the temp table (#y)
DELETE FROM #y WHERE ID = @current
-- Delete this entry from the stack
DELETE FROM #stack WHERE Thislevel = @level AND item = @current
-- Insert into the stack any IDs that are replies to the CURRENT ID
INSERT #stack SELECT ID, @level + 1 FROM #y WHERE InReplyTo =
@current
-- If any were found, increment the level
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
IF ((SELECT COUNT(*) FROM #y) > 0 AND (SELECT COUNT(*) FROM #stack) = 0)
BEGIN
IF @level = 0
SELECT @level = @level + 1
INSERT #stack
SELECT ID, @level + 1 FROM #y
SELECT * FROM #stack
END
END -- WHILE
/*
IF EXISTS(SELECT ID FROM #y)
BEGIN
UPDATE #x SET Position = @Pos + 1, ThisLevel = 1 WHERE Position = 0 AND
ThisLevel = 0
UPDATE #x SET Position = @Pos + 1 WHERE Position = 0
END
*/
DROP TABLE #stack
SELECT ID,Name,ThisLevel,Position FROM #x ORDER BY Position,DateCreated
DROP TABLE #x
DROP TABLE #y
----------------------------------------------
Original Message
From: "Jeffry Houser"<[EMAIL PROTECTED]>
Subject: Re: Database Design Challenge!
Date: Thu, 17 May 2001 11:27:21 -0700
>
> I was struggling with a similar situation a while back, I don't know if
>I posted the query to the list (if I did, I didn't get any
>responses). Unfortunately, I don't know of a way to do it other than
>looping. I suspect this can be done via a stored procedure, but those are
>not my area of expertise. (anyone? Anyone?) I don't know if they have
>any looping functionalities.
>
> However, I was able to mock something up using a Query of a Query
>feature of CF 5.0. It increased performance about 3 times, I believe, but
>the further down you were into the table the bigger the performance
increase.
>
>Query the database to get the whole table.
>
>set tempID = mycurrentD
>loop until fatherID = 0
> query the query to get record where fatherID = tempID
> set tempID = querythequery.uniqueD
>endloop
>
>
> That's the basic algorithm (off the top of my head), but I bet you
>already knew that.
>
>At 10:30 AM 05/17/2001 -0400, you wrote:
>>If anyone can solve this one, I'll give you a million
>>bucks. Or maybe not, but I'll certainly be impressed...
>>
>>Let's say, for instance, I'm storing data about a family
>>tree. Every person has a record in the People table. Every
>>person has a Mother and a Father, linked with their unique
>>ID.
>>
>>Now the challenge: How can I create the list of all male
>>ancestors, all the way back to the root level (i.e. Adam &
>>Eve)? I've managed to do this with a CFLOOP that keeps
>>running a query, going up the tree, until the FatherID is
>>zero, but this is expensive on the database end.
>>
>>For the record, I'm not actually keeping track of people
>>all the way back to Adam & Eve, but this is the best way to
>>explain this database design issue.
>>
>>Thank you very much!
>>
>>Norman Elton
>>Jefferson Labs
>>
>>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
