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.



Related Entries

1 person 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.
Good point about re-starting CF. The change you made may require CF to be restarted before it takes effect.
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.
Sean, that isnt very helpful when its proven that CF / SQL2K and client vars are a performance killer, I mean how hard is it to change the server to check the vars before a write, we are getting 150-200% performance lags with Client vars.
I don't think it should matter that it hits the SQL server - at least in MySQL, if an UPDATE instruction is sent, and the data is the same, the mySQL server does nothing. It doesn't just update with the same data. I'd assume the MS version is the same.
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.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?