yes .. perfect sense and my understanding also ... thank you .
i had debug on... the clearcache page showed the query was not being cached
and had some time there (e.g. 160ms) - i.e. query not coming from cache
then when I went back to run my 'proper page' (with timespan 5,0,0,0) the
debug showed 0ms and had "cached query" BUT was pulling back the old cached
results. This leads to the conclusion that the clearcache query had not
cleared it from cache even though the SQL etc was identical :o/
the only other thing that I can think of, is as you suggest, perhaps a small
whitespace difference but it was a straight copy and paste :o/
I have various parameters in the WHERE clause and I am using a cfloop on the
clearcache page to go through each and clear cache for each circumstance:
<!-- Get Maximum match id --->
<cfquery datasource="fclounge" name="matchids">
SELECT MAX(match_id) as maxmatch
FROM match_details
where score <> '-'
</cfquery>
<!--- Recache Match positions --->
<cfloop from="1" to="#matchids.maxmatch#" index="id">
<cfquery name="MatchDetails" datasource="fclounge"
cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#">
SELECT Match_Details.Match_id, Team_Lookup.team_name, Match_Details.Subs,
Match_Details.Goalscorers, Match_Player_Details.goals,
Match_Details.SubsUsed, Match_Player_Details.red_card,
Match_Player_Details.yellow_card, Match_Player_Details.mom,
Match_Player_Details.points, Match_Player_Details.started,
Match_Player_Details.sub_info, Match_Player_Details.subsoff,
Pitch_Positions_Lookup.position_id, Pitch_Positions_Lookup.position_name,
Match_Player_Details.squad_number, Match_Player_Details.present,
Competition_Lookup.competition, Match_Details.Score, Outcome_Lookup.outcome,
Venue_lookup.venue, Player_Details_1.Surname AS mom1, Match_Details.Date,
Player_Details.First_Name, Player_Details.Surname, Player_Details.player_id
FROM Pitch_Positions_Lookup INNER JOIN (Competition_Lookup INNER JOIN
(Venue_lookup INNER JOIN ((Outcome_Lookup INNER JOIN (Team_Lookup INNER JOIN
((Match_Details INNER JOIN Match_Player_Details ON Match_Details.Match_id =
Match_Player_Details.match_id) INNER JOIN Player_Details ON
Match_Player_Details.player_surname = Player_Details.player_id) ON
Team_Lookup.team_id = Match_Details.Team_id) ON Outcome_Lookup.outcome_id =
Match_Details.Outcome) INNER JOIN Player_Details AS Player_Details_1 ON
Match_Details.MOM = Player_Details_1.player_id) ON Venue_lookup.venue_id =
Match_Details.Venue) ON Competition_Lookup.competition_id =
Match_Details.Competition) ON (Pitch_Positions_Lookup.position_id =
Match_Player_Details.position_name) AND (Pitch_Positions_Lookup.position_id
= Match_Player_Details.position_name) AND
(Pitch_Positions_Lookup.position_id = Match_Player_Details.position_name)
AND (Pitch_Positions_Lookup.position_id =
Match_Player_Details.position_name) AND (Pitch_Positions_Lookup.position_id
= Match_Player_Details.position_name)
WHERE (((Match_Details.Match_id)=#id#))
ORDER BY Pitch_Positions_Lookup.position_id;
</cfquery>
</cfloop>
this is another reason that I did not use your suggestion about passing the
timespan as a parameter because I would have to do this for each of the
where clause outcomes whereas this loop will do it for me. (this is just one
of the queries on the clearcache page)
_______________________________________________________
* Regards,
Richard Lovelock
Westminster City Council - Web Support
Cap Gemini Ernst & Young
Southbank
95 Wandsworth Road
London
SW8 2HG
( 0870 906 7482
_______________________________________________________
-----Original Message-----
From: Aidan Whitehall [mailto:[EMAIL PROTECTED]
Sent: 27 October 2003 16:07
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] Clearing cached queries
> yes I am using (1) - copied and pasted the queries in to a separate
file and
> changed the creatimespan from 5 to 0.
>
> so theoretically should be identical apart from the timespan -
changing this
> wouldn't prevent the query being cleared from cache would it - as this
is
> the whole point of the parameter isn't it? (or am i confused)
AFAIK, the SQL is pre-compiled before being executed (someone please
correct if wrong). Then, if there is a cached query of the same name
whose pre-compiled SQL matches the pre-compiled SQL of the query that's
it's about to executed, ColdFusion uses the recordset retrieved from the
previously executed query as long as the current date/time is within the
timespan associated with the previously retrieved statement. If not, it
re-runs the SQL and stores that as a newly cached query.
And the SQL it uses in the comparison is the bit inbetween the SQL tags,
so, no -- the fact that the cachedwithin attribute differs between the
two copies of your SQL won't make a difference.
Whilst what you say suggests that it should treat both SQL statements as
the same, I've only ever used a single SQL statement called from another
template, as I felt it was the best way to guarantee that the SQL was
identical -- you then don't have to worry about a trailing space or
accidental tab character preventing the SQL comparison from being true.
The easy way to test this is to enable debugging and view the SQL
activity. If ColdFusion is using a cached query, the length of time it
took to execute the query will show as "Cached" in the debugging output.
Hope this helps (and makes sense ;-)
--
Aidan Whitehall <mailto:[EMAIL PROTECTED]>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775
Queen's Awards Winner 2003 <http://www.fairbanks.co.uk/go/awards>
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
=======================================================
This message contains information that may be privileged or confidential and is the
property of the Cap Gemini Ernst & Young Group. It is intended only for the person to
whom it is addressed. If you are not the intended recipient, youare not authorized to
read, print, retain, copy, disseminate, distribute, or use this message or any part
thereof. If you receive this message in error, please notify the sender immediately
and delete all copies of this message.
=======================================================
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
