Optimizing Your Cold Fusion Applications for Oracle Justin

Optimizing Your Cold. Fusion 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 § § § § 4 Oracle 8 i 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)

Configuring Your Platform -- Cold. Fusion § § § 5 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

Query Tuning § § 6 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

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 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 § § § 9 Introduced in CF 4. 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

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 § § § 12 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

Special Coding Techniques § § 13 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(field 1, field 2, …) VALUES (value 1, value 2, …) Explicit field listing helps with different database field ordering (production vs. development)

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 § § 14 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

NULL Handling § § 15 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

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', 'Female', 'N', 'Unknown') as fullgender FROM users 17

Date Handling § § § 18 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

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('#Date. Format(l_In_Date, "MMDDYYYY")#', 'MMDDYYYY') </cfquery> SELECT TO_CHAR(sale_date, 'Day DD Month YYYY HH 24: 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 HH 24: 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 Meta. Link (part of Oracle Support) Many good Oracle books (O’Reilly, Oracle Press) Questions? Justin@team. bantu. com 22