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

On Tue, 01 Jun 2004 16:33:34 +1000, thirdrock wrote:

>On Sun, 30 May 2004 00:24:48 +0200, Hugo Kornelis
><hugo_at_pe_NO_rFact.in_SPAM_fo> wrote:
>
>> On 29 May 2004 11:18:22 -0700, Neo wrote:
>
>> No, they are not. There is no such thing as a "RAM table" in MS SQL
>> Server.
>
>Yes there is. It's called a temporary table. Really fast too, but does not
>get flushed to disk at any point.

Hi Ian,

That is not correct. MS SQL Server stores temporary tables in a special database, tempdb. I just tried what happened when I create a temporary table, fill it with lots of rows (the joys of cross join <g>) and keep the connection open. Sure, when I checked the size of tempdb had grown to a little over 2 GB and more almost half of it was used (the other half had been used for logging the operation; since the transaction was committed, this part was free again). After closing the connection, the temp table was automatically dropped and the amount of space used in tempdb was back to normal.

If the temp table is small enough to fit in memory and it is dropped soon after being created, then the data may be gone from the cache before being flushed to disk; the log records will still be written to disk (logging for temp tables is reduced, not completely avoided).

>Still, what was the point you were trying to make?

None - I was answering a question Neo asked.

(snip)
>This was versionb
>6.5 so things may have improved since then.
>Every other database I tested the same update on beat this performance by
>at least 50%, Oracle was 100% faster. Even Access was faster, but once the
>record count went above a million, Access started crashing and corrupting
>data.

Not sure about the differences between 6.5 and 2000 myself either. Too bad I don't have Oracle at hand - from what you write I gather that I might have beaten Neo's challenge even more using Oracle!

(snip)

Best, Hugo

-- 

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