Our production database (Oracle) has hundreds of millions of records. The DB
is partitioned by month and you need to join tables by compound keys not
just the one identity column. I can't see the use of UUID being "heavier"
than our compound key method...and our DB still returns excellent
performance.
Also a grain of salt here because you should see this machine. *phew* 6
processor beast. Disk arrays everywhere...dayam! ;)
-----Original Message-----
From: Paul Smith [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 8:59 PM
To: Fusebox
Subject: Re: max_id or autonumber?
The technique/CF Code I posted earlier (where you store the current MaxID
in a table with one row) was taken from a Microsoft recommendation/article
a few years ago (during SQL 6, I believe).
best, paul
At 12:34 PM 5/31/01 -0700, you wrote:
>Derek,
>
>Can you recommend a good alternative to the identity seed feature in
>SQL 7 for coming up with Unique ID fields?
>
>Brian
>
>----- Original Message -----
>From: "Derek Hamilton" <[EMAIL PROTECTED]>
>To: "Fusebox" <[EMAIL PROTECTED]>
>Sent: Thursday, May 31, 2001 10:23 AM
>Subject: Re: max_id or autonumber?
>
>
> > Another issue with the identity feature of SQL Server 7 (not sure
> > about
>2000
> > however) is that the identity is tracked by the internal system
> > tables of SQL Server. If your server ever crashes or whatever then
> > those tables
>could
> > be reset and you would run into a lot of errors or duplicate keys
> > because the identity would use values that had previously been used.
> > Microsoft doesn't even recommend that you use the identity feature
> > for a Unique ID field because of this.
> >
> > Derek
> >
> > ----- Original Message -----
> > From: "Marsh, Jeffrey B" <[EMAIL PROTECTED]>
> > To: "Fusebox" <[EMAIL PROTECTED]>
> > Sent: Thursday, May 31, 2001 9:11 AM
> > Subject: RE: max_id or autonumber?
> >
> >
> > > If SQL 7 is your database, use stored procedures. Stay away from
> > > in-line
> > SQL
> > > as much as possible. Let the database do the work it is supposed
> > > to do. Don't let the web server do work it doesn't need to do.
> > > Whether you use identity seeds or not, the store procedure can
> > > figure out the next key
>to
> > > use in a table. Using maxID and then doing an insert makes
> > > unnecessary
> > trips
> > > to the database. Make every trip to the database count.
> > >
> > > ---
> > > Jeffrey B. Marsh
> > > Professionals built the Titanic.
> > > Amateurs built the Ark.
> > >
> > > -----Original Message-----
> > > From: Ross Keatinge [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, May 30, 2001 4:43 PM
> > > To: Fusebox
> > > Subject: max_id or autonumber?
> > >
> > > 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
