Transcript Document

Optimizing Your ColdFusion
Applications for Oracle
Justin Fidler, CNA, CPS, CCFD
Chief Technology Officer
Bantu, Inc.
8 May 2001
1
Overview










2
Why Oracle?
Configuring Your Platform
Query Tuning
CFQUERYPARAM
Special Coding Techniques
NULL Handling
Lists of Values
Date Handling
BLOCKFACTOR
Further Reading, Questions
Why Oracle?






3
Industry-accepted platform
Runs on virtually any server platform
Large base of Oracle knowledge
Robust, scalable, proven technology
Widely supported
BUT .. It won’t be cheap!
Configuring Your Platform -- Database






Oracle 8i is the current popular version
Standard vs. Enterprise Edition
Beware of “App Server” focus
Have a DBA to help with configuration
Use an internal-address NIC (security)
Don’t skimp on hardware -- typically single point of
failure
 Perform routine maintenance (tablespace sizing, init
settings, backups, table index analysis)
4
Configuring Your Platform -- ColdFusion
 Only the Enterprise Edition provides native Oracle
drivers
 Use and enable connection pooling
 Set connection pooling similar to simultaneous request
limit, in most cases
 Beware of running out of connections -- have a DBA
look at settings
 Use a separate NIC for the database traffic
5
Query Tuning
 Most poor database performance results from poorlydesigned queries
 EXPLAIN PLAN will solve most of your problems
 Use TKPROF in severe cases
 Have your DBA closely check and monitor Oracle
performance statistics
6
Query Tuning - EXPLAIN PLAN
Bad Query:
SELECT u.gender
FROM users u, user_chat_preferences ucp
WHERE ucp.user_id = u.user_id
AND u.logon_id='justin’
Output:
SELECT STATEMENT Cost= 4806
NESTED LOOPS
TABLE ACCESS FULL USERS
TABLE ACCESS FULL USER_CHAT_PREFERENCES
7
Query Tuning - EXPLAIN PLAN
Good Query:
SELECT u.gender
FROM users u, user_chat_preferences ucp
WHERE ucp.user_id = u.user_id
AND u.logon_id='justin’
Output:
SELECT STATEMENT Cost= 4
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID USERS
INDEX UNIQUE SCAN UK_USERS_LOGON_ID
INDEX UNIQUE SCAN PK_USER_CHAT_PREFERENCES
8
CFQUERYPARAM




Introduced in CF4.5
Enormous performance improvement
Works with any database that supports “bind” variables
Oracle SQL statement cache is literal and casesensitive
 Statement cache determines execution plans
9
CFQUERYPARAM Before Example
Your Code before CFQUERYPARAM:
<CFQUERY DATASOURCE="DSN_NAME">
SELECT username
FROM users
WHERE user_id=#SESSION.USER_ID#
</CFQUERY>
In the Database before CFQUERYPARAM:
SELECT username FROM users WHERE user_id=2236
10
CFQUERYPARAM After Example
Your Code after CFQUERYPARAM:
<CFQUERY DATASOURCE="DSN_NAME">
SELECT username
FROM users
WHERE user_id=<CFQUERYPARAM VALUE="#SESSION.USER_ID#"
CFSQLTYPE="CF_SQL_NUMERIC">
</CFQUERY>
In the Database after CFQUERYPARAM:
SELECT username FROM users WHERE user_id=:1
11
CFQUERYPARAM Summary
 Works with all datatypes except BLOBS, including
dates, characters, numbers
 Null handling is done with the NULL=“YES” parameter
 Can be used on UPDATEs, INSERTs, SELECTs,
DELETEs
 Should be used for all literal and dynamic values
(parameterized values)
 Bind variable enumeration will appear in debug output
 No reason not to use CFQUERYPARAM
12
Special Coding Techniques
 SELECTs should use listed field names instead of
“SELECT *”
 SELECTs should only select the fields needed for the
query
 INSERT statements should list field names explicitly:
INSERT INTO tablename(field1,field2,…) VALUES
(value1,value2,…)
 Explicit field listing helps with different database field
ordering (production vs. development)
13
More Coding Techniques
 SIMPLE computations can be done in the query:
SELECT product_id, price*1.05 as taxprice
FROM products
WHERE category_id=6
 Complex operations should be avoided
 Correlated Subqueries
 GROUP BY, HAVING, UNION (temp sort area)
 Many table complex joins
 Aggregate functions, whenever possible
 Stored Procedures, when applicable
14
NULL Handling
 Evaluation of NULLs can often be misleading (IS NULL
vs. = NULL, GTE evaluations)
 NULLs can’t be indexed
 Aggregate queries like MAX and MIN may return NULL
 Try to design your data model so that NULLs aren’t
allowed
 Use other identifiers for NULL
 Start with no columns nullable, then make a case for
each to allow NULLs
15
NULL with NVL
Use in a general query:
SELECT product_id, NVL(price,-1) as NULLprice
FROM products
WHERE category_id=6
Use in an aggregate function (note placement of NVL):
SELECT NVL(MAX(price),0) as maxprice
FROM products
WHERE category_id=6
16
Lists of Values
 Improved performance over a join, if you can enumerate
the items in code
SELECT DECODE(gender,'M','Male','F','Female','N','Unknown') as
fullgender
FROM users
17
Date Handling
 Do not assume CF will handle date conversion
 Non-literal date conversion depends on server locale
settings
 Oracle in-line date conversion functions are very fast
 Bind variables are supported (use character type)
 Multi-lingual conversion is supported
18
Date Handling Examples
<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>
SELECT TO_CHAR(sale_date, 'Day DD Month YYYY HH24:MI:SS') as
nice_sale_date
FROM sales
WHERE sales_id=3939
Nice_sale_date
-----------------Wednesday 25 October 2000 00:16:13
19
More Date Handling Examples
In French:
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
Nice_sale_date
-----------------Mercredi 25 Octobre 2000 00:16:13
20
BLOCKFACTOR
 Directive for database fetch size
 Only applies to SELECT statements
<cfquery name="qry_products" BLOCKFACTOR="10"
datasource="DSN_NAME">
SELECT product_id, product_name
FROM products
</cfquery>
21
Further Reading




Oracle Technet -- http://technet.oracle.com
Allaire Developer Exchange
Oracle MetaLink (part of Oracle Support)
Many good Oracle books (O’Reilly, Oracle Press)
Questions?
[email protected]
22