By Thomas Ortega II
In ColdFusion (CF), there are three ways to manipulate database recordsets: inline queries, cached queries, and stored procedures. They each have their strengths/weaknesses and appropriate times for usage. I will explain each of these methods, so that you can decide for yourself where and when you should use them.
The three methods are presented in order of complexity. Not necessarily complexity of code, but, rather, complexity of preparation. Inline queries take little time to prepare because you type in the SQL code to get the result you want. Cached queries take a minimal amount of outside thought, because you need to decide beforehand that the data is not going to change very frequently. Lastly, stored procedures take a considerable amount of time outside of your programming IDE to define the desired results and create the code to return those results.
For demonstration only
This article does not provide optimized SQL code or code logic in the enclosed examples. The code I've provided is merely for demonstration purposes.
Inline queries are the simplest of the three types, using one of CF’s most basic yet powerful tags. The <cfquery> tag is the foundation of the Web application experience, going back to the days when CFML was known as DBML. With a quick SQL statement, you’re well on your way to bringing a resultset from a database server into your application. Here is an example:
<cfquery name="Your_Info" datasource= "mainDB ">
select* from site_users where user_id = #client.user_id#
This code will select the matching information for this particular client’s user_id. Obviously, this is something that will have to be run every time a client requests this information since each client will have a different user_id.
There’s nothing technically wrong with this code. You could write every call to a database in this manner, but as your traffic increases, this method may begin to hinder the performance of your site. Why?
Think about what happens when the code runs. CF server processes the #client.user_id# value and replaces that variable string with the appropriate value, and then sends the query to the database server. The database server needs to process the SQL statement and gather a recordset; this recordset is then sent back to the CF server, and the recordset is stored in system memory until it can be further used by other code logic on the page. That’s a lot of steps for such a simple query.
Also, take into consideration that the application may be storing client variables in a database as well, which adds even more load to the database.
Cached queries bear a striking resemblance to an inline query, but with one major difference; they run only once, despite the fact that their resultset is used many times throughout the application. Here is an example of a cached query:
<cfquery name="States_Names_Abbr" datasource= "mainDB ">
selectstate_id, state_name, state_abbreviation from US_States
There are three main ways to make the above query a cached query: using the cachedAfter attribute, using the cachedWithin attribute, or making the recordset an application scope variable.
I prefer the application scope method. I do this by loading all of my cached queries at startup and then refreshing the queries by calling a specific page. I provide attributes via a URL or by passing parameters in the code that will set the cached queries to NULL or rerun the query. I do it this way so that I can selectively refresh queries whenever I need to without having to push out new code or bounce the CF Application Service, which deletes all application scope variables.
The problem I have with using cachedWithin and cachedAfter is that there hasn’t been a set lifespan for data in my applications. Sometimes I won’t refresh the data for three weeks, but then sometimes I may refresh it again three days later.
Looking at the query above, you might think, “Well, that’s good for a states drop-down list, but not much else.” Here’s where we begin to hit a bit of the complexity.
Say that you need to get this particular user's state name and abbreviation. Our DBA has normalized the data so that only a state_id is stored in the user table that refers to a state_id record in the states table. So, you would use this code:
<cfquery name="User_State_Info" datasource= "mainDB ">
selectstate_name, state_abbreviation from US_States
where state_id = #Your_Info.state_id#
This would work, but it would make another database call. If you had cached the States_Names_Abbr query, you could do this call instead:
<cfquery name="User_State_Info" dbType="query ">
selectstate_name, state_abbreviation from States_Names_Abbr
where state_id = #Your_Info.state_id#
Now, the CF server will merely access the recordset in system memory, which will be faster and less stressful for the machine than going out to the database server.
Stored procedures are completely different from the two methods I mentioned above. This method requires major work with the SQL code and some DBA support. You must carefully lay out what you want to achieve with the stored procedure far in advance of coding it, because what you need will determine how you code.
The CF code involved in calling the stored procedure and receiving the desired recordset is shown in Listing A.
With the call shown in Listing A, you could execute hundreds of lines of database logic contained within the stored procedure. This can make your CF coding easier because you’ve spent the time determining which recordset (and the appropriate columns) you will be getting back. So, your backend database may switch, newer security methods may be put into place, and new datasources may be utilized, but your stored procedure call may easily stay the same. It makes sense to put business logic or calls to numerous tables into a stored procedure for this reason. Not to mention that, in many database servers, the SQL code itself gets further optimized every time it is run. Imagine that, code that gets faster the more you use it!
Pros and cons
You’re not without options when using ColdFusion to talk to a database. If you need to code quickly and/or the data results will constantly be changing, you’re better off using inline queries within your pages. If you know that a recordset will be used more than once and/or the data will change infrequently, it’s safe to use cached queries. If the logic is complex or touches numerous tables, stored procedures are the way to go.
Once you understand these three techniques, you can begin to use them as more advanced methods. If you find your site slow to respond, one of the first culprits to look for is an abundance of inline queries hogging up resources. In fact, I’ve often come in to projects where the Application.cfm will call the same group of queries every time a request is made. Armed with the above knowledge, you’ll be able to spot troublesome areas such as this, and promptly rectify the situation.