Kan någon effektivisera detta ytterliggare (och gärna komma förbi hindret på 32 djup i rekursiviteten). Jag har mekat med det ett tag nu och nu har det tagit slut på fantasin!

Har följande tabell:

CREATE TABLE [thr_threads] (
[pst_id] [int] NOT NULL ,
[pst_id_answer] [int] NOT NULL ,
[thr_isreferenced] [bit] NOT NULL,
[thr_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

Och följande SP's:


CREATE PROC GetThreads
@pst_id int
AS
CREATE TABLE #Threads
(
[pst_id] int,
[pst_id_answer] int,
[thr_isreferenced] bit,
[thr_date] datetime,
[thr_depth] int
)
EXEC GetThread @pst_id
SELECT DISTINCT * FROM #Threads

GO

CREATE PROC GetThread
@pst_id int
AS
SET NOCOUNT ON

DECLARE @pst_id_next int, @count int

DECLARE Thread CURSOR LOCAL FOR
SELECT [pst_id_answer]
FROM [thr_threads]
WHERE pst_id = @pst_id

OPEN Thread

FETCH NEXT FROM Thread
INTO @pst_id_next

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Threads ([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date], [thr_depth])
SELECT *, (@@NESTLEVEL - 1) AS thr_depth FROM thr_threads WHERE pst_id = @pst_id
IF (SELECT ISNULL(COUNT(*),0) FROM thr_threads WHERE pst_id IN (SELECT pst_id_answer FROM thr_threads WHERE pst_id = @pst_id)) > 0
BEGIN
EXEC GetThread @pst_id_next
END
FETCH NEXT FROM Thread
INTO @pst_id_next
END
CLOSE Thread
DEALLOCATE Thread

GO

Lite data vore inte fel. Så man ser hur strukturen är tänkt.

lite data:
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 11241, 0, '2002-09-24 14:19:08.893')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 11254, 0, '2002-09-24 14:19:08.903')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 11299, 0, '2002-09-24 14:19:05.020')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 11419, 0, '2002-09-24 14:19:04.950')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 11498, 0, '2002-09-24 14:19:08.683')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 12081, 0, '2002-09-24 14:19:05.030')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 12090, 0, '2002-09-24 14:19:05.010')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11233, 12102, 0, '2002-09-24 14:19:05.570')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11241, 11255, 0, '2002-09-24 14:19:06.010')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11299, 11304, 0, '2002-09-24 14:19:04.950')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11299, 11348, 0, '2002-09-24 14:19:05.000')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11304, 11337, 0, '2002-09-24 14:19:04.890')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11337, 11347, 0, '2002-09-24 14:19:04.880')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11337, 11349, 0, '2002-09-24 14:19:04.890')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer], [thr_isreferenced], [thr_date])
VALUES (11348, 11365, 0, '2002-09-24 14:19:04.870')
INSERT INTO [thr_threads]([pst_id], [pst_id_answer],[thr_isreferenced], [thr_date])
VALUES (11498, 11519, 0, '2002-09-24 14:19:08.683')

Pice of cake.

För att komma förbi hindret med begränsningen av rekursivitet är det ju bara att låta bli att vara rekursiv.

Jag angriper datan nivå vis. Vilket innebär mindre Insert-satser. Vilket borde ge en bättre prestanada. Du skulle ju kuna använda index för att slippa många av de Table Scan som utförs.

Skippat cursor. Eftersom det inte längre behövs. Dessutom är det ju långsamt har jag hört. Inte hunit jämföra själv.

Behöver du verkligen DISTINCT på Select-satsen mot #Threads? Den drar ju mycket prestanda när den sorterar urvalet:

CREATE PROC GetThreads_The_AH_Way
(
@pst_id int
)
AS

CREATE TABLE #Threads
(
pst_id int,
pst_id_answer int,
thr_isreferenced bit,
thr_date datetime,
thr_depth int
)

DECLARE @thr_depth int

SET @thr_depth = 0

INSERT INTO #Threads (pst_id, pst_id_answer, thr_isreferenced, thr_date, thr_depth)
SELECT pst_id, pst_id_answer, thr_isreferenced, thr_date, @thr_depth
FROM thr_threads
WHERE pst_id = @pst_id

WHILE EXISTS (SELECT Null FROM #Threads WHERE thr_depth = @thr_depth)
BEGIN
INSERT INTO #Threads (pst_id, pst_id_answer, thr_isreferenced, thr_date, thr_depth)
SELECT pst_id, pst_id_answer, thr_isreferenced, thr_date, @thr_depth + 1
FROM thr_threads
WHERE pst_id IN (SELECT pst_id_answer FROM #Threads WHERE thr_depth = @thr_depth)

SET @thr_depth = @thr_depth + 1
END


SELECT * FROM #Threads

DROP TABLE #Threads

Ahhh, mycket smart Andreas!

Det här var mitt första försök att skriva en rekursiv fråga i sql så jag får skylla på det.

Ja, jag behövde använda distinct, det beror på att när jag rekurserade så fick jag dubbla träffar av någon anledning som jag inte har rett ut än till 100%...

Jag är själv nöjd med proceduren.

Skulle vara roligt om man kan förbättra den yttrligare. Eller göra en bättre lösning på ett annat sätt.

Se det som en utmaning Christoffer Hedgate och alla andra experter. ;o)

Kanske inte snabbare (har inte testat) men en annorlunda lösning:

CREATE FUNCTION funcGetThread (@pst_id int)
RETURNS @CurrentThread TABLE ([pst_id] int,
[pst_id_answer] int,
[thr_isreferenced] bit,
[thr_date] datetime, [thr_depth] int)
AS
BEGIN
DECLARE @thr_depth int

SET @thr_depth = 1

INSERT INTO @CurrentThread (pst_id, pst_id_answer, thr_isreferenced, thr_date, thr_depth)
SELECT pst_id, pst_id_answer, thr_isreferenced, thr_date, @thr_depth
FROM thr_threads
WHERE pst_id = @pst_id

WHILE EXISTS (SELECT Null FROM @CurrentThread WHERE thr_depth = @thr_depth)
BEGIN
INSERT INTO @CurrentThread (pst_id, pst_id_answer, thr_isreferenced, thr_date, thr_depth)
SELECT pst_id, pst_id_answer, thr_isreferenced, thr_date, @thr_depth + 1
FROM thr_threads
WHERE pst_id IN (SELECT pst_id_answer FROM @CurrentThread WHERE thr_depth = @thr_depth)

SET @thr_depth = @thr_depth + 1
END

INSERT INTO @CurrentThread (pst_id, pst_id_answer, thr_isreferenced, thr_date, thr_depth)
SELECT [pst_id] AS pst_id, [pst_id] AS pst_id_answer, 0 AS thr_isreferenced, [pst_date] AS thr_date, 0 AS thr_depth
FROM [pst_posts] WHERE pst_id = @pst_id

RETURN
END

Ser att du gjort det till en funktion som retunerar en Tabell variabel.