I hear larger (disk space) table take more time to search.
At 01:56 AM 6/1/01 -0400, you wrote:
>********************** 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
