1 optimizing your coldfusion applications for oracle justin fidler, cna, cps, ccfd chief technology...
TRANSCRIPT
1
Optimizing Your ColdFusion Applications for Oracle
Justin Fidler, CNA, CPS, CCFD
Chief Technology Officer
Bantu, Inc.
8 May 2001
2
Overview
Why Oracle? Configuring Your Platform Query Tuning CFQUERYPARAM Special Coding Techniques NULL Handling Lists of Values Date Handling BLOCKFACTOR Further Reading, Questions
3
Why Oracle?
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!
4
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)
5
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
6
Query Tuning
Most poor database performance results from poorly-designed queries
EXPLAIN PLAN will solve most of your problems Use TKPROF in severe cases Have your DBA closely check and monitor Oracle
performance statistics
7
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
8
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
9
CFQUERYPARAM
Introduced in CF4.5 Enormous performance improvement Works with any database that supports “bind” variables Oracle SQL statement cache is literal and case-
sensitive Statement cache determines execution plans
10
CFQUERYPARAM Before Example
Your Code before CFQUERYPARAM:<CFQUERY DATASOURCE="DSN_NAME">
SELECT usernameFROM usersWHERE user_id=#SESSION.USER_ID#
</CFQUERY>
In the Database before CFQUERYPARAM:SELECT username FROM users WHERE user_id=2236
11
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
12
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
13
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)
14
More Coding Techniques
SIMPLE computations can be done in the query:
SELECT product_id, price*1.05 as taxpriceFROM productsWHERE 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
15
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
16
NULL with NVL
Use in a general query:SELECT product_id, NVL(price,-1) as NULLpriceFROM productsWHERE category_id=6
Use in an aggregate function (note placement of NVL):SELECT NVL(MAX(price),0) as maxpriceFROM productsWHERE category_id=6
17
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
18
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
19
Date Handling Examples
<CFSET l_In_date="#Now()#"><CFQUERY name="qry_calendar" datasource="DSN_NAME">SELECT event_idFROM calendarWHERE 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 salesWHERE sales_id=3939
Nice_sale_date------------------Wednesday 25 October 2000 00:16:13
20
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 salesWHERE sales_id=3939
Nice_sale_date------------------Mercredi 25 Octobre 2000 00:16:13
21
BLOCKFACTOR
Directive for database fetch size Only applies to SELECT statements
<cfquery name="qry_products" BLOCKFACTOR="10" datasource="DSN_NAME">SELECT product_id, product_nameFROM products
</cfquery>
22
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?