Grabbing the max ID will often work just as well but it absolutely
necessitates the use of CFTRANSACTION around your INSERT and SELECT
Max(ID) queries. If the table of interest grows to contain many records
it's likely to eventually take a lot longer to return the max ID than it
would to read and update a tiny Next_Nbr table (as if Access isn't doggy
enough!). Often you only really need to use CFTRANSACTION around the
SELECT and UPDATE queries acting upon Next_Nbr as long as you don't need
the rollback protection on your INSERT query on a big table. In that
case the worst outcome is that you might skip an ID value if the INSERT
query fails and Next_Nbr has been incremented (I've never seen that
happen but it's possible), so if that's not an issue you get better
performance.
Sometimes it will certainly matter more than others, and maybe we'd be
splitting hairs most of the time even worrying about it. However,
having used a Next_Nbr table for this purpose with never any problems I
regard it as much 'cleaner' than the 'workaround' of querying a table to
get a max ID for the record last inserted and having a large table
locked for the entire duration of both the INSERT and SELECT Max(ID)
operations. The major advantage of using a Next_Nbr table instead of
the max ID therefore is that it is always fast, not just sometimes fast.
If you build NEXT_NBR.cfm to SELECT and then UPDATE a record in the
Next_Nbr table you can just call it as a custom tag like so:
<CF_NEXT_NBR
DATASOURCE="#DataDB#"
FIELDNAME="NUID"
>
<CFSET NUID = Variables.NextNbr>
Now you can use Variables.NUID in any following INSERT queries very
easily and reliably.
Regards,
Karl Simanonok
Original Message:
============
Date: Mon, 24 Jul 2000 10:48:49 -0700
From: Dan Haley <[EMAIL PROTECTED]>
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
Subject: RE: Newbie needs a little PRO help
Message-ID: <[EMAIL PROTECTED]>
How does this approach improve upon pulling the max id after an insert?
If
<cftransaction> will keep two requests from pulling the same NextNbr
before
the first request has updated it, why won't it work with the max id
method?
The max id method is much cleaner without the need for a work around.
Maybe I missed something . . . if so . . . whoops . . .
Dan
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
