YUP, write a query against both of these tables to delete all entries that
are older than a certain data range. I've done something like this in the
past... btw, this is old code :)
<!--- // Get the Date "X" days ago and format it. // --->
<cfset XDaysAgo = DateFormat(DateAdd("d", -180, Now()), "MM/DD/YYYY")>
<!--- // Set the "X" date to one second past midnight. // --->
<cfset XAtMidnight = XDaysAgo & " 11:59:59">
<!--- // Find all CVRs that are older than 'X' days. // --->
<cfquery name="getOldCVRs" datasource="mydatasource">
SELECT CFID
FROM CGLOBAL
WHERE LVISIT <= '#XAtMidnight#'
</cfquery>
<!--- <cfoutput>#XAtMidnight#</cfoutput><br>
<cfdump var="#getOldCVRs#"> --->
<cfif getOldCVRs.RecordCount NEQ 0>
<cfoutput query="getOldCVRs">
<!--- // Delete CGLOBAL variables that are older than 'X'
days. // --->
<cftransaction>
<cfquery name="DeleteFromCGLOBAL"
datasource="mydatasource">
DELETE FROM CGLOBAL
WHERE CFID = '#getOldCVRs.CFID#'
</cfquery>
</cftransaction>
<!--- // Delete CDATA variables that are older than 'X'
days. // --->
<cftransaction>
<cfquery name="DeleteFromCDATA"
datasource="mydatasource">
DELETE FROM CDATA
WHERE CFID = '#getOldCVRs.CFID#'
</cfquery>
</cftransaction>
</cfoutput>
</cfif>
-----Original Message-----
From: Paul Alkema [mailto:[email protected]]
Sent: Wednesday, February 24, 2010 12:30 PM
To: cf-talk
Subject: CDATA / CGLOBAL Purging
Hi all,
I recently installed Fusion Reactor on my server. While viewing queries that
every page hit I noticed a query that I didn't recognize that was taking up
a significant amount of load time. These queries were to the tables CDATA
and CGLOBAL.
Upon research I found that this was caused by our server storing client
variables however looking at the table, there was about 6 million records
going back more than a year. I looked in the ColdFusion Admin under Client
Variables and it says it's supposed to purge unwanted visitors every 90
days. I changes that to 2 just to test (this was actually done on our
testing server).
Anyway, I haven't seen any change in records. Does anyone have any ideas on
how to decrease the overall amount of records stored?
Best Regards,
Paul
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331085
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
