Webmaster Forum / ColdFusion / Getting Started / July 2007

My guess is that you are running your 2nd query from a different page.

Yes, I obviously don't understand this "in memory" query functionality!  Is
there some way i can save a query in memory for easy reuse?

Some of the XML files can take 30 seconds++ to parse into a query and I want
to analyse them in diferent ways.

Make them session variables.

[i]This is the master query:
<cfquery name="rsQofmyXmlQuery" dbtype="query"
cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT * FROM myXmlQuery
</cfquery>[/i]

I'm afraid not. It seems the master query is [i]myXmlQuery[/i].

Your use of the cachedWithin attribute in the query of query is redundant. You
should use it in the original query, [i]myXmlQuery[/i], thus

<cfquery name="myXmlQuery" datasource="myDSN"
cachedwithin="#CreateTimeSpan(0,0,30,0)#">
SELECT * FROM myTable
</cfquery>

Then Coldfusion will cache the query for 30 minutes. It means, in the code
that follows, you may implement a query that makes Coldfusion to use the same
data without having to make another trip to the database.

However, there are conditions. To use cached data, the current query must use
the same

SQL statement ([i]SELECT * FROM myTable[/i] in this example),
data source ([i]myDSN[/i]  in this example),
and query name  ([i]myXmlQuery[/i]  in this example).

Therefore, in the next 30 minutes, whenever Coldfusion encounters a query like
 
<cfquery name="myXmlQuery" datasource="myDSN">
SELECT * FROM myTable
</cfquery>

on a page in the current application, it will simply use the cached data.

An entirely different way to cache a query is to use the method that Dan
suggests. Store the query in a shared scope. You would then usually do
something like

<cfquery name="request.myXmlQuery" datasource="myDSN">
SELECT * FROM myTable
</cfquery>

or

<cfquery name="session.myXmlQuery" datasource="myDSN">
SELECT * FROM myTable
</cfquery>

or

<cfquery name="application.myXmlQuery" datasource="myDSN">
SELECT * FROM myTable
</cfquery>

Coldfusion would cache the query request.myXmlQuery, session.myXmlQuery or
application.myXmlQuery, respectively, for the duration of a request, a session
or for the entire application. The scopes already suggest the functionality.
You would store a query in request scope only if the data was applicable to a
request. You would store it in session scope only if the data was applicable to
each particular client session, and application scope if the the data is to be
cached for all clients for the entire duration of the application.

You may of course do a query of a query starting from a query in any of these
3 categories. Naturally, the QofQ must happen within the same scope. For
example, suppose session management is on and you had earlier defined
[i]session.myXmlQuery[/i] as above. Then, anywhere in the session, you could do

<cfquery name="rsQofmyXmlQuery" dbtype="query">
SELECT * FROM session.myXmlQuery
</cfquery>

Thanks BKBK & Dan,

I have saved the query as a session variable... very useful, I did not realise it was possible!

Saving the parsed XML as a session variable has also speeded things up.