For anyone who has heard of Spectra, note that it uses UUIDs for Primary
Keys. I'm sure we've all heard of or experienced first-hand Spectra's
incredible slowness. This is due (in small part) to using UUIDs for pkeys.
NAT
----- Original Message -----
From: "Steve Nelson" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2001 8:16 AM
Subject: Re: max_id or autonumber?
> It's not that dbs don't support autonumbers it's that the different dbs
> deal with autonumbers differently. So if you ever switch it's a pain in
> the Caboose.
>
> Yes, integers are faster, but you're not going to notice any difference
> between an indexed integer field and an indexed text field until you hit
> 50-100 concurrent users.
>
> Let me put this in perspective....
>
> If you assume ceteris paribus (all things being equal) Let's say every
> page on your site has a single query (i know most have more, others have
> none, let's just assume 1 query per page)
>
> If you have 1 concurrent user EVERY second (again, i understand peak
> load times, ignore that for now) looking at a single page on your site,
> that's 60 seconds in a minute, 60 minutes in an hour 24 hours a day.
> 86,400 requests in a single day. Now multiple that by 50 (when you'll
> see a difference between integers and text fields) That's 4,320,000
> requests in a single day.
>
> The question you should ask yourself.... do i get this many requests a
> day? If so, it would make sense to do some more detailed research before
> making a decision on integers vs. text strings.
>
> The other factor which is more important is that the performance
> difference between text strings and integers is negligible compared to
> the difference of 4.3 million transaction locks a day versus 0.
> (Autonumbers perform transaction locks behind the scene, don't be
> fooled)
>
> Steve Nelson
>
>
>
> Ken Beard wrote:
> >
> > which db is it that doesn't support autonumber type fields?
> > also, my dba's here tell me that integers search faster (with indexes)
or
> > some such nonsense.. 2cents.
> >
> > At 08:41 AM 5/31/01 -0400, you wrote:
> > >If you're unsure about the max_id stuff.... try UUIDs.
> > >
> > ><cfquery ... >
> > > INSERT INTO table(ID, Field1, Field2 etc)
> > > VALUES('#createuuid()#','#Field1#', '#Field2#')
> > ></cfquery>
> > >
> > >No transaction needed and it's database independent. As long as you've
> > >got a clustered index on that table you won't have any speed problems
> > >with searches even though it's a text string.
> > >
> > >The only issue which is only a psychological issue is the fact that the
> > >PKs will be 35 characters long.
> > >
> > >Steve Nelson
> > >
> > >Ross Keatinge wrote:
> > > >
> > > > Hi folks
> > > >
> > > > I know this is an old recurring discussion but I still can't find a
> > > > good answer in the various CF mailing lists.
> > > >
> > > > I'm soon to start building a new web application with CF and XFB.
I'm
> > > > convinced that meaningless integer primary keys for db tables are
the
> > > > way to go (although that is another discussion in itself). Our
database
> > > > is MS SQL Server 7. I'm still tossing up the question of whether to
use
> > > > the db provided identity functionality or roll my own using
something
> > > > like Steve's cf_max_id tag. I think identity fields work fine in SQL
> > > > Server 7 and they are certainly tempting. However, I like the
feeling
> > > > of 'freedom' from db dependent features so a normal int field with
> > > > SELECT MAX(ID) is appealing to some extent. As Steve's docs say,
moving
> > > > to another db is a lot easier without autonumbers.
> > > >
> > > > The recommended use of cf_max_id results in something like this:
> > > >
> > > > <cftransaction>
> > > > <cfquery name="GetMaxID">
> > > > SELECT MAX(ID) FROM table
> > > > </cfquery>
> > > >
> > > > <cfquery>
> > > > INSERT INTO table
> > > > (ID, Field1, Field2 etc)
> > > > VALUES(#GetMaxID.Max_D#,'#Field1#', '#Field2#')
> > > > </cfquery>
> > > > </cftransaction>
> > > >
> > > > Is that really safe in a multi threaded environment? It doesn't look
> > > > like it to me. As far as I know <cftransaction> doesn't stop me
losing
> > > > the CPU between the two queries. If I am interupted there, what
would
> > > > happen to a second page hitting the same code? Would it just hang at
> > > > the cftransaction or what? What odbc isolation lock type is
> > > > appropriate? I know cflock would fix it but that seems like a bad
idea
> > > > or is it really any worse?
> > > >
> > > > Any comments would be appreciated.
> > > >
> > > > Thanks
> > > > Ross
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
