Skip to main content
Ben Nadel at cf.Objective() 2014 (Bloomington, MN) with: Alec Irwin
Ben Nadel at cf.Objective() 2014 (Bloomington, MN) with: Alec Irwin

Installing FusionReactor APM Showed Me A Huge Oversight In My ColdFusion Queries

By on
Tags: ,

In an ongoing effort to modernize and improve my ColdFusion blogging platform, I installed the FusionReactor Application Performance Monitoring (APM) java agent earlier this week. I've talked about FusionReactor before and how critical it's been for debugging complex problems at InVision. And while my blogging platform is orders-of-magnitude more simple, I'm hoping that it can still offer me a different perspective on my ColdFusion application performance. And, wouldn't you know it, the moment I installed it, I immediately spotted a huge oversight in the way I'm writing my ColdFusion queries.

After I installed the FusionReactor java agent and restarted ColdFusion, I started to poke around in the UI (user interface) and get an initial sense of how my blog is operating. It's certainly a different "feel" from a SaaS product like InVision since it gets very little traffic and does almost no processing. But, when I switched to the JDBC Requests view within the Transactions section, I saw a major issue:

Can you spot it?

When I was replacing all my CFQuery tags with queryExecute(), I completely forgot to apply one of my best practices: including a DEBUG comment in my SQL statements. I find this approach to be incredibly helpful! I've even talked about it specifically in the context of FusionReactor request tracing. And yet, somehow—in the mad dash to modernize—I completely overlooked it on my ColdFusion blog.

So, this morning, I went through all of my ColdFusion components and replaced queryExecute() calls like this:

component {

	/**
	* I get all active blog posts for pre-warming of the cache.
	*/
	public query function getAllActivePosts() {

		var results = queryExecute(
			"
				SELECT
					b.id
				FROM
					blog_entry b
				WHERE
					b.isActive = 1
				ORDER BY
					b.id DESC
			"
		);

		return( results );

	}

}

... with calls like this:

component {

	/**
	* I get all active blog posts for pre-warming of the cache.
	*/
	public query function getAllActivePosts() {

		var results = queryExecute(
			"/* DEBUG: partials.blogPostGateway.getAllActivePosts(). */" &
			"
				SELECT
					b.id
				FROM
					blog_entry b
				WHERE
					b.isActive = 1
				ORDER BY
					b.id DESC
			"
		);

		return( results );

	}

}

Notice that the first part of my SQL statement is now a /*DEBUG*/ comment. Since this is a SQL comment, and not a CFML comment, it will be sent to the database as part of the query execution. Which means, not only will it show up in FusionReactor, it will also show up in the database's slow-query log (and other database performance monitoring tools).

ASIDE: You may notice that I'm using string concatenation to include the DEBUG comment. This is silly, but I'm doing that solely because my current syntax highlighter gets messed up when I try to add the comment directly to the main SQL statement. This isn't a "good reason" to do it; but, it's a pragmatic reason to do it. In the long run, I'd like to help contribute back to the CFML syntax package for SublimeText; but, I don't have those skills yet.

Once I deployed these changes, I went back to the FusionReactor JDBC Transactions and was delighted to immediately have more insight into how my ColdFusion application was performing:

As you can see, each JDBC transaction now sheds more light onto where it is actually executing from within the ColdFusion application. This should make identifying and fixing performance issues a lot easier.

Though, of course, you'll see that most of these JDBC times are 0ms - 2ms in duration. Aren't databases freakin' magical?! I mean, come on!

I know this is a really minor update on the blog; but, it really reduces the cognitive load on debugging. And, a "best practice" is only a best practice if you put it into practice. And, this is something I should have been including in my ColdFusion queries from the start. Omitting it was just an oversight in my haste to rip out all the old query code.

Want to use code from this post? Check out the license.

Reader Comments

8 Comments

I really dislike the whole "index.cfm" thing, so use your cfc to set it dependent on route, i.e javaAgentHelper.transactionSetName( req.routeName ) - that way I know which actual path is slow, rather than it just defaulting to index.cfm. Makes for some colourful graphs too.. :)

15,260 Comments

@Tom,

100% agreed. That's on my list of things to do. This site uses an event variable to do the routing, like blog.view. My one concern is that I want to do that at the end of the request so that I know the request is valid (ie, not some rando 404 URL that someone tried to hack-in). Hopefully, FusionReactor will still honor that customization at the end of request.

I'll see how it goes!

15,260 Comments

@Adrian,

Oh, that's a really interesting thought! That would definitely tell you exactly where it is in the app. I might have some concern about possible performance-overhead of calling that on every query - not sure how that call-stack is generated. But, very thought provoking!

15,260 Comments

Another immediate win that I get with FusionReactor is that I can see exactly how much RAM I'm using. And, since I cache a lot of data in-memory, this is really important:

www.bennadel.com/blog/4225-fusionreactor-apm-gives-me-peace-of-mind-over-in-memory-caching-in-coldfusion.htm

This alleviated a lot of fears for me! And, showed me that I'm barely using any RAM at all, despite how much data it seems like to my feeble human brain.

Post A Comment — I'd Love To Hear From You!

Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.