Optimizing Your ColdFusion Applications for Oracle

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 username FROM users WHERE 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 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

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

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_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

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 sales WHERE 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_name FROM 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? Justin@team.bantu.com