Re: Serious article on comparison between MS SQL Server 2005 Yukon and Oracle 10g

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 22 Nov 2004 21:43:42 -0800
Message-ID: <1101188533.245859@yasure>

Jonathan Leffler wrote:

> DA Morgan wrote:
>

>> Mladen Gogala wrote:
>>
>>> Finally, the debate that got my eyebrows raised was the discussion of 
>>> the identity field vs. sequences, claiming sequences to be a "major 
>>> overhead".
>>>
>>> Well, sequences are certain (but by no means a major one)
>>> overhead because they allow things like clustering (see "ORDERED"
>>> and "NOCACHE") as well as good caching for performance. The
>>> article doesn't say anything about he performance of the identity
>>> fields in the clustered environment.
>>
>>
>> Actually identity columns have more of an impact on performance
>> than do sequences which is why IBM immediately added sequences to
>> Informix. The sad fact of identity columns is that they force
>> serialization.

>
>
> Informix SERIAL columns do not force serialization - for any reasonable
> meaning of serialization with which I am familiar.
>
> Suppose two transactions, TxA and TxB, both insert a record into a table
> with a serial column. For sake of argument, TxA does so first and is
> allocated number 1000. TxB is allocated number 1001 (under normal
> circumstances), regardless of whether TxA has committed or rolled back
> or is still active. The values are assigned without more than a
> transitory latch to permit the serial counter value to be updated.
> Suppose TxA rolls back. TxB may still commit (or rollback). Any
> subsequent transaction TxC will be allocated 1002, regardless of whether
> TxA or TxB or both rollback. Of course, either TxA or TxB or both may
> insert multiple records into the table with the serial column, or into
> any other tables; there are no petty restrictions for having acquired a
> new serial number.

A "transitory" latch is still serialization. That is substantially different than a cached sequence.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Mon Nov 22 2004 - 23:43:42 CST