By Justin R. Fidler
CNA, CPS, CCFD
Chief Technology Officer
Bantu, Inc.

One of the most popular large-scale, server-based database vendors, Oracle provides data storage facilities to millions of web applications, including those powered by ColdFusion. This article explores integrating Oracle databases into your ColdFusion applications, from both a performance and development perspective. While Oracle is used as the chief example, some of the concepts apply to other database vendors products as well.

Oracle comes in both major and minor revision numbers, many of which contain significant changes. For Oracle 8i (that is, version 8.1), there are a number of different revisions, the most current being 8.1.7 as of this writing. Typically, you should wait at least a few months before moving to a new revision to ensure that all the bugs are ironed out. If you have Oracle support (via MetaLink), you can view a list of all open bugs on their support site. In general, the newer revisions have better performance, so they are worth looking at closely.

In addition, there are different versions of each revision. The two that apply in the server arena are the Enterprise and Standard editions. The core database engine is basically the same, but Enterprise adds a few more features like replication, partitioning, and the distributed database option. Evaluating these features and how they're useful in your web application is outside the scope of this article, but sites requiring very high performance should definitely evaluate the differences.

Oracle runs on all major platforms, including Windows NT, Solaris, Linux, and other flavors of Unix. You should test the database very thoroughly on your desired platform. At Bantu, we prefer to run Oracle on Solaris because it's one of the most popular high-end platforms for Oracle. Therefore, other customers have likely already found any potential problems. Because a Linux version of Oracle has only been around since version 8.0, be careful with this combination because there are fewer existing installations for testing.

On many sites, the database is a single point of failure, either by design or for cost purposes. (Oracle licenses aren't cheap, so a secondary server can be a big cost issue.) It's a lot easier to add another ColdFusion server than it is another database server. Because of this, a very robust hardware platform must be used for the database.

To separate traffic and ensure maximum network throughput, use dual network interface cards (NICs) for your ColdFusion servers. One NIC will face the external world for answering HTTP requests and the other NIC will use an internal address (for example, 192.168.x) for communicating with the database.

The database server need only have a single NIC on the internal network. As an added bonus, this keeps your database more secure because it will reside in an address space that's not externally addressable and routable.

The Enterprise Edition of ColdFusion Server adds a variety of features, including native Oracle drivers. Included in the Professional Edition, Open Database Connectivity (ODBC) support for Oracle is much slower than its native drivers. If you're thinking of migrating to an Oracle database, be sure to budget in an upgrade to the Enterprise Edition of ColdFusion for the native Oracle drivers. Those native Oracle drivers are better supported because they form the baseline, whereas ODBC drivers are added in later (by Oracle) as a secondary access method.

In the ColdFusion Server Administrator, you can configure the native drivers. Because no "Oracle 8i" selection exists, use the "Oracle80" selection when connecting to an Oracle 8i database over Net8, as shown below. Setting up a system data source name (DSN) is optional, because it is only used by ODBC drivers.

Establishing a database connection with an Oracle database can take anywhere from 0.5 to 3 seconds. Therefore, you'll want to enable connection pooling, which is done in the native drivers setup. At Bantu, we set the connection pool maximum to be the same as the maximum number of requests the ColdFusion Server can make, which works well assuming all your CFML pages need database access.

Be aware that the Oracle server itself limits the maximum number of connections it will accept. Make sure your database administrator (DBA) configures this to coincide with your ColdFusion servers.

Some of the blame for poor database performance can be laid at the feet of poorly designed queries. A variety of tactics can be pursued to combat this problem, such as using Oracle's "explain plan" feature, ColdFusion's bind variables, and other methods.

As explained in Oracle's documentation, Oracle's explain plan feature enables you to submit a query, after which it will return the execution plan it uses to get the results by listing what indexes the database used to assemble the query results and how it read the table. At Bantu, we set up a CFML page that lets our developers type in the query, get the query plan from Oracle, and display it.

Keep in mind that the results of the explain plan are data-dependent. If you have separate development and production databases (as you should), and your development database doesn't have the same type and amount of data as your production database, your query plan results will likely be different.

For example, suppose the PRODUCTS table on your development site contains 10 records but the same table on your production site contains 10,000 records. A query plan run against the development site may show a full table scan was used. Ordinarily, this is a bad thing because an indexed read should be performed. However, the Oracle query engine noted that there are only 10 records and, in cases of only a few records in a table, a full table scan may be more efficient (that is, direct block reads instead of index reads then block reads).

The same query plan run on the production database should show an indexed read. That's because the database has many more records and a full table scan is likely inefficient. To solve this problem, give your developers some sort of query plan about how to access your production database. Note that running a query plan doesn't actually run the query; it merely explains how Oracle would run the query.

The previous explanation of query plans is very high-level. There are many nuances to database and query tuning. Oracle has an entire manual devoted to query tuning, which you can read online at the Oracle Technet. You should also consult with your DBA to ensure the database is tuned properly on the server-side. There are hundreds of tuning parameters that can be adjusted on the Oracle server-side that can affect performance.

Miscellaneous Query Coding Tips

With the cfqueryparam tag (ColdFusion Server version 4.5), bind variables can produce performance improvements of more than 500 percent. (See my article, "CFQUERYPARAM and Oracle Databases" to read more about it.)

When you code SELECT statements, do not use "SELECT *". Instead, list out each field needed. Because you won't need data from every field, querying all of them just sends unnecessary data across the line between the ColdFusion server and the Oracle database. Also, using a "*" forces the Oracle query parser to look up the names of all the fields in the table and then substitute in the field list in place of the "*". This takes extra time and can slow performance.

List out fields when doing an INSERT. Instead of coding your insert as the following:

INSERT INTO tablename VALUES(...)

You should actually list out the fieldnames as follows:

INSERT INTO tablename(field1,field2,...) VALUES(...)

If you have different field-ordering on your development vs. production databases, your code isn't tied to any particular field-ordering by listing out field names. The field-ordering difference on development vs. production databases is actually fairly likely to happen over time if you add new fields to an existing table.

On your development database, where preservation of data isn't important, you may opt to drop and recreate the table, interspersing the new fields with the existing fields. On the production database, where you can't drop the table, you might just add the fields to the end.

In addition, if you have a required field but you don't know the proper value yet (see the upcoming carrier shipping preference example below), you can let Oracle insert the default you've defined. Thus, you don't need to track the proper default in your application code.

The Oracle date and number functions provided within Oracle are very fast. Do not be afraid to use them in your queries. For instance, if you need to multiply a price field by 1.05 (price plus 5 percent) for tax purposes, it's OK to do this in the database. The query might look like the following:

SELECT product_id, price*1.05 as taxprice
FROM products
WHERE category_id=6

It just so happens that the Oracle number and computation engine is written directly into the database. Therefore, simple arithmetic functions like this are performed very quickly and efficiently. (In the preceding example, use bind variables for both the 1.05 and the category_id WHERE clause because both can be bound with the cfqueryparam tag.)

The same goes for date functions, such as TO_DATE and TO_CHAR. More complex date functions like LAST_DAY (which returns the last day of a given month) are better handled outside of the database.

Handling NULLs in any database is not an easy task. Evaluating NULL in a WHERE clause of a query must use "IS NULL" as opposed to "= NULL", and you may get strange results with aggregate functions, such as MAX().

Oracle provides a built-in function, NVL, to make NULL easier to handle. NVL lets you indicate to Oracle what non-NULL value to return if the value is NULL. For example, it's possible that the PRICE field in a table of products can be NULL, but no product can have a negative value for PRICE. If you want to perform some sort of evaluation in your CFML code, such as finding items within a price range that also meet another criterion, a NULL value could cause problems. A GTE evaluation on a NULL value could return unintended results.

Therefore, by using NVL, you can ensure that a valid value is returned. For demonstration purposes, look at the following query:

SELECT product_id, NVL(price,-1) as NULLprice
FROM products
WHERE category_id=6

If the price field is NULL, the use of the NVL function above will return the number value "-1". Please note that if you're using this function and having it return a datatype that is not the same as the default datatype, you'll need to convert the overall return value. For example, if you would like to return the text value "noprice" when the price is NULL, all values of the price will need to be converted to a text value, as follows:

SELECT product_id, NVL(TO_CHAR(price),'noprice') as NULLprice
FROM products
WHERE category_id=6

Like the tax computation example in the previous section, you can use bind variables (the cfqueryparam tag) for the NVL value and for the WHERE clause.

The NVL is also useful with aggregate functions. For instance, you want to find the price of the most expensive item in the products table with category_id=6. If there are no items in that category, it will return NULL. However, if your application code is expecting some sort of numeric value, this can cause a problem. Instead, set it to 0 if there are no items, as follows:

SELECT NVL(MAX(price),0) as maxprice
FROM products
WHERE category_id=6

Because the MAX function itself returns a NULL value if no rows are found, the NVL occurs after the MAX function.

As an aside, because NULL has no value, it can't be placed in an indexed list. Thus, Oracle cannot index NULL values. A query to find all items with a NULL value will always do a full table scan. Therefore, you should use NULL values sparingly.

When you design tables, start off by assuming all fields should not allow NULLs unless you can think of a good reason to allow them. As long as that field isn't the subject of your WHERE clause and you're querying for NULLs on that field, it's OK to have NULL values. A table that stores customer information may allow NULLs in the second line of the street address. That's acceptable because that particular field is rarely queried.

In other cases, you can often design around a NULL field. For example, your customer record uses a single character field to track the customer's shipping carrier preference—for example, U=UPS, F=FedEx, etc. If the customer has never completed an order, he or she may not have a preference yet.

One solution would be to store a NULL value in that field for customers who don't have a shipping preference. A better solution would be to create a value that indicates no preference, perhaps "N". You can also define a default value for a field if none is provided upon table creation in Oracle—perhaps "N" as well. While the field no longer allows NULLs, it will work if the user does not input data.

Sometimes you may have a field where the possible values are few but, for storage space reasons, you do not enumerate the field. For instance, take a USERS table where the field GENDER is a one-character text field. It's a required field with three values (that is, "M", "F", and "N" if gender is unknown).

In a page that outputs this data, you may prefer to print the full gender value (for example, "Male" instead of "M"). To do this in the database, you could design a separate lookup table and perform a join on it. However, since the possible gender values will never change over time, it's faster not to do the join and use Oracle's built-in DECODE statement to print the values.

The DECODE function takes a list of field values and the value it should return in the query. In the following example:

SELECT DECODE(gender,'M','Male','F','Female','N','Unknown') as fullgender
FROM users

The first parameter is the database field to be used. The subsequent parameters consist of pairs of values, with the first parameter in the pair being the value in the database and the second being the value the query should return. It's also possible to specify a default return value in the case where there's a value in the database that's not specifically enumerated in the DECODE list.

In ColdFusion, the standard date type is an ODBC date format. Unfortunately, Oracle doesn't like this format. There are many nuances to the Oracle date format, and it can vary depending on the date type on your ColdFusion server and the date type setup in your Oracle configuration file on the Oracle server.

Instead of dealing with all these problems, it's much easier to convert the date to a character and pass it through to the date conversion function in Oracle. If you're doing date conversion, you need to force character-date conversion as a string variable, such as:

<CFSET l_In_date="#Now()#">
<CFQUERY name="qry_calendar" datasource="DSN_NAME">
SELECT event_id
FROM calendar
WHERE start_date > TO_DATE('#DateFormat(l_In_Date,"MMDDYYYY")#','MMDDYYYY')
</cfquery>

The line l_In_Date, which is a ColdFusion date data type, uses the ColdFusion function DateFormat to convert it to a specific string with format MMDDYYYY. Next, it tells Oracle to produce an Oracle date data type out of a specified string format (that is, MMDDYYYY).

Note how the l_In_Date variable is converted to a string when passed to the TO_DATE function. This ensures no other conversion will take place. When querying dates, you can use Oracle's built-in date formatting. On most database installs, it will even do language translation of "named" date data (for example, "Monday" as the day of the week).

Let's say you want to get the date returned in the following format: day of the week, day of the month, year, and 24-hour time format. The query would look like the following:

SELECT TO_CHAR(sale_date, 'Day DD Month YYYY HH24:MI:SS') as nice_sale_date
FROM sales
WHERE sales_id=3939

The result would be the following:

Nice_sale_date
------------------
Wednesday 25 October   2000 00:16:13

Beware of the space formatting. Oracle will space-pad date elements of the date that can be variable lengths, such as the month name.

To get the same result in French, add an NLS formatting specifier in the query, as follows:

SELECT TO_CHAR(sale_date,'Day DD Month YYYY HH24:MI:SS','NLS_DATE_LANGUAGE=
FRENCH') as nice_sale_date
FROM sales
WHERE sales_id=3939

Note how the name of the day and month are now in French:

Nice_sale_date
------------------
Mercredi 25 Octobre   2000 00:16:13

While language support depends on the database install, most installations include support for Western European languages. The Bantu site works in six languages with the proper NLS specifier being passed to Oracle depending on the language selected by the user.

For more information on date formatting with the cfqueryparam tag, see my article, "CFQUERYPARAM and Oracle Databases," or refer to the Oracle Server SQL Reference manual for a list of all date formatting specifiers.

Inserting rows with a numeric, sequential, or unique primary key remains a requirement in almost all application development. In Oracle, sequence objects accomplish this task. A sequence is an object that, when the "next value" is requested from it, is guaranteed to be unique. Depending on how you configure it, it may not always be sequential, but it will be close. In most applications, the requirement is only that the number is unique. Being in sequence (that is, without gaps) is not as important.

Sequences are not tied to a specific table or field. They're a standalone object, just like a table, view, or stored procedure. While at first this may not seem desirable, in large-scale systems it can actually be very useful.

In some of our applications at Bantu, we have a number of tables that need a unique, numeric primary key. However, the unique number doesn't need to be sequential. Therefore, we use the same sequence for a variety of tables instead of creating a different sequence for each table. This way, whenever we add a new table, we don't need to create a new sequence, and we don't have to worry about which sequence maps to which table. One sequence is used for all tables.

Because sequences are also not inserted automatically into any field, you will need to code them. This can be done at row-level before the insert trigger in your application. At Bantu, we keep the logic for this on the web application (ColdFusion) side. If an application puts in a sequence value that then gets overridden by the trigger value, bad things happen. We don't use triggers at all for this purpose.

On the application side, there are two situations that arise when performing inserts. Either you need to know the value of the key inserted—perhaps to display on a subsequent page—or the key value is unimportant as long as it's unique.

If you don't need to know the value, you can code the call directly in the INSERT statement. For example, let's say you're inserting a new row in the PRODUCTS table, and you would like to populate the product_id primary key number field using the APPLICATION_SEQ sequence. A call to NEXTVAL will do the following:

INSERT INTO products(product_id, product_name)
VALUES(APPLICATION_SEQ.NEXTVAL,'New Product')

When performing the insert, Oracle substitutes APPLICATION_SEQ.NEXTVAL with the "next" unique value for the APPLICATION_SEQ sequence. Because this is basically a function call and not a literal string, notice that this call does not contain single quotes. This also means that bind variables, through CFQUERYPARAM, can't be used. But this is OK because it's a function call to NEXTVAL and no parameters change.

In other cases, you may want to know the value used for the primary key. For example, after a user completes the registration process, your web application displays a confirmation page referenced by the primary key field. The easiest way to do this is with two CFQUERY calls. The first call gets the sequence number and saves it to a local ColdFusion variable. The second call inserts that value. In the following example:

<CFQUERY name="qry_user_id" datasource="DSN_NAME">
SELECT APPLICATION_SEQ.NEXTVAL as user_id_key
FROM dual
</CFQUERY>

"Dual" is a special Oracle table that always exists. It's a pseudo-table that always returns one row along with whatever is selected from it. You can select the current date, do arithmetic with it (for example, "SELECT 2+2 FROM dual"), or perform other function calls. The dual table exists because there's no other way to perform function calls against the Oracle database without running a query.

In the query above, take the NEXTVAL query result and assign it to a local variable, as follows:

<CFSET l_user_id=qry_user_id.user_id_key>

Then take this local variable and use it in the insert, as follows:

<CFQUERY datasource="DSN_NAME">
INSERT INTO USERS(user_id,name)
VALUES(#l_user_id#,'Justin')
</CFQUERY>

By default, cfquery requests records from the database one row at a time. If your query returns 100 rows, it will send 100 individual fetch requests to the database. As you might imagine, this can be rather slow and eat up server resources.

The blockfactor attribute specifies the number of rows to request at one time. You can set this as high as 100. If your page is going to list the first 15 products, for example, set the blockfactor to 15 and it will request them all at once. There's no need to set the blockfactor higher because it will just request extra, unnecessary data.

If you know your query will only return one row, you don't need to specify a blockfactor at all. If you don't know how many rows your query will return, but you know you need all of them, set the largest blockfactor possible (100), so it will fetch the rows in large blocks. Regardless, the blockfactor only applies to SELECT statements, not UPDATEs, DELETEs, or INSERTs.

In addition, the blockfactor attribute is only an internal command that tells the Oracle driver how many rows to fetch at a time and hold in cache. It does not affect your looping through the query. Therefore, each loop through the query will always return one row, regardless of blockfactor size. Your application code won't need to change after adding in a blockfactor .

The example below assumes you're displaying 10 products per page:

<cfquery name="qry_products" BLOCKFACTOR="10" datasource="DSN_NAME">
	SELECT product_id, product_name
	FROM products
</cfquery>

There's still much more you can do to increase your Oracle performance with ColdFusion. Spend a lot of time tuning the database and your data design. Also, make sure all your queries use the cfqueryparam tag, which is very important in Oracle.

Oracle is a very robust, high-end database, and you should be able to achieve excellent performance with a bit of work.

More Resources

About the Author

As Chief Technology Officer, Justin Fidler oversees all aspects of technology infrastructure, systems development and operations at Bantu, Inc, a leader in secure, business-grade Instant Messaging and Presence solutions. The Bantu IM & Presence Platform enhances communication and collaboration in industries such as financial services, telecommunications, government and education.

Justin was also CTO at IntraACTIVE, Inc., the predecessor company to Bantu, Inc., where he was responsible for the development of SiteKnowledge and InTandem, Web-based groupware products. Prior to IntraACTIVE, Inc. Justin developed systems for Oracle, NASA, the Department of Defense, and washingtonpost.com.

Justin holds a bachelor's degree in Information and Decision Systems from Carnegie Mellon University. He holds certifications from Macromedia, Microsoft, and Novell. He is also on the Board of Directors for the Presence and Availability Management (PAM) Forum, an industry consortium dedicated to giving users of diverse communications and messaging services greater control over where, when, how and by whom they are contacted. He can be reached at justin@team.bantu.com.