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

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 23 Nov 2004 05:04:02 GMT
Message-ID: <6xzod.2720$uV6.1291@newsread3.news.pas.earthlink.net>

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.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Received on Mon Nov 22 2004 - 23:04:02 CST