ColdFusion allows you to set an APPLICATION scope that can be leveraged to decrease database calls and increase your site performance. This is a beginner level explanation of how you save a result set in ColdFusion memory that will process quicker than querying a table.
I run a ColdFusion based article site and as the site grew, I was querying against an ever growing table to retrieve articles display in a category. The left navigation involves a database call to dynamically populate the categories.
First, examine your database calls throughout your application. Do you query large amounts of data repeatedly? Are you hitting the same table over and over? Look at those queries as potential candidates to be cached in the APPLICATION scope. I prefer to create the queries without a WHERE statement, then I use a query of query (QofQ) to get the specific results.
For our example we will include the following code in our Application.cfm file:
1 2 3 4 | <cfquery name="APPLICATION.Articles" cachedwithin="#createtimespan(1,0,0,0)#"> SELECT ID, Title, Content, SubCategory, PublishedDate FROM Articles </cfquery> |
There’s not much to it. We created a query in the as an APPLICATION variable and it that result set will be used for one day or 24 hours. The createtimespan function uses the syntax (days, hours, minutes, seconds). I prefer to use a result set for 24 hours, because I am constantly adding content to the site.
Here is how we use a query of query to consume the data on page (Category.cfm) that lists 20 of the newest articles in a given subcategory.
1 2 3 4 5 6 | <cfquery name="getCookingArticles" dbtype="query"> SELECT TOP 20 * FROM Articles WHERE SubCategoryID = 23 ORDER BY PublishedDate DESC </cfquery> |
Now display the data as normally and you will have a result set without a database call. You are using ColdFusion to generate the data without going the extra step to ask the database.
1 2 3 4 5 6 7 | <table> <cfoutput query="getCookingArticles"> <tr><td><a href="Article.cfm?ID=#ID#">#Title#</a><br/> #left(Content,300)#<br/> Published on: #dateformat(PublishedDate, 'short')#</td></tr> </cfoutput> </table> |
Not requesting from your database as often will speed up your application and will benefit the user experience when people visit your site. Nobody likes to see a site wait to load.
Note: The first time you run the APPLICATION query it will take a bit longer than a normal query because it is saving it to memory. The next call will be very quick.
I hope you have enjoyed this beginner level tutorial on how to speed up your applications. If you like it, please share it with others.
November 6th, 2007 at 10:32 am
This is a good informative post. Am i right in assuming that even without the QofQ it will still limit the server activity to once every 24 hours? If that is the case, then the QofQ won’t be necessary, will it? The WHERE clause can be added to the main query, and provide the same results. Is there a reason for your preference for using the QofQ, other than just personal choice. Does it save on MORE server resources?
Something else to consider… when a new article is actually added, functionality could be built in to automatically update the cached query, so your users would get the new article straight away (not having to wait 24 hrs for the query to re-cache). This can also allow you to lengthen the cached period in cachedwithin, as all new articles will already be added to the cached query.
November 6th, 2007 at 12:33 pm
Hello, thanks for your comment. Yes, even without the QoQ you can hit the server every 24 hours. The QoQ is to simply extract the data you need from the general query that is saved in the application variable. It is just personal preference to use the QoQ. I don’t have any hard figures to see if it saves even more resources, but it gives me something to look into.
For the content that I serve, it is better (performance wise) if I refresh the query all at once because if the huge amount of data. However, it is possible to refresh the application variable whenever the table is updated. A solution would be to create a CFIF statement that checks for the existence of a refresh variable, then use the CFQUERY tag to refresh the data.
November 6th, 2007 at 2:53 pm
For most people using cachedwithin will be enough and if you are not going to use QoQ then there is no need to use Application scope.
cachedwithin="#createtimespan(1,0,0,0)#"QoQ is much slower than hitting the database, but it is easier and cheaper to scale out the web servers than it is to scale up the database server.
On a high load page that needs to be fresh, I would still use cachedwithin but just set the time in minutes instead of hours. If you cached a database query for one minute and there were 10 page views in that minute, then you saved 9 hits on the database for the same thing.