Pete Freitag Pete Freitag

Client Variables unnecessary overhead?

Published on December 04, 2003
By Pete Freitag
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.



client variables cfml sql

Client Variables unnecessary overhead? was first published on December 04, 2003.

If you like reading about client, variables, cfml, or sql then you might also like:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

CFBreak
The weekly newsletter for the CFML Community


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.
by PaulH on 12/04/2003 at 4:37:22 AM UTC
I beleive I did restart CF!
by Pete Freitag on 12/04/2003 at 1:05:28 PM UTC
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!
by Pete Freitag on 12/04/2003 at 5:08:07 PM UTC
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... :)
by Sean Corfield on 12/08/2003 at 2:27:43 AM UTC
Sean, where can I read more about converting session vars over to J2EE vars?
by Seth on 12/24/2003 at 10:42:28 AM UTC
Hi Seth,

Just enable the check box in ColdFusion Administrator.
by Pete Freitag on 01/02/2004 at 10:28:55 PM UTC
Eric,

It has to check to see if the data is the same, isn't that doing something? Also there is network overhead.
by Pete Freitag on 07/21/2005 at 12:21:44 PM UTC
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.)
by charlie arehart on 03/01/2007 at 1:43:30 AM UTC
Thanks Charlie, I have updated the entry.
by Pete Freitag on 03/01/2007 at 9:01:20 AM UTC