Is it possible to cache a ColdFusion Query of Query

Question

I've got query that is returning all translations for a site. It does this by getting all translations that are in the users desired language, then the remaining that are in the site default language, then any other strings that have not been translated. I'm using cachedwithin on that query since the data doesn't change often, and I'm resetting that queries cache if translations are modified. I'm then using ColdFusion's Query of Query to get the individual record that I'm after. This has increased performance considerably.

I was wondering if it's possible to further cache the Query of Query query to further increase performance. It appears to work as page load is 1/6 faster, however are there any gotchas with this technique?

The Query of Query is below.

<cfquery name="qryTranslation" dbtype="query">
    SELECT
        TranslationString
    FROM
        qryGetText
    WHERE
        TranslationHash = <cfqueryparam value="#StringHash#" cfsqltype="cf_sql_varchar">
        AND DesiredLanguageID = <cfqueryparam value="#Arguments.LanguageID#" cfsqltype="cf_sql_bigint">
</cfquery>

Barry Jordan · Answer 1 · 2011-12-08T13:21:00.970

Is it possible to cache a ColdFusion Query of Query

Yes, it is possible.

however are there any gotchas with this technique?

You queries will be cached based on its signature, so in your case the StringHash and Arguments.LanguageID. If you have a cached QofQ for every translation on a page, on many pages on your site, then you could potentially max out the "Maximum number of cached queries" value. If this happens other, potentially larger and more important, cached queries in the query cache could be evicted.

Calculating a suitable "Maximum number of cached queries" could be determined by load testing and using the build in server monitor to monitor the number of queries in the cache.

score 1 · Accepted Answer · answered Dec 08 '11 at 23:21

There is one big gotcha with caching a query of query.

The documentation for caching a query states that:

To use cached data, the current query must use the same SQL statement, data source, query name, user name, and password.

However a Query of Query does not have a data source, user name or password, so you lose a lot of "over cache" protection. The query as it stands in your question will conflict with any other queries on your server that have the same name and formatting. So if you have more than one website that uses this code then the first website that is loaded will dictate the translations used on the rest of the websites.

A quick way around this is to trick the query into being more constrained.

<cfquery name="qryTranslation" dbtype="query">
    SELECT
        TranslationString
    FROM
        qryGetText
    WHERE
        TranslationHash = <cfqueryparam value="#StringHash#" cfsqltype="cf_sql_varchar">
        AND DesiredLanguageID = <cfqueryparam value="#Arguments.LanguageID#" cfsqltype="cf_sql_bigint">
        AND '#Variables.DSN#' = '#Variables.DSN#'
</cfquery>

Change Variables.DSN to be the value of the datasource attribute in the main query. If you don't trust that variable, then also make it a cfqueryparam on both sides of the operator.

Is it possible to cache a ColdFusion Query of Query

2 Answers2