Letting the Database Do Its Job

Databases are built and optimized to manipulate data. For instance, SQL's aggregate functions are faster at finding an average or a sum of a set of numbers than at reading the set of numbers, looping over them with a <cfoutput query=""> tag, and doing the arithmetic in ColdFusion. Therefore, you should use your database to enforce referential integrity, apply constraints, and use triggers when one database activity needs to cause another to be performed.

In addition, avoid performing calculations or analysis in database data within ColdFusion. Wherever possible use stored procedures to achieve this. As a rule, any time data retrieved in one query is passed to a second query, that operation should be a stored procedure.

Stored procedures were reviewed in Chapter 47, "Advanced Database Features."