Problem
A large data set can cause performance problems if an application needs to query the database to drill down into the data to get to specific details.
Solution
ColdFusion allows queries to be run against existing queries with a subset of SQL commands available to use.
Detailed explanation
If an application has a large query that is run and then needs to filter the data, it's not always good for performance to make a trip to the database to retrieve the filtered data. Loading the large query into memory and using ColdFusion's query of a query functionality is an easy solution. For example a product catalog is often searched on any number of variables such as category, price range, ratings, etc.
The main product query can be run and loaded into the application scope in the onApplicationStart method of Application.cfc
<cfquery name="APPLICATION.qGetProducts" datasource="dsn"> SELECT * FROM tbl_Products </cfquery>
Using a query of a query the application can filter the larger data set without having to make another trip to the database. Instead of supplying a datasource attribute, the <cfquery> sets the dbtype attribute to query and in the SQL statement the FROM keyword uses the name of the query instead of a tablename.
To filter the products on a specific category the application would use the following code:
<cfquery name="qGetProductsByCategory" dbtype="query"> SELECT * FROM APPLICATION.qGetProducts WHERE categoryId = <cfqueryparam value="#FORM.categoryId#" cfsqltype="cf_sql_integer" /> </cfquery>
This is a great tool to help with performance however you are limited to a specific subset of SQL commands when using query of queries.
Also, keep in mind you can use query of queries on any ColdFusion tag that produces a recordset, not just the <cfquery> tag.
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
