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

On 21 May 2004 12:24:55 -0700, Neo wrote:

Hi Neo,

>> 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

Executing in tempdb is not a good idea. SQL Server uses tempdb to store temporary tables and intermediate results. Use a seperate test database.

>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

The sudden boost in execution time for the second run raises the question: were you the only one using SQL Server at that moment? Were other processes active on the server. You might have executed simultaneously with some other process (maybe even one of SQL Server's own housekeeping processes).

>
>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
>10.623 10.797 174 4th conseq run
>
>What might I be doing wrong to get results different than yours?

Hard to diagnose from here, but see my other message as well.

>
>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

The execution time with XDb1 printing IDs only is not relevant to this challenge. The challenge was to generate a report printing names.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)