Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)

> Starting time Ending time Time Elapsed
> ------------- ------------ ------------
> 14:44:57.670 14:44:57.733 67 ms
> 15:02:26.233 15:02:26.297 64 ms
> 15:07:57.780 15:07:57.843 63 ms

Yesterday's results were all 1st runs after running the provided script in a new db each time (on NT 4, SQL Server 7, with sp6). Just to double check, I ran the script again today.

Execute script in existing system tempdb: Start End Elapsed Comment

------  ------  -------  ----------------
16.860  17.013     153   1st run
03.013  03.047      34   2nd conseq run
31.717  31.750      33   3rd conseq run
48.920  48.950      30   4th conseq run

Drop relevant tables.
Execute script in existing system tempdb: Start End Elapsed Comment

------  ------  -------  ----------------
06.623  06.780    157    1st run
29.297  30.483  1,186    2nd conseq run (yes 1.186 seconds)
05.577  05.610     33    3rd conseq run

Execute script in brand new Test db:
Start   End     Elapsed  Comment
------  ------  -------  ----------------
06.687  06.733     46    1st run
47.640  47.700     60    2nd conseq run
06.343  06.403     60    3rd conseq run
31.000  31.060     60    4th conseq run

Drop Test db.
Executed script in new Test db:
Start End Elapsed Comment

------  ------  -------  ----------------
32.403  32.450     47    1st run
34.577  34.640     63    2nd conseq run
56.153  56.217     64    3rd conseq run

What might I be doing wrong to get results different than yours?

Below are 5 conseq runs with XDb1 printing IDs only, thus approximating your solution which does not link to other tables in order to generate the report. Yes this not a fair comparison as char keys are slower than numerical keys.
It is only meant to be a very rough comparison.

  2.23 ms
  2.16 ms
  2.04 ms
  2.18 ms
  2.15 ms