I had to gather some information to share with my team to promote the use of Database views versus CFQUERY. Figured I’d share with everyone else, too.
When I started with ColdFusion I never had access to a powerful database. Granted that was back 8 years ago. I was taught on ColdFusion 4.5 Express and MS Access. Which did the trick for those quick e-commerce sites that got my CF career in motion. But when I took that official CF job, and by official I mean a regular paycheck, things changed quite a bit.
A high traffic site with an Oracle DB on the back-end was a nice change. The first habit to break was the over use of the CFQUERY tag. It was almost a ‘no-no’ at the shop I was working in. After all, why use ColdFusion for database transactions when Oracle, and the server it ran on, cost a lot more. That’s looking at it from the business side of things…
The technical side is a little more involved but makes sense after you break it down.
You need to understand what is involved when using the CFQUERY tag. There are a few steps to get that data where you want it:
(note) – If it is the first time the CF template is being processed extra time will be added to compile and cache it.
1. CF replaces all dynamic values in your SQL (dbField = #variableName#) and binds your cfqueryparams with the actual values for that request.
2. Then it sends the query to the database server.
3. The database server compiles it.
4. Runs the query and returns the result set to the CF server.
5. The data is then stored in the CF server’s memory for you to manipulate with more CF code.
(note) – If you are storing Client variables in the DB, then consider the load added with those trips back and forth.
The process may not seem extreme or unnecessary, yet…
What about caching the queries, you ask? A great idea, however, you’re approach matters. Using the CFQUERY params like cachedWithin and cachedAfter are restricted by timespans. Which is fine if your application handles the data in the same matter. Often, that’s not the case, and the data will need to be refreshed or updated at different intervals and request throughout your application.
What about caching them in one of your scopes (application, session, etc)? This is a better idea. It gives you control over when queries are cached, and how often the data is refreshed. It’s a practice I was using often, until just recently. But that’s a topic for another post coming soon (which I will include a nice caching component for public use, as well).
Now, we’ve covered the options CF gives us natively. Hopefully, you are not using MS Access and you have a solid DB behind your application. From what I can recall, VIEWS are supported by most of the major players (SQL Server, Oracle, Postgre, and MySql).
A VIEW is a virtual table that is pre-built from an existing query. The DB’s SQL engine will pre-compile them with built-in algorithms that optimize performance on the platform they are run on. They can also be indexed like a table, and we all know how great indexing is when it comes to performance gains. However, indexing a View is not as easy as a table.
Performance is the name of the game for high-end sites. It doesn’t matter how pretty or informational your site is if it takes too long to deliver.
As a Software Engineer you have to account for all parts of the system you are developing while enforcing best practices. Understanding how things work can only benefit you as a developer. We know all the steps involved in a simple CFQUERY and we know the pros of using a View. There is however, a con when using a View. You lose the power to substitute dynamic values in your SQL. A stored procedure will get that kind of job done, but that’s another topic on its own.
Let’s do what they do on the business side, and maximize our resources. We have this complex query we are using with CFQUERY, and we need that data. To begin, assume there are no dynamic values involved in the SQL.
SELECT * FROM tblOne INNER JOIN tblTwo ON tblOne.id = tblTwo.id
LEFT OUTER JOIN tblThree ON tblTwo.id = tblThree.id
WHERE tblOne.isActive = 1 AND tblTwo.isApproved = 1
We are going to take our well-formed query and create a View in the DB with it. You can script this by just adding ‘CREATE VIEW vwMyNewView AS’ before your query. We then update the CFQUERY tag to do a simple SELECT on our view.
SELECT * FROM vwOurNewView
It won’t take CF much to send that UPDATED query to the DB server. The DB server already has the original query optimized and returns the results in record time to CF. Nice little improvement. Now you have some options. You can add dynamic values to that CFQUERY if you want to filter with a WHERE clause or sort with an ORDER BY, or even treat it as a sub-query. If the situation allows for it, cache it (application scope being shown here).
SELECT * FROM vwOurNewView
WHERE categoryId = #form.categoryId#
ORDER BY #form.sortBy#
I ran some tests and the proof is in the pudding. The ORIGINAL query would take 234ms to return 1 record, while the UPDATED would take 65ms. That was on the initial run, so some extra time is in there to compile the CF before even getting to run the CFQUERY. So I ran it a bunch of times after to average things out…
ORIGINAL’s average was 65ms and UPDATED was 16ms.
That’s 4 times faster. Case closed.
TWEAKING THE ADMIN
On another note, take into consideration your datasource settings. Choosing to maintain the DB connections will keep an active connection between the CF server and the DB server opened based on activity. This will allow for faster results if there is an existing connection to use. There is a limit on the number of pooled connections, and time limits on their lifetime when idle. If one gets dropped and the others are in use, time is added to wait and re-establish a new connection. So try to figure out the optimal values for those ADVANCE SETTINGS under your datasource.
The other section to look into is the Cache settings. Queries and templates can be cached by CF, and limited as well. So again, if it’s been ran recently and cached by CF it will run quicker. If it gets bumped out of the pool, time will be added to recompile.