Not only is it possible, but I spend most of my time every week doing this for our Webapper customers.

As we all know, businesses and their supporting web applications are constantly changing and adapting. With any luck, change is in the direction of growth, and adaptation simply becomes a component necessary to keep up with that change. As businesses and web applications grow over time and exposure, so does the data. A tool or process in a web application that has been humming along and performing well over the years is subject to turning on a dime and making your life miserable as additional data and concurrent usage flood your application.

I recently fixed a classic example of this for a customer of ours. This particular customer runs (among other things) a pretty well utilized job posting and searching application which sits atop ColdFusion 8 and a SQL Server 2005 database. The site is averaging about 6,000,000 page views a month, so performance gains can be expected to have a broad impact when you factor them by that level of utilization. The customer was reporting all sorts of performance problems and slow running pages within their application but was at a bit of a loss in terms of specifically identifying the problem areas and addressing them. That’s when they called Webapper.

I never begin a performance analysis engagement like this for a customer without first installing SeeFusion. It’s quite simply one of the best tools I’ve ever worked with when it comes to quickly identifying the problem areas and usage trends of a ColdFusion application.

After a day of monitoring the application performance with SeeFusion, it became quickly evident that a very critical page in their application needed some tuning – their “Browse Jobs” page. I could see via the SeeFusion logging repository that this page was continually running long (anywhere from 20-45 seconds), and was accessed very frequently in comparison to other pages on the site. So, I started looking into why this page was causing such headaches for our customer.

The page delivers some pretty simple functionality. It first shows a total number of active jobs available in the application. Then, just beneath that, it generates a list of all 50 states, a count of the jobs listings available in each state, and a parameter-driven link to a second-level page that then displays the jobs for that state. Pretty simple concept. When I accessed the page, the average execution time I was getting was around 24 seconds (24,000 ms) per request. This clearly needed to change.

The first place I typically start when evaluating a page or process like this is with the SQL operations. No matter how clean and optimized your CFML code may be, if your SQL calls are lackluster in the performance department, then it bleeds into everything else. You can’t build a great house on a tattered foundation, right? So I always begin with tidying up the SQL performance before moving on to any potential CFML optimizations.

I could see this page was running 3 varieties of SQL operations. First, a simple parameterless query to the tblJobPostings database table in order to determine the overall number of active job postings available to the entire site. Second, another parameterless query to the tblStates table in order to get a listing of all states. And third, a loop through the query of all 50 states in order to run a by-state query returning the count of jobs in each particular state. Put them all together and you have a total of 53 queries. Now, I immediately jumped out of my seat at the idea of running the by-state job counts from within a loop. But, I stayed focused on first task of SQL optimization before getting sidetracked – I’d get to that part later.

The query running within the loop of all states to get each by-state job count looked something like this.

Seems like a pretty simple query, but looks can be so deceiving when it comes to SQL performance if you’re not looking under the hood. I could already see in the ColdFusion debugging output that the 50 instances of this seemingly simple query were taking about 400-500ms each to execute. Multiply that average execution time by 50 states, and you can quickly see why the page was taking an average of 24,000 ms to render. So, I cut-n-paste an example of this query from the ColdFusion debugging output and brought it over into Query Analyzer in SQL Server Management Studio. I paste the query into Query Analyzer, enabled the “Include Actual Execution Plan” option, and executed the query.

I could immediately see some problems in the execution plan. SQL Server was performing a pretty costly index scan operation and then combining those results with a key lookup operation. This meant that SQL Server was utilizing an available index to match part of the selection criteria presented in the query, but was then having to perform additional work and filtering in order to evaluate other columns that weren’t a part of the index being used. Immediately, I took a look at the indexes on the tblJobPostings table. There was a clustered primary key index and a few single-column indexes, but not one multi-column index. This immediately explained the execution plan I was staring at. So, I created a new index on the table that included state, jobCode, expireDate, and jobPostingID in that order, essentially to match the conditions being applied in the WHERE clause and then including the columns referenced in the SELECT clause. With this approach to indexing, SQL Server should now be able to get all the information it needs for this query from the new index without even directly accessing the table itself.

With this new table index in place, I returned to the web browser and reloaded the page.

Execution time: 12,400 ms

I had already effectively doubled the performance throughput of the page simply by creating that index. But, 12 seconds was still far from being an acceptable load time. So I moved on to step 2 – reorganizing the CFML code.

Experience has taught me that running <cfquery>s from within a <cfloop> is almost never a good idea. Not only can it be a system memory hog, but it’s often a shortcut for not understanding how to get that same data in a single database call. When I had first glanced at this code, I had already made a mental note that I would need to re-write this into a single database call, grouping the job counts by state, and organizing it in a way that I could later reference individual job counts by simply indicating a state.

Re-writing the query was the easy part.

This addressed the issue of getting all the counts in a single database call, but didn’t directly address the problem of how I would go about referencing the job counts from within the <cfloop> through all states. But this is a technique I use all the time, so I quickly moved onward. I had decided that converting this query into a structure would be my best option. I would create the structure with keys for each state abbreviation and a value of the respective job count for that state. The code looks something like this.

The result of this code is a structure that visually might look something like this:

With the data organized this way, I can use the following bit of code to get the specific job count of a given state.

At this point, I was ready to move ahead with adapting the code within the <cfloop> to utilize this new data structure. Here’s a summary of original code.

But with the new single grouped query to get job totals, and the conversion of that query to a structure, I could re-write the code to look something like this.

It’s worth noting that the structKeyExists() call is critical here since the possibility exists that there may be 0 active jobs for one or more states that were separately returned from the states table in qStates. For example, if “TX” exists in the states table, but there are no active jobs in tbjJobPostings for state “TX”, then there will not be a record in the qJobCountsByState query for “TX”. That means a reference to stcJobCountsByState["TX"] would return an error indicating that the specified key does not exist. The structKeyExists() method call addresses this issue.

With this new method of deriving by-state job counts in place, I returned once again to the web browser to see how my work was paying off in the performance department. I clicked the refresh button in my web browser and scrolled to the debugging output.

Execution time: 2,120 ms

Alright, well now we’re getting somewhere. Typical human factors studies of web users would indicate that 2 seconds is reasonable when it comes to perceived wait times for a given page in a web application. However, I knew I could do better than this. I knew that the list of all states wasn’t likely to change very often (if ever), and that the job postings on the site were only updated once a night by a scheduled import process. This means that during the business day when the site is being access by the end users, the data being utilized to render this page isn’t changing. While I’m typically an advocate of always writing your own caching methods in ColdFusion for better control and management, this seemed like an ideal opportunity to take the easier path and simply cache the database queries on this page using the cachedWithin argument of the <cfquery> tag. So, I returned to the code and altered it by adding a 1 hour cache directive to the 3 remaining <cfquery> tags.

With this change in place, ColdFusion will handle all the logic associated with caching and refreshing the cached data from these queries every hour. One request per hour will run the queries against the database, but any other requests in that same hour will be fetching the cached query object from ColdFusion’s in-memory cache. Once again, I returned to the web browser, clicked my refresh button, and scrolled to the debugging output.

Execution time: 24 ms

Bingo! The page rendered almost faster than I could release the browser refresh button. I could see from the debugging output that the page was now referencing the 3 queries from the cache. Granted, once an hour a single request will actually have to run them against the database, but even then the execution time will only take about 2 seconds.

So, from an original page that started off with an average execution time of 24 seconds (24,000 ms), I had worked through a series of optimizations and come to rest on a new average execution time of 24 ms. In the span of roughly 30 minutes, I had created a 1000x increase in the performance throughput of this commonly requested area of the application.

If you have an area of your existing web application that seems to be suffering from some performance challenges, I’d encourage you to have another look and see what you can do about it. Review your SQL statement performance, optimize your database indexes, tweak your application logic, and apply caching where you can afford to do so. If you don’t feel comfortable doing it on your own or simply would prefer to have someone else do the work for you, then pickup the phone, call Webapper, and put our expertise in this area to work for you. The result just might be a 1000x increase in performance.