Speeding Up CFDirectory in Coldfusion

We were having high CPU load (and frequent alerts) on an older server at work this week, so I took a look at what I suspected was the culprit - a particularly nasty piece of code I'd attempted to speed up before by wrapping a <cache> tag around it.

The Problem

Each database page on the website had related images stored in a /photos directory, and the previous programmer had used Coldfusion's cfdirectory tag to pull out ALL files from this directory for reference / displaying on the page. Next, a query-of-queries was used to filter these filenames to match the current page before they were printed out into HTML.

<cfdirectory
  action="list"
  directory="#request.filepath#\photos\"
  name="images"
  />
<cfquery name="getimages" dbtype="query">
    select * from images where name like '#variables.page_id#%'
</cfquery>

These pages took over FOUR seconds to load without caching. I wasn't sure of the exact timeline, but my hypothesis was that any bot crawling the site - Googlebot, Bing, etc - would trigger enough of these super-slow four-second requests for Coldfusion to hang up and/or crash.

The Solution

I'd been aware of the poor performance of this before, and attempted wrapping a <cfcache> around the issue before. This worked "fine", I suppose, but it was only cached for six hours and it was still slow for newer pages.

Use ListInfo="Name"

This is a "duh" performance improvement. Ben Nadel says:

Listing only the name column can have significant performance increases and should be used whenever you don't need the rest of the information returned in CFDirectory.

I quickly added listinfo="name" and checked - load time was now around 1 second. Woah, what a difference!

Prefer Filter="#var#" Over a Query-Of-Queries

Mike Sprague had passed this on to me a while ago - don't use query-of-queries! I was curious to see if cfdirectory's filter attribute would be faster or slower than this query - my suspicion was it would be slower. I was happy to be proven wrong!

<cfquery name="getimages" dbtype="query">
Select * from dirimages where name like '#variables.page_id#%'
</cfquery>

The Result

The final <cfdirectory> looks like

<cfdirectory
    action="list"
    directory="#request.filepath#\photos\"
    name="dirimages"
    listinfo="name"
    type="file"
    filter="#variables.page_id#*.jpg"
    />

The page now renders in ~400ms. Woop woop! This should make a huge difference in CPU load, server performance, etc.