- Files
- Speed and Performance
- Database Issues
- Security Issues
- General Issues
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Database Issues
Working with relational databases is a central facet of ColdFusion development. Because all too often the person creating the database is the same person shouldered with development work, it's important to understand the implications of database calls in your applications. Here, we'll look at a few tips to improve performance and keep your applications solid.
Let the Database Do the Work
If you are going to perform some aggregate function on data that resides in your database, do it there. That's what it is made to do. Your code will run faster. You can do formatting of data in your code, however, so you have more control.
For instance, SQL includes a number of aggregate and scalar functions that are also available in ColdFusion.
Some of the functions that are replicated include most mathematical functions, date and time functions, trim string functions, and more. Remember that if you employ conditional logic or other ColdFusion code inside your SQL statement, ColdFusion will create the statement first and then will send the SQL statement off to the database. You do not incur a longer database connection time by writing complex CF code in your <CFQUERY>s.
Use the maxrows Attribute of the <cfquery> Tag
If you know how many rows you're supposed to get (say, you're checking for the existence of a matching username and password that should be unique, and therefore you have only one row returned), set the maxrows attribute to 1, and give your database a break.
Don't Let Query-of-Queries Capability Make You Lazy
You might find it tempting with the query-of-queries capability new in ColdFusion to think about loading your entire 3,000-row product table into Web server memory and start querying that for your e-commerce application. This is not careful designing or prudent use of resources. Determine what you need, and use only that. Determine your trade-offs, and act according to greatest benefit (do some tests).
There are major benefits to the new query-of-queries capability; among these are the ability to perform cross-datasource joins, cross-datasource unions, and in-memory denormalization. And, of course, it is faster to retrieve memory-resident values. All of these can really bring together enterprise business applications. That doesn't make query-of-queries a license to slouch, though.