********************** we're switching listservs
**********************
**********************
[EMAIL PROTECTED]**********************
I agree with all the theories you're saying. The issue is that my stupid
little tests I just ran shows different results from these theories.
They show that UUIDs are faster! I'm a scientific kind of guy, that
makes me want to see theories in a simulated test.
Disk space is not an issue. 44 megs vs 14 megs? Let's look at the costs
of this.
A Barracuda 30 gig hard disk costs $119. (that was the first one i found
at outpost.com), boil that down to $s/meg and its ~3 cents per meg. So
let's see.... by using UUIDs, on a million records we spend $1.74 vs
integers which costs us $0.55. Hell multiple those number by 10 and buy
a decent SCSI hard disk.
Come on!! It cost me more to write this email! Disk space cost is not a
factor for the general user, and the folks out there like Stacy Young
who is hammering hundreds of millions of records is probably given a
much bigger budget anyway and is dealing with terabyte disks not
gigabyte disks. Stacy, correct me if I'm wrong.
I hate to be the one to drone on about this, but i absolutely HATE
identity keys, they're a pain in the neck to work with. The thing that
interests me the most about UUIDs is the fact that you shouldn't ever
get deadlocks because the db doesn't have to lookup any previous data.
So if tests show that the speed is not drastically different, I'd go
with the UUIDs for the deadlock solution.
Does anyone out there have a stress tester we could use to run
simulations of a bunch of concurrent users? I don't have any way to test
that.
Let's document these tests and make them available on Fusebox.org.
Steve Nelson
>
> UUID bad:
>
> - An integer datatype in SQL Server takes 4 bytes of storage space, the
> space required to store a UUID is 35 bytes. The primary key of a
> database table (in SQL Server) automatically has a unique index created
> on it. This index is ((35 + 9) * number of rows in the table)bytes long
> when using a UUID as a primary key. So if the table were to have a
> million rows, your looking at an index that is 44 megs in size, vs. 14
> megs in size for the integer primary key.
> - If the primary key of the table is also the clustered index, each
> subsquent index created on the table will be the new index size + the
> clustered index size, again wasting space.
> - Primary keys are almost always invovled with joins of other tables. 35
> character join column = bad.
> - The whole point of using a surrogate primary key such as an identity
> column of sequence is when the intellegent primary key of the table is
> long enough to impair performance. A 35 character string column is long
> enough to impair performance when dealing with a million row table, so
> you are defeating the whole point of using a surrogate primary key.
>
> Auto Number (identity, sequence, etc.) bad:
>
> - Portablility. Good luck rewriting a major portion of your application
> code if your company decides to move from SQL Server to Oracle or to
> something else. Using intellegent primary keys ensures that your code
> isn't full of platform specific autonumber functions.
>
> - Identity columns defined with clustered indexes cause hot spots on a
> physical disk. If you use one as a primary key, don't make it the
> clustered index.
>
> My advice, avoid both of them. It's beyond rare that a 3rd normal form
> table will not have at least one candidate primary key, compound or not.
> Save headaches in the future by avoiding surrogate keys altogether. But
> if I had to pick, smaller is better and smaller is faster. Go with the
> autonumber.
>
> Jeff
>
> ==^================================================================
> EASY UNSUBSCRIBE click here: http://topica.com/u/?bUrFMa.bU4xGZ
> Or send an email To: [EMAIL PROTECTED]
> This email was sent to: [EMAIL PROTECTED]
>
> T O P I C A -- Register now to manage your mail!
> http://www.topica.com/partner/tag02/register
> ==^================================================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
