linkfoxo 摘自 http://www.allaire.com/index.cfm?id=18427&Method=full#7

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

Introduction

ColdFusion pioneered database-driven Web application development. 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 will explore integrating Oracle databases into your ColdFusion applications. Techniques unique to Oracle will be examined from both a performance and development perspective. While Oracle will be used as the example, some of the concepts will apply to other database vendors' products as well.

• The Basics
• Coding Good Queries
• NULL Handling
• Lists of Values
• Handling Dates
• Using Sequences
• Use BLOCKFACTOR in Queries
• Conclusion
• About the Author

Oracle has both major and minor revision numbers, many of which contain significant changes. For Oracle 8i (i.e., 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 for at least a few months before moving to a new revision to ensure all the bugs are worked 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 will run on all major platforms, including 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 platforms for Oracle on the high-end. 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 (e.g., 192.168.x) address 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 since it will reside in an address space that is 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. The native Oracle drivers are better supported because native Oracle drivers are 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 (see Figure 1). You don't need to setup any system DSNs because they are only used by ODBC.

Establishing a database connection with an Oracle database takes 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 has a limit on the maximum number of connections it will accept. Make sure your database administrator (DBA) configured 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.

Explained in Oracle's documentation, Oracle's explain plan feature enables you to submit a query, and it will return the execution plan it uses to get the query 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 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, 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 (i.e., 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 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

• Introduced with ColdFusion 4.5 with the introduction of CFQUERYPARAM tag, bind variables can produce performance improvements of more than 500 percent. Click here to read more.
• 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 INSERT INTO tablename VALUES(?, you should actually list out the fieldnames 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, and the new fields might be interspersed 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), 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 this:

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 CFQUERYPARAM).

The same goes for date functions, such as TO_DATE and TO_CHAR. More complex date functions like LAST_DAY (returns the last day of a given month) will be better handled outside 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 criteria, a NULL value could cause problems. A GTE evaluation on a NULL value could return unintended results.

Therefore, by using NVL, you can ensure 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:

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 (<CFQUERYPARAM>) 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:

SELECT NVL(MAX(price),0) as maxprice

FROM products

WHERE category_id=6

Because the MAX function itself will return 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 NULLs. As long as that field isn't the subject of your WHERE clause and you're querying for NULLs on that field, it's okay 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 (e.g., 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 for tracking the user's gender. It's a required field with three values (i.e., "M", "F", and "N" if gender is unknown).

In a page that outputs this data, you may prefer to print the full gender value (e.g., "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. Here's an 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 value 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 a lot of nuances to the Oracle date format, and it can actually 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 (i.e., 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 (e.g., "Monday" as the day of the week).

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

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:

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 month name.

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

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 CFQUERYPARAM, click here to be taken to a related article. 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 (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 either be done at row-level before the insert trigger in your application. At Bantu, we keep the logic for this on the Web application (i.e., ColdFusion) side. If an application puts in a sequence value that then gets overridden by the trigger value, bad things will happen. So, 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 this:

INSERT INTO products(product_id, product_name)

VALUES(APPLICATION_SEQ.NEXTVAL,'New Product')

When performing the insert, Oracle will substitute 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 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 will get the sequence number and save it to a local ColdFusion variable. The second call will insert that value. Here's an 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 will always return one row and whatever is selected from it. You can select the current date, do arithmetic with it (e.g., 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.

<CFSET l_user_id=qry_user_id.user_id_key>

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

<CFQUERY datasource="DSN_NAME">

INSERT INTO USERS(user_id,name)

VALUES(#l_user_id#,'Justin')

</CFQUERY>

By default, CFQUERY will request 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 eats up server resources.

The parameter BLOCKFACTOR specifies the number of rows to request at one time, and can be set 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 be requesting 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 (i.e., 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, BLOCKFACTOR 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>

Of course, there's still more that can be done 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 CFQUERYPARAM, 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.

As Chief Technology Officer, Justin Fidler oversees all aspects of technology infrastructure, systems development and operations at Bantu, Inc, a leading provider of Web-based instant communication to businesses, ISPs and Web sites. Founded in 1999, Bantu operates one of the largest interoperable instant messaging networks reaching more than 85 million users.

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 Allaire, Microsoft, and Novell. He can be reached at justin@team.bantu.com.