Nick,
Agreeing with you completely regarding the overhead of a DB call.
I find it very interesting that people don't recommend the use of UUID
as DB keys. In this way, using CreateUUID, CF would already know the
key value and the DB INSERT would proceed on it's own.
The suggestion, which is often made, that the use of the MAX() function
is a simple call, are making significant assumptions about the DB
engines which might be used. While SQL Server might treat MAX() of the
key in an optimized fashion, not all DBs would or do (I know of at least
one). Equally, the additional DB call to obtain the MAX() value,
increases the length of the transaction which can introduce
concurrency/locking issues (not to be overlooked in high volume
applications).
What am I missing?
Sean
-----Original Message-----
From: Nick Slay [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 24, 2000 7:38 PM
To: [EMAIL PROTECTED]
Subject: Re: Avoiding using the MAX function
If the MAX() function is used within a transaction you should be ok.....
but it does mean two calls to the DB, instead of one. So there is a
performance overhead... the worst performers in a web application are
the
calls to the database, so trying to reduce them as much as possible is a
good idea.
At 09:28 25/05/00 +1000, you wrote:
>Why, what's the matter with using the MAX() function? Is it anything
>other than the performance load?
>
>Cheers,
>Mike Kear
>AFP Web Development
>Windsor, NSW, Australia
>
>
>On Wed, 24 May 2000, Eron Cohen wrote:
>
> > Hi Everyone,
> >
> > At the CFUG-orama in Washington DC last week, I think Ben Forta said
that
> > there is a way to avoid having to use the "MAX()" SQL function to
get
> the ID
> > number of the item you just inserted into a database. Can anyone
tell me
> > how to do this?
> >
> > In other words, currently I'm doing something like this:
> >
> > <CFQUERY NAME="insert_the_record" DATASOURCE="my_table">
> > INSERT INTO tablename (x,y,z)
> > values ('x','y','z')
> > </CFQUERY>
> >
> > Then, to get the autonumber'ed ID of the item I just inserted, I'll
use:
> >
> > <CFQUERY NAME="get_the_newest_record" DATASOURCE="my_table">
> > Select Max(ID) as the_newest_record
> > >From Tablename
> > </CFQUERY>
> >
> > Now I'll have the ID number of the newest inserted record in
> > #get_the_newest_record.newest_record#.
> >
> > If I understood Ben correctly, then there is a way to insert the
record AND
> > return the ID number of that inserted record all at the same time.
> >
> > The database in question is a SQL Server database. (Although it
would be
> > good to know how to do it with MS Access if its possible).
> >
> > Thanks,
> >
> > Eron
> >
> >
>
------------------------------------------------------------------------
------
> > Archives: http://www.eGroups.com/list/cf-talk
> > 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.
> >
>
>-----------------------------------------------------------------------
-------
>Archives: http://www.eGroups.com/list/cf-talk
>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.
------------------------------------------------------------------------
------
Archives: http://www.eGroups.com/list/cf-talk
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.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
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.
