I didn't say they were *the* problem, bucko. The custom tag architecture and
insane overhead *were* the main problems.
But no matter how you test it, UUIDs are slower than integers for pkeys.
Spectra makes TONS of entries in the objects table even for a moderate-sized
application. If Allaire was making an app for only SQL Server, only for NT,
then they would have used auto integers. But because they had to make it run
on a zillion different dbms, they had to use UUIDs.
Again, review the business requirements for your application and if you know
you'll be with one DB forever and can handle increased costs if your DB
needs porting, then use auto ints. Otherwise, investigate UUIDs.
NAT
----- Original Message -----
From: "Steve Nelson" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2001 11:23 AM
Subject: Re: max_id or autonumber?
> The UUIDs weren't the problem, it was silly ass architecture that
> spectra used and often because of poorly written CFML in the spectra
> codebase.
>
> Try a test, compare a search on a million records in SQL server, do one
> search with an indexed integer PK, and another with an indexed text
> string.
>
> Steve
>
> Nat Papovich wrote:
> >
> > 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
