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
