The most common problem I've run into with applications slow performance is the lack of indexes on the databases. Sometimes this is just because the application ran fine with a small amount of data but now is working off a larger amount, or the application is being used in unexpected ways (running less 'common' queries run more often), or the production environment doesn't have the same indexes as the development environment or worse case the indexes were never created in the first place.
Without indexes queries can take seconds with indexes they take milliseconds!
Most ColdFusion applications I'm come across tend to use a single datasource or if they use more than one the same user credentials are used. As well as causing possible performance and scalability issues this can be a security risk.
It's quite easy to restrict what SQL statements a datasource will run with the ColdFusion administrator.
Last week when I looked at databases pools I got a few suggestions re master/slaves databases. In this configuration you set up a single database (called the master) and have it replicate to one or more other databases (called the slaves).
You've tuned your databases queries, added all of the indexes you can think of and cached all of the queries you can but your database still isn't giving you the performance you need.
So it's time to split up your database into several databases and move each of these new databases off onto separate database servers. Or perhaps a RAID database is the solution to your performance issues.
Consider the following code that implements a simple login.
select * from users
where name = '#form.login#' and password = '#form.password#'
// if login failed go back to login page
<cfif user.recordcount is 0>
One of the issues with this is that the password is stored in the database as plain text. Anything with access to the database (including your ColdFusion application) could possibly be used to get a list of all users and their passwords.