When your ColdFusion application starts to slow down especially during queries then it is time to analyze and improve your application’s performance. You can optimize the performance of your queries by making modifications to your SQL statements Administrator and data source settings and your database configuration.
Optimizing your SQL queries means that elements in your queries are organized and referenced properly. Most importantly, you have to analyze the elements in your FROM and WHERE clauses. Here are some basic guidelines for optimizing your SQL queries
• In the FROM clause list the larger tables last.
• In the WHERE clause list the most restrictive condition first. The most restrictive condition yields the least number of rows of data
• Limit the amount of columns included in a GROUP BY clause.
• Avoid using the HAVING clause.
• Avoid using the OR operator and use IN instead.
• Avoid using the LIKE operator and wildcards frequently.
• Convert.recurri’ng SQL statements to stored procedures for faster execution.
• Text fields and other unbound data columns should be placed last in the SELECT . statement.
• Try to use COUNT( *) and not COUNT( column_name).
• If you are performing large queries on your database, you should frequently use the CFTRANSACTION tag to commit or rollback the results.
Remember these guidelines are not cut and dried. You will still need to experiment run tests and timeline executions to make sure that your optimizing procedure works
Data Source/Administrator Settings
In order to get the best performance out of your applications it is wise to make sure the settings in your ColdFusion administrator are optimized. Here are some suggestions to keep in mind:
• For each Access ODBC data source, set the following in the EDIT ODBC Data source-CF Settings screen:
• Buffer Size = 0
• Page Timeout = 600
• Limit Connections should be checked
• Enable Limit of _simultaneous connections should be set to 1
• Maintain Database Connections should not be checked
• Better yet, do not use ODBC to connect to an Access database. Instead, use OLE DB (requires MDAC 2.1), which is more robust and scalable.
Database optimization means doing something to your database to improve its performance. It may also mean restructuring the database and the design and layout of the tables. One of the first things that you should check is the index. What should be indexed? Obviously the primary keys in your tables foreign keys and columns that are used for table joins. Other candidates for indexing are columns that are referenced by ORDER BY or GROUP BY clauses. Columns frequently used in WHERE clauses should also be considered. The best way to accomplish this is to run a test with or without certain indexes. With your Cold Fusion debug turned on you can see how long it takes to process queries.
While we have provided the following suggestions here you should always check your database documentation for more assistance
• When you expect the heaviest traffic on the database, avoid running very large processing queries.
• Do not run batch-processing queries while your table is indexed. Indexes can slow
down queries that are accessing most of the table records.
• Do not create indexes on small tables.
• try to minimize the size of the database.
• Store the database transaction log on another drive.
• Add processors to your computer to allow for parallel processing. ‘
• Add RAM as much as possible, but not less than 256.
• If possible, store tables and indexes on separate drives.
Inevitably, you will run across errors during the development of any application. These errors could be due to numerous problems. They could stem from problems with your SQL statements, problems with your database setup, problems with your ColdFusion code, or a variety of other issues
To assist you in troubleshooting these problems and to help prevent them from ever occurring, many of them have been detailed here. If you are not finding what you need here, you can search the Allaire Forums (http://forums.a11 ai re . com),check out the resources detailed at the end of this chapter, or if all else fails, you can contact Allaire’s support desk
Incorrect SQL code is usually the cause of most application problems. This may partially be because many ColdFusion developers are not database or SQL experts. At the same time if this is your problem it is probably much easier to fix than if your database has been corrupted or is not configured properly .
When using quotes in your SQL statements within your CFQUERY tags you should use the following guidelines:
• Place single quotes (‘) around a field’s argument if it is a text or memo field type.
• Place single quotes around dates for Microsoft SQL Server data fields.
• Do not place quotes around numeric, auto number, and binary field types.
• Do not place quotes around MS Access dates.
• Never use double quotes (“).
If you need to use single quotes in your SQL statement and want to preserve the single quotes instead of having them automatically escaped by ColdFusion, use the preservesing function..
Varying Data Types
When you are inserting or updating data in your database, you need to make sure that the data being passed to the database fits the field type specified in the database. If you try to insert text into a numeric field you will receive an error. To assist in validating data types use the CFQUERYPARA tag described earlier in this chapter.
Null and Zero-Length Strings
Fields in the database can be defined so that a NULLvalue will not be accepted. If you try to insert a new record into a database and do not pass data for NOTNULLfields, you will receive an error. There are several ways to deal with this.
first you can remove the NOT NULL limitation from the field. If you do not have access to the database then you can use the ALTERclause to drop the particular field and then redefine it as a NULLfield. Altering tables is discussed earlier in this chapter
Second, you can pass data to the field. This can even be a space if necessary.
Versions of SQL
You have already seen in Chapter 10 and in this chapter how SQL can vary depending upon the back end database. Always try to use ANSI standard SQL that is supported by most databases. Unfortunately. there is.no one way of coding your .3QLthat will always work with all databases. For information on other databases, please consult that database’s documentation. Some of the differences between Access and SQL server are described here:
• DELETE* FROMis not supported by SQL Server; use DELETEFROMinstead.
• DISTINCTRO~is not supported by SQL Server; use DISTINCTinstead.
• ORDERBYis not supported by SQL Server in a sub query.
• GROUP BYcannot contain more than 16 fields when used with SQL Server.
• WHEREfi e1d_name •• t rue is lot supported by SQL Server; use WHERE fi e1d_name – 1 instead.
• WHEREfi e1d_name = NULLis not supported by SQL Server; use WHERE -fi e 1d_name IS NULLinstead.
• Concatenating fields with the ampersand (&) is not supported in SQL Server; use the plus (+) operator instead.
• If you plan to convert your Access database to SQL Server review the following
• Bit fields default to no in Access. There is no default in SQL Server; you must always specify a value. You can change this by adding a default value to the field in SQL Server.
• To create an autonumber field in Access, use the counter data type. In SQL Server use the i denti ty data type set to uni que.
• SQL Server 7 has a limit of 2,147,483,647 bytes for text data types.
• If you are not using ANSI standard SQL, your application may throw errors when switching from ODBC to OLE DB
ColdFusion Query Problems
This section details both problems related to using CFQUERYand useful techniques for solving those problems.
Using DEBUG and REQUESTTIMEOUT
While your applications are in development it is a good idea to turn on all of the debug settings in the ColdFusion Administrator. The debug information is useful for interpreting error messages and what went wrong. The SQL query will be displayed along with specific error messages related to ColdFusion or your database. Check the ODBC Errors section later in this chapter for more information
If you do not have access to the Administrator, then use the DEBUGattribute of the CFQUERYtag. You can also append the following parameter to a URL in order to timeout a query that is taking too long to execute: REQUESTIIMEOUT-seconds.
If by chance you come across the CFSQL tag you should know that it has been obsolete since very early versions of ColdFusion. You will need to convert all CFSQLtags to CFQUERYtags .
The following are some common ODBC errors encountered while developing ColdFusion applications
• OOBCError Code = 07001 (Wrong number of parameters) [MicrosoftJEODBC Microsoft Access 97 Driver] Too few pa.-ameters. Expected
This error can be due to improper use of the. quotes within your SQL statement.
• OOBCError Code = IM002. IM003 or IM004
You should consider using the following functions to help you build your SQL queries and solve SQL problems you might come across:
• IsDefi nedO
Y2K Patch for Oracle Stored Procedures
If you are using CFSTOREDPROaCnd Oracle 7.3 and 8.x, there is a patch available to fix a problem in interpreting the year 2000. The patch can be found at ‘II’Iffl. a11ai re. com/ hand1ers/index.cfm?ID=13202&Aethod=Fu11.
Finding Additional References for the Various Databases
There is much more to learn about the various databases that you will be uslng for your ColdFusion application. The best place to start is the free ColdFusion Support Forum located
You can post your questions and concerns there and you can also search for certain topics that interest you. The forum is divided into topics. For topics covered in this chapter check out the “Database Access and Query Building” area. Cold Fusion’s Web site also has a section called the Knowledge Base. It is a compilation of topics that include problem discussion and analysis and workarounds. It is located at Base articles relevant to this chapter are:
Article 7606, “Connecting with Oracle 7.3 Native Driver