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!
But how do you know what column to put an index on? If you do the following:
- Put an index every column used in a join
- Put an index on every column that's compared with another column or compared to parameter
- Put an index on every column that you sort by or group by
A few DBA's reading the above will cringe and say that's too many indexes and it will make inserts and updates slow or it will increase the size of the database. Generally web applications; make far more reads than updates/inserts on the databases, the databases are small to medium in size and hard disk space is cheap!
Indexes are easy to create.
You can create indexes via SQL like so:
create index <index name> on
And you can also create indexes via the
create index <index name> on <table name>(<column name>)
Make sure the SQL script or ColdFusion page it run as part of the build processes that way you know the live application has all of the correct indexes.