Show the execution time of any query in ColdFusion 8

Posted by Dan on Nov 17, 2008 @ 1:17 PM

I was working on some code today where I was getting back a query object from a CFC and needed to get the execution time. You can get this information from the <cfquery /> tag by using the result attribute, but since I was getting my query object back from a CFC, I needed to try and determine the execution time from only the query object.

Since the <cfdump /> tag is outputs additional meta data about a query (including the executionTime,) I knew this information could be obtained if I could just find the correct internal Java methods to call. After playing around for a few minutes I discovered that the additional struct keys that ColdFusion 8's <cfdump /> tag outputs come from: query.getMetaData().getExtendedMetaData().

So, to grab the execution time of any query in your code, you can just use:

<cfoutput>#queryName.getMetaData().getExtendedMetaData().executionTime#</cfoutput>

Since I was using this information on a search results page, I wanted to show the execution time in seconds to the user (carried out to the 3rd decimal.) Because query's can often return a 0ms execution time (either because of timing issues on Windows or due to cached queries,) I came up with the following code to show the execution time in seconds. I use the min() function to at least show a 1ms execution time (since 0ms time just looks weird.)

<cfoutput>
     #numberFormat(max(GetCustomers.getMetaData().getExtendedMetaData().executionTime, 1)/1000, "0.000")# seconds
</cfoutput>

The obvious disclaimer here is that since we're using internal methods, this can change in future versions of ColdFusion—which means your code could break.

Categories: HTML/ColdFusion

7 Comments

  • Michael De Jonghe's Gravatar
    Michael De Jonghe
    I prefer to use the RESULT attribute of the CFQUERY tag.

    <cfquery name="read" datasource="dns" result="stats">
    </cfquery>

    then you can simply refer to the stats.executiontime variable.
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @Michael,

    In my case, that wasn't an option because I had a query being returned from a CFC I didn't have control over. I posted this as a solution for getting the execution time when all you have is the query object.
  • Michael De Jonghe's Gravatar
    Michael De Jonghe
    Forget my first comment... I was only reading the code and not the explanation.

    Couldn't you return a structure of both the query and the result?
  • Michael Sharman's Gravatar
    Michael Sharman
    @Michael De Jonghe - Looks like you need to read the 2nd comment ;)

    Dan had no control over the method returning the query so couldn't simply return a struct. Also this could have had repercussions on the rest of the system (API's or other local calls to that method etc).
  • vpxl's Gravatar
    vpxl
    Great peice thanks
    Intresting.
  • brien8cake's Gravatar
    brien8cake
    Anyone know how to get the getExtendedMetaData from from an UPDATE/INSERT/DELETE statement?
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @brien8cake:

    Did you try just giving the <cfquery /> tag a name attribute like you would for a normal SELECT statement? I doubt it works different--but I haven't tested this to confirm.

Comments for this entry have been disabled.