cftransaction for SELECT statements

[Pat Larrea]

Hi all,

I have a question about the use of cftransaction tag. I've been reviewing the
source code of the system of my company, where I started to work recently,
and found that in some parts this tag is used just to wrap some (about 10)
SELECT statements ( without INSERT, UPDATE o DELETE). I doubt that
this kind of use has an positive effect, on the contrary it seems to me that
it affects the performance. I don't have much experience in Coldfusion, so
I'm not sure if my impression is correct but I've never seen any example of
the use of cftransaction tag only with SELECT statements.

I'd appreciate if someone could tell me how this use of cftransaction afects
the performance of retrieving data. The DB we use is SQL Server 2000.

Thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3178
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Raymond Thompson]

You do not need the CFTRANSACTION around select statements. The
CFTRANSACTION statement is used around a group of queries that update the
database and will not commit the changes until all the queries are
successful.

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578

-----Original Message-----
From: Pat Larrea [mailto:hiralalala@...]
Sent: Tuesday, January 20, 2009 4:56 PM
To: sql
Subject: cftransaction for SELECT statements

Hi all,

I have a question about the use of cftransaction tag. I've been reviewing
the
source code of the system of my company, where I started to work recently,
and found that in some parts this tag is used just to wrap some (about 10)
SELECT statements ( without INSERT, UPDATE o DELETE). I doubt that
this kind of use has an positive effect, on the contrary it seems to me that
it affects the performance. I don't have much experience in Coldfusion, so
I'm not sure if my impression is correct but I've never seen any example of
the use of cftransaction tag only with SELECT statements.

I'd appreciate if someone could tell me how this use of cftransaction afects
the performance of retrieving data. The DB we use is SQL Server 2000.

Thanks.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3179
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Robert Rawlins - Think Blue]

Just as a quick note on this, there are some instances when using
cftransaction around a SELECT query can be beneficial, for instance in a
busy database sometimes you won't want SELECT queries waiting around for
resource locks to be dropped on tables that are being inserted into, you
could then use <cftransaction isolation="read_uncommited"> and it'll read
'dirty' data.

This is discussed on Ben Nadels blog which I was reading just a week or so
ago.

http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Imp
rove-Performance.htm

Rob

-----Original Message-----
From: Ray Thompson [mailto:ray@...]
Sent: 21 January 2009 13:32
To: sql
Subject: RE: cftransaction for SELECT statements

You do not need the CFTRANSACTION around select statements. The
CFTRANSACTION statement is used around a group of queries that update the
database and will not commit the changes until all the queries are
successful.

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578

-----Original Message-----
From: Pat Larrea [mailto:hiralalala@...]
Sent: Tuesday, January 20, 2009 4:56 PM
To: sql
Subject: cftransaction for SELECT statements

Hi all,

I have a question about the use of cftransaction tag. I've been reviewing
the
source code of the system of my company, where I started to work recently,
and found that in some parts this tag is used just to wrap some (about 10)
SELECT statements ( without INSERT, UPDATE o DELETE). I doubt that
this kind of use has an positive effect, on the contrary it seems to me that
it affects the performance. I don't have much experience in Coldfusion, so
I'm not sure if my impression is correct but I've never seen any example of
the use of cftransaction tag only with SELECT statements.

I'd appreciate if someone could tell me how this use of cftransaction afects
the performance of retrieving data. The DB we use is SQL Server 2000.

Thanks.





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3180
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Pat Larrea]

Thank you both,

Yes, my understanding was exactly what Ray kindly explained, but I saw some comments about the use of isolation attribute and it made me unsure about which is better, removing this tag or adding 'read_uncommitted' attribute to this tag. From what I read in the page Rob put, SQL server put locks on the resource whether or not I put cftransaction tag, therefore, to force the query to read data without waiting for the drop of locks, it is recomended to use <cftransaction isolation="read_uncommited"> always (of course when the dirty read is acceptable). Anyway should be the time to compare the performance of each case, with and without this tag.

Thanks again, but further information, if any, is still welcome.

Pat

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3182
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[James Holmes-3]

Another use would be if you want the DB to be consistent for the whole
set of queries; in this case you would use the serializable isolation
level.

mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/


2009/1/21 Robert Rawlins:

> Just as a quick note on this, there are some instances when using
> cftransaction around a SELECT query can be beneficial, for instance in a
> busy database sometimes you won't want SELECT queries waiting around for
> resource locks to be dropped on tables that are being inserted into, you
> could then use <cftransaction isolation="read_uncommited"> and it'll read
> 'dirty' data.
>
> This is discussed on Ben Nadels blog which I was reading just a week or so
> ago.
>
> http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Imp
> rove-Performance.htm

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3181
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Raymond Thompson]

I doubt you will notice much difference in performance unless you are really
going through that code many times a second. While it is good to have code
that is optimized and doing as it should at some point you have to consider
if it is worth the effort. There are many arguments about code bloat but the
simple fact is that people time is expensive. The time spent in making the
change and testing the change may not produce results that justify the
effort.

At one time there may have been some selects and an update or insert within
the cftransaction block. The updates or inserts may have been removed and
the cftransaction was not noticed. It happens with code, I myself being
guilty more than once.

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578


-----Original Message-----
From: Pat Larrea [mailto:hiralalala@...]
Sent: Wednesday, January 21, 2009 9:30 AM
To: sql
Subject: Re: cftransaction for SELECT statements

Thank you both,

Yes, my understanding was exactly what Ray kindly explained, but I saw some
comments about the use of isolation attribute and it made me unsure about
which is better, removing this tag or adding 'read_uncommitted' attribute to
this tag. From what I read in the page Rob put, SQL server put locks on the
resource whether or not I put cftransaction tag, therefore, to force the
query to read data without waiting for the drop of locks, it is recomended
to use <cftransaction isolation="read_uncommited"> always (of course when
the dirty read is acceptable). Anyway should be the time to compare the
performance of each case, with and without this tag.

Thanks again, but further information, if any, is still welcome.

Pat



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3183
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6