• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Improving Application Performance (Part 2)

Not all queries can be saved as an application variable. For Queries that do not meet the checkpoints in my previous tutorial there is another way to improve performance.  Query Caching is another way to save data and eliminate unnecessary queries. This is for queries that are more dynamic in nature.

For this tutorial we will look at one coldfusion task that almost everyone uses. The next-n style of navigating multiple pages of data. The next-n navigation allows users to brows back and forth through query results one page at a time. The problem here is that every time the page is loaded it has to query the database. To make things worse, It usually returns ALL the data from the database, It is normally the cfoutput loop that restricts the amount of data being displayed. Regardless of what is being displayed, ALL the rows are being retrieved. If your visitor clicks the next or back buttons 10 times you just performed 10 queries for the exact same data.

I guess you figured out by now that this is a non-efficent query method and really a waste of resources. This is where Query Caching comes in handy. You can cache query results for a specified amount of time  and coldfusion does not have to rerun the query until is needs too. Query cashing is the easiest item to add and only involves adding a single line to your existing code.

A sample query for this tutorial might look something like this:

<cfquery name="movies" datasource="MovieList">
SELECT MovieTitle, ProductionCompany, MovieYear, Catagory
FROM MovieList
ORDER BY MovieTitle
</cfquery>

If I had hundreds of records in my movie database I would want to cache the query result, and add a next-n style navigation since we want to take advantage of the saved data, we could add one line to save the data for us and reuse it for the next page. That query would look like this:

<cfquery name="application.list" datasource="MovieList" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
SELECT MovieTitle, ProductionCompany, MovieYear, Catagory
FROM MovieList
ORDER BY MovieTitle
</cfquery>

As you can see, the big difference here is the CACHEDWITHIN attribute that we added. I'll explain what this does and how it works.

The CACHEDWITHIN attribute specifies how long a query is to be used before it needs to be re-run. The CreateTimeSpan () function is used to create the interval values. It needs 4 values in order to operate.  days, hours, minutes, seconds. So in my query above, CreateTimeSpan(0,0,15,0) would be (0 days, 0 hours, 15 minutes, and 0 seconds) after 15 minutes my query will be re-read and and possible cashed again.

That's it, no other programming involved.