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
