Fabio,

You're aboslutely right that this code is easy and fast. However, If you compare resource consumption it would be a huge factor compared to a RECORD ORIENTED approach. This is because it essentially launches a new SQL statement for each level with all kinds of overhead. Now in most modern RDBMSs it would be able to reuse the execution plan, but nontheless you're executing overhead that does not exist in a RECORD ORIENTED approach.

In a record ORIENTED APPROACH, you'd directly talk to the index and seek the right record with explicitely telling the DBMS how to do it: No execution plan needed. No need to take care of a situation that the SELECT returns multiple rows.

Though both in VFP and SQL this would be quick, but It would not surprise me that the RECORD ORIENTED approach is many times faster than the SQL approach, just because of bypassing the overhead the SQL engine has to take.

In VFP

nSec = SECONDS()

CREATE CURSOR RECURSIVE (ID I , ID_PARENT I NULL)
INSERT INTO RECURSIVE VALUES (1,NULL)
INSERT INTO RECURSIVE VALUES (2,NULL)
INSERT INTO RECURSIVE VALUES (3,2)
INSERT INTO RECURSIVE VALUES (4,1)
INSERT INTO RECURSIVE VALUES (5,2)
INSERT INTO RECURSIVE VALUES (6,3)
INSERT INTO RECURSIVE VALUES (7,5)
INSERT INTO RECURSIVE VALUES (8,6)
INSERT INTO RECURSIVE VALUES (9,8)

INDEX ON ID TAG ID
CLEAR


nBoss = 9
FOR nT = 1 TO 10000
    SEEK nBoss
    DO WHILE SEEK(id_parent) AND !ISNULL(id_parent)
    ENDDO
ENDFOR
nTime = SECONDS() - nSec
? nTime, ID

In SQL:

CREATE TABLE #RECURSIVE (ID INT PRIMARY KEY, ID_PARENT INT NULL)
INSERT INTO #RECURSIVE VALUES (1,NULL)
INSERT INTO #RECURSIVE VALUES (2,NULL)
INSERT INTO #RECURSIVE VALUES (3,2)
INSERT INTO #RECURSIVE VALUES (4,1)
INSERT INTO #RECURSIVE VALUES (5,2)
INSERT INTO #RECURSIVE VALUES (6,3)
INSERT INTO #RECURSIVE VALUES (7,5)
INSERT INTO #RECURSIVE VALUES (8,6)
INSERT INTO #RECURSIVE VALUES (9,8)

DECLARE @nT int
DECLARE @idBoss INT
SET @nT = 1

WHILE @nT < 10000
BEGIN
     SET @idBoss = 9
     WHILE @@ROWCOUNT = 1
   	SELECT @idBoss = ID_PARENT
		FROM #RECURSIVE WHERE ID=@idBoss AND ID_PARENT IS NOT NULL
     SET @nT = @nT + 1
END
SELECT @idBoss
	
DROP TABLE #RECURSIVE

Do your math..

Walter,









>I cannot to see a big difference with this SQL 2000 code
>

>CREATE TABLE #RECURSIVE (ID INT PRIMARY KEY, ID_PARENT INT NULL)
>INSERT INTO #RECURSIVE VALUES (1,NULL)
>INSERT INTO #RECURSIVE VALUES (2,NULL)
>INSERT INTO #RECURSIVE VALUES (3,1)
>INSERT INTO #RECURSIVE VALUES (4,1)
>INSERT INTO #RECURSIVE VALUES (5,2)
>INSERT INTO #RECURSIVE VALUES (6,3)
>INSERT INTO #RECURSIVE VALUES (7,5)
>INSERT INTO #RECURSIVE VALUES (8,6)
>INSERT INTO #RECURSIVE VALUES (9,8)
>
>-- FIND BOSS
>DECLARE @idBoss INT
>SELECT @idBoss = 9
>WHILE @@ROWCOUNT = 1
>	SELECT @idBoss = ID_PARENT
>		FROM #RECURSIVE WHERE ID=@idBoss AND ID_PARENT IS NOT NULL
>
>SELECT @idBoss
>-- END FIND
>
>DROP TABLE #RECURSIVE
>

>
>Hierarchical query/subquery in SQL 2000 is not very hard.
>
>Fabio
>
>>Now just implement the first query in both VFP and SQL and compare resource consumption. BTW, the rest of the excersize should be very familiar to you too.