Client Variables unnecessary overhead?

December 04, 2003
coldfusiondatabases

I was curious to know how much overhead client variables incur in an application. I had some questions such as: Does a request that doesn't use client variables, but is in an application where client variables are enabled still cause a trip to the database? It turns out it does!

I enrolled a very cool feature in SQL Server called the SQL Profiler for the job. SQL Profiler basically allows you to log communication with the database server. With SQL Profiler it is easy to watch ColdFusion's communication with SQL Server.

So I created a client database and checked "Disable global client variable updates" in The ColdFusion Administrator (Using ColdFusion MX 6.1). The description for this setting in the Administrator states:

This option controls how ColdFusion updates global client variables, such as HITCOUNT and LASTVISIT. If updates are disabled, ColdFusion updates these variables only when they are set or modified. If updates are enabled, ColdFusion updates global client variables for each page request.

So you would think that with client variable updates disabled, ColdFusion would not have to make a trip to the database on pages that don't use client variables. This however did not turn out to be the case, here's the database communication for each page request:

SET IMPLICIT_TRANSACTIONS ON
exec sp_execute 1, '200:55269098', 'localhost'
exec sp_execute 2, '200:55269098'
IF @@TRANCOUNT > 0 COMMIT TRAN
exec sp_execute 1, '200:55269098', 'localhost'
declare @P1 int
set @P1=8
exec sp_prepexec @P1 output, N'@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000)',
N'update CDATA set data = @P1 where cfid = @P2 and app = @P3 ',
' ', '200:55269098', 'localhost'
select @P1
exec sp_execute 4, '200:55269098'
declare @P1 int
set @P1=9
exec sp_prepexec @P1 output, N'@P1 varchar(8000),@P2 datetime,@P3 varchar(8000)',
N'update CGLOBAL set data =@P1 ,lvisit =@P2 where cfid =@P3 ',
'urltoken=CFID#=200&CFTOKEN#=55269098#lastvisit={ts ''2003-12-03 23:59:40''}
#timecreated={ts ''2003-12-03 23:59:40''}#hitcount=1#cftoken=55269098#cfid=200#',
'Dec 3 2003 11:59PM', '200:55269098'
select @P1
IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

It is interesting to point out that it doesn't change the value of lastvisit, or hitcount, but it does indeed UPDATE them in the database for every page request! This seams like quite a bit of unnecessary overhead. Am I missing something here, or is this a bug?

Update: Charlie Arehart points out in the comments that this has been fixed in 6.1 Updater, and CF7.



You might also like:

2 people found this page useful, what do you think?

Comments

did you try re-starting the service? turning it on & seeing the time spent? if its going to run back to the db server anyway, might as well have it update these values. interesting information in any case.
I beleive I did restart CF!
Just to clarify the values of hitcount and lastvisit are not changing in the update statement. The point it that the update statement is still being sent to the database server, even though nothing is changing!
And people wonder why the first thing I do when I set up a CF instance is disable client variables and switch session variables over to the underlying J2EE variables... :)
Sean, where can I read more about converting session vars over to J2EE vars?
Hi Seth, Just enable the check box in ColdFusion Administrator.
Eric, It has to check to see if the data is the same, isn't that doing something? Also there is network overhead.
Just an update for any who come along and see this long after it was posted: this was identified and fixed by MM as a hotfix, as discussed at http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19590. That fix was rolled into the "6.1 Updater" and of course is fixed in CF7. This is no longer an issue (unless you never updated, in which case if you don't want to update versions, at least use the hotfix link above.)
Thanks Charlie, I have updated the entry.


foundeo