none of them are worth a performance penalty of ANY amount. And if you're
using any vendor-specific SQL, then using the built-in autonumbering is the
way to go.
If you want to do application-side autonumbering, there are better ways.
Track your maxID in some kind of syncronized store and use that, rather than
computing it on every INSERT. That'll save you from having to do that
potentially very time consuming query.
The easiest solution is to use a file on the file system, though that has
problems with scalability. A better solution is probably to have a table in
your DB like this:
Create table keyGenerator (
table char(30), /* fixed width for speed */
currentMaxKey int unsigned not null,
primary key (table)
);
Then make a function stored in the application scope (if you're on CFMX)
which takes a table name and returns the next available key, incrementing
the database's value as well. Inside the function use an exclusive named
lock so you don't get duplicates returned. Do NOT put the call to it inside
your CFTRANSACTION block. You you need zero concurrency for that table, and
transactions are designed to do exactly the opposite.
Just add a row in the keyGenerator table for each table in your DB that
needs an autoincrement column, and away you go. I've never implemented this
in practice (DB-specific autonumbering isn't a big deal to me), but it
should work a lot faster than CF_MAX_ID.
Cheers,
barneyb
> -----Original Message-----
> From: Spectrum WebDesign [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 06, 2004 10:14 AM
> To: CF-Talk
> Subject: RE: CF_MAX_ID - MySQL slowww
>
> Jochem
>
> this the Max_ID CustomTag code (really simplest):
> <!--- required attributes --->
> <cfparam name="attributes.datasource">
> <cfparam name="attributes.tablename">
> <cfparam name="attributes.primarykey">
>
> <cfquery name="getmaxID" datasource="#attributes.datasource#">
> select max(#attributes.PrimaryKEY#) as Max_ID
> from #attributes.tablename#
> </cfquery>
>
> <cfif len(getmaxID.Max_ID)>
> <cfset caller.Max_ID = getmaxID.Max_ID + 1>
> <cfelse>
> <cfset caller.Max_ID = "1">
> </cfif>
>
> --------------------------
> Barney
>
> why don't use AUTONUMBER? Please see:
>
> This tag is used as a "application side autonumber" In other
> words, instead of using the
> "autonumber" A.K.A. "identity key" as your primary key
> datatype in your database, you would
> use a simple number datatype.
>
> Why the hell would you want to do this?
>
> Because autonumber datatypes are not easily transferable from
> database to database. They are also
> a pain in the neck to develop with, because once the record
> has been created that number can never
> be used again. This technique is not 100% necessary for
> Fusebox applications, but after
> you play with autonumbers and try and move data from one
> database to another you'll thank me.
>
>
> But my query still very slowwwwww....
>
> Do you have another option to Max_ID????
>
>
>
>
> ----- Original Message -----
> From: "Barney Boisvert" <[EMAIL PROTECTED]>
> Date: Tue, 6 Jan 2004 09:39:34 -0800
> To: CF-Talk <[EMAIL PROTECTED]>
> Subject: RE: CF_MAX_ID - MySQL slowww
>
> Last time I checked, CF_MAX_ID simply did a SELECT MAX() on
> the table, and
> added one to the result.
>
> If you haven't already, make sure that you have an unique
> index (key) on the
> intNewsID column. If it's your primary key, then why aren't you using
> MySQL's AUTO_INCREMENT column type? It will automatically
> handle making
> unique primary keys for you.
>
> Create table tblNews (
> intNewsID int unsigned not null auto_increment,
> ...,
> primary key (intNewsID)
> );
>
> Cheers,
> barneyb
>
> > -----Original Message-----
> > From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, January 06, 2004 8:21 AM
> > To: CF-Talk
> > Subject: Re: CF_MAX_ID - MySQL slowww
> >
> > Spectrum WebDesign said:
> > >
> > > <!---get MaxID form DB--->
> > > <cfloop condition="true">
> > > <cftry>
> > > <cftransaction>
> > > <cf_max_ID
> > > datasource="#dsn#"
> > > tablename="tblNews"
> > > primarykey="intNewsID">
> >
> > It would help if we had the code for cf_max_id and if you
> placed some
> > timers for the different sections.
> >
> > Jochem
> >
> >
> >
> >
> >
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
