Jochem,
Thanks for that clarification ... I guess in my zest to improve
performance I was willing to try anything and didn't read that clearly
enough. Using Stored Procedures where possible is helping, I'm caching
everything I can too to try and speed things up. I was worried about
using CACHEDWITHIN because I thought it cached based on the query name
(yes, foolish thinking) and not the actual query itself. Now that I know
better I'll be just fine.
On the following topic:
--- "For instance, the SELECT MAX(ID) + 1 -> INSERT trick to get the
next
value for a primary key technically requires a serializable isolation
level."
That seems like a bad idea anyway, wouldn't it be possible for someone
to write to the database between you grabbing that value and actually
using it?
Wouldn't this be safer:
<cfquery name="qryIns" datasource="#request.dsn#">
BEGIN TRANSACTION
SET NOCOUNT ON
INSERT INTO tblMyTable
(MyValue)
VALUES
('A Value');
SET NOCOUNT OFF
SELECT @@Identity AS 'Identity'
COMMIT TRANSACTION
</cfquery>
Then just use: #qryIns.Identity# in your supporting data instead of the
method above?
Or does the method of incrementing the ID work reliably? I would assume
that's inside of a transaction so that no one can grab that ID before
you can use it?
Thanks,
Joshua Miller
Head Programmer / IT Manager
Garrison Enterprises Inc.
www.garrisonenterprises.net
[EMAIL PROTECTED]
(704) 569-9044 ext. 254
************************************************************************
*************
Any views expressed in this message are those of the individual sender,
except where the sender states them to be the views of
Garrison Enterprises Inc.
This e-mail is intended only for the individual or entity to which it is
addressed and contains information that is private and confidential. If
you are not the intended recipient you are hereby notified that any
dissemination, distribution or copying is strictly prohibited. If you
have received this e-mail in error please delete it immediately and
advise us by return e-mail to [EMAIL PROTECTED]
************************************************************************
*************
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 5:04 PM
To: CF-Talk
Subject: Re: Macromedia TechNote on Database Performance
Joshua Miller wrote:
> Does anyone have any information on changing the Isolation Level on MS
> SQL Server 2000 as referenced in:
> http://www.macromedia.com/support/coldfusion/releasenotes/mx/releaseno
> te
> s_mx.html#perf
>
> Does this have much impact on performance? If so, how do I do it?
> There is no setting in the CFAdministrator to change this value, and I
> don't see a connection string box as referenced in the technote. I was
> thinking connection strings were only needed if the driver didn't
> exist or in some special instances ... do I need to use a connection
> string to setup my connection to SQL Server in this manner?
Focus on the second part. It can be quite usefull to change this setting
for specific transactions, but if anything read_commited is a to low
level of isolation for general queries.
(For instance, the SELECT MAX(ID) + 1 -> INSERT trick to get the next
value for a primary key technically requires a serializable isolation
level.)
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
