Quick and easy performance gains!

In my last post I talked about tuning the JVM that ColdFusion runs on to get better performance. However the tuning doesn't stop there, by improving legacy/3rd party code you can make significant gains. Once I'd got the server stabalised I then set about looking at the slow requests using Fusion Reactor (SeeFusion does this as well). To start with I looked for the requests that appeared more than once in the list (as you can get annonymies because of other processes).

Here are some quick tips to improve performance:

Only use cfqueryparam only when the value changes.

We all read about using cfqueryparam to prevent SQL inject attacks, however, if the value doesn't change (and isn't passed in from a scope that can be manipulated such as URL/Form/Cookie), then you can gain performance by not using it. For example take the following query (which uses a deleted column for tracking soft deletes).

<cfquery name="foo" datasource="#getDsn()#">
  SELECT * 
  FROM myTable
  WHERE deleted = <cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate( CreateDate( 1900, 1, 1 ) )#" />
</cfquery>

In the query above, the value used for the deleted column never changes. By using cfqueryparam, you are not allowing your database engine to optimise the execution plan. I found that by simple changing the query above to the one below I shaved off a massive 400ms:

<cfquery name="foo" datasource="#getDsn()#">
  SELECT * 
  FROM myTable
  WHERE deleted = #CreateODBCDate( CreateDate( 1900, 1, 1 ) )#
</cfquery>

I could potentially gain a couple of milliseconds by using {d '1900-01-01'} instead.

I should point out that this doesn't always work. The performance gains (if any) will depend on the database engine, datatype and records in your database. Just don't assume that using cfqueryparam will always make your query faster.

Use Query Caching

This site wasn't using query caching as the data changes quite a lot. However, by just caching the query for just 1 minute you can get improved performance. 

<cfquery name="foo" datasource="#getDsn()#" cachedwithin="#CreateTimeSpan( 0, 0, 1, 0 )#">
  SELECT * 
  FROM myTable
  WHERE deleted = #CreateODBCDate( CreateDate( 1900, 1, 1 ) )#
</cfquery>

Also, consider if you need to get all columns from the table. If you don't then you might be better off not using "SELECT *". 

With more complicated queries, or queries in a loop, I strongly suggest that you also learn about using derived tables and subqueries. Writing good SQL is often the best way to improve performance.

ColdFusion has a host of caching capabilities that you can use, but I think that you should look at getting your code to run faster and then use caching, rather than relying on caching on it's own. 

• Posted in:
• ColdFusion

3 comments

1. Interesting info about cfqueryparam, John, thanks. I assumed it would always be faster because you were avoiding the need for the DB driver to work out what data type the value was. But your tests show that not to be the case with static values.
   
   Comment by Julian Halliwell – July 29, 2010
2. @Julian, as usual it depends :)
   
   Performance gains (and even penalties) are different depending on the database engine, datatype and number of records in the database. So this isn't a one size fits all solution, but it's definitely worth looking at if you have a slow query!
   
   Comment by John Whish – July 29, 2010
3. nice info is there any other ways we can improve the performance of an coldfusion appication using cfquery
   
   Comment by shariff – January 25, 2011