As I understand it, that would mean that "apple" has two parents ... "computers" and "food" ... which would not be allowed

Quote from Jacob in Page 2:

"It is possible to have 'apple' in food and computers."

Hello, my stats:

(5007 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 1041, physical reads 9, read-ahead reads 0, lob logical reads 1648, lob physical reads 6, lob read-ahead reads 0. Table 'TC60'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 39, lob logical reads 3501192, lob physical reads 34, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 250330 ms, elapsed time = 286668 ms.

(I'm working in VBOX on a slow computer)

1 Computers NULL 2 Food NULL 3 x NULL 4 Apple 1 5 Apple 2 6 a11 3 7 a101 3 8 a1001 3 9 a111 3 10 a121 3 (...) 5000 aaaaaaaaa10097 4996 5001 a9797 4997 5002 a9897 4998 5003 aaaaaaaaa9997 4999 5004 aaaaa10098 5000 5005 aaaaa9998 5003 5006 a10099 5004 5007 a99_99 5005

Table 'spt_values'. Scan count 5, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TC60'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 16 ms, elapsed time = 15 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

Can anyone send me the test data that you guys are using??

@pierre

look at first page of comments, its in one of leszek posts

1
Computers
NULL
2
Food
NULL
3
x
NULL
4
Apple
1
5
Apple
2
6
a1_1
3
7
a10_1
3
8
a100_1
3
9
a11_1
3
10
a12_1
3




4998
aaaaa98_96
4896
4999
aaaaaaaaaaaaa99_96
4897
5000
aaaaaaaaa100_97
4898
5001
a97_97
4899
5002
a98_97
4900
5003
aaaaaaaaa99_97
4901
5004
aaaaa100_98
4902
5005
aaaaa99_98
4903
5006
a100_99
4904
5007
a99_99
4905

(5007 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tsqlc_Tally'. Scan count 102, logical reads 453, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TC60'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 2623, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 5484 ms, elapsed time = 5565 ms.

@Pierre - my v1 result is wrong, as your

4998 aaaaa98_96 4994

4999 aaaaaaaaaaaaa99_96 4995

5000 aaaaaaaaa100_97 4996

5001 a97_97 4997

5002 a98_97 4998

5003 aaaaaaaaa99_97 4999

5004 aaaaa100_98 5000

5005 aaaaa99_98 5003

5006 a100_99 5004

5007 a99_99 5005

Stats are not so cool but result looks fine

(5007 row(s) affected)

Table 'Worktable'. Scan count 1, logical reads 1274668, physical reads 0, read-ahead reads 0, lob logical reads 912474, lob physical reads 0, lob read-ahead reads 0. Table 'tsqlc_Tally'. Scan count 204, logical reads 1880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC60'. Scan count 2, logical reads 78, physical reads 0, read-ahead reads 0, lob logical reads 684972, lob physical reads 0, lob read-ahead reads 451964.

SQL Server Execution Times: CPU time = 24843 ms, elapsed time = 24881 ms.

Got better result

(5007 row(s) affected) Table 'Worktable'. Scan count 4905, logical reads 50053, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tsqlc_Tally'. Scan count 204, logical reads 1880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC60'. Scan count 2, logical reads 78, physical reads 0, read-ahead reads 0, lob logical reads 684972, lob physical reads 0, lob read-ahead reads 451964.

SQL Server Execution Times: CPU time = 8500 ms, elapsed time = 8609 ms.