1. Improving Database Performance Derrick Rapley adrapley@rapleyzone.com www.cfbookmark.com

2. Agenda • Query Life Cycle • Caching • Variable-based • Query Caching • <CFQueryParam> • Stored Procedures • Block Factor • Caching • Variable-based • Query Caching • Using SQL JOINS • Microsoft Access

3. Query Life Cycle • Request sent to CF to process a template • ColdFusion executes <cfquery> • CF sends SQL statement to the DB • CF waits for a response from the DB • DB parses the SQL statement • DB executes the SQL statement • DB returns record set to CF 1 record at a time

4. Caching • Queries that hardly change are perfect for caching. • I.E. List of Document Types • Queries can be stored in Shared Scope variables: Session, Application, Server • Queries can be cached based on Results

5. Variable-based Caching <CFQUERY name=“Application.doctypes” datasource=“#datasource#”> SELECT type_id, doc_type FROM doctype </CFQUERY> <select name=“doctype> <cfoutput query=“Application.doctypes”> <option value=“#type_id#”>#doc_type# </cfoutput> </select>

6. Query Caching • Two attributes can be used in <CFQUERY> • CACHEDWITHIN-caches data for a specified time span • CACHEDAFTER-caches data until a specific date is reached. • Queries are only cached if there is enough space. If not, the query won’t cache and operate as normal. • Perform a Q of Q on a Cached Query for ‘Next N’ records and search results

7. <CFQUERYPARAM> • Parsing, validating, and analyzing the query can take longer than actually processing it • Simple queries are easy to cache. SELECT id, FirstName, LastName FROM Employees • It’s a waste of time and space to cache dynamic queries, perfert for cfqueryparam. SELECT id, FirstName, LastName FROM Employees WHERE ID = <cfqueryparam value=“150" cfsqltype="CF_SQL_NUMERIC"> • <CFQUERYPARAM> helps differentiate what is dynamic in the SQL statement

8. <CFQUERYPARAM> • Must be used within <CFQUERY></CFQUERY> • Supports Bind Parameters • Improves Performance • Provides some benefits of Stored Procedures without using them • Can be used with Stored Procedures when using <CFQUERY> to call them • Can not be used with Cached Queries • <CFQUERYPARAM VALUE=“150” CFSQLTYPE=“CF_SQL_INTEGER”>

9. <CFQUERYPARAM> <CFQUERY name=“getEmployee” datasource=“#datasource#”> SELECT id, FirstName, LastName FROM Employees WHERE ID = <CFQUERYPARAM VALUE=“150” CFSQLTYPE=“CF_SQL_INTEGER”> </CFQUERY>

10. CF_SQL_BIGINT CF_SQL_BIT CF_SQL_CHAR CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARCHAR <CFQUERYPARAM>

11. Stored Procedures • Execute faster than SQL from the client • Precompiled SQL Statements • Stored in the database • Returns the a complete record set as a result • Can execute a block of Statements • Increased Security • Access to tables unavailable to the user • Can use <CFQUERY> and <CFSTOREDPROC>

12. <CFQUERY> OR <CFSTOREDPROC> • <CFQUERY> • Can only be used when native driver is available • It only passes ODBC compliant code to an ODBC driver • Stored Procedures can vary depending on which database is being used • <CFSTOREDPROC> can always be used

13. Block Factor • Rows are retrieved from the database one row at a time • Block Factor is the number of rows retrieved at one time (defaults to 1) • Block Factor can hurt performance if too high of a Block Factor is declared

14. SQL JOINS • A Relational DB is key to using JOINS • Advantages of Relational DB • Data does not have to be repeated • Easier to maintain • Uses Less Storage Space • Performs quicker than flat DB

15. Using Joins • Specify tables in FROM clause SELECT documents.title, doctype.doc_type FROM documents, doctype • How does the DB know which rows to join? • Inner Joins • Right Outer Joins • Left Outer Joins • Full Outer Joins

16. Using Joins • You must specify the join condition • Can be specified in the WHERE clause SELECT doc.title, dt.doc_type FROM documents doc, doctype dt WHERE doc.doc_type_id = dt.type_id • Can be spcified in the FROM clause(ANSISQL Standard) SELECT doc.title, dt.doc_type FROM documents doc JOIN doctype dt ON (doc.doc_type_id = dt.type_id) • JOIN sytax is similar in most databases but can vary

17. Inner Join • If no Join is specified, then INNER JOIN is assumed SELECT doc.title, dt.doc_type FROM documents doc INNER JOIN doctype dt ON (doc.doc_type_id = dt.type_id) • INNER JOIN only matches rows in both tables

18. Outer Join • LEFT OUTER JOIN • Returns all rows from left table • RIGHT OUTER JOIN • Returns all rows from right table • FULL OUTER JOIN • Returns all rows from both tables

19. Microsoft Access • Obtain the latest ODBC drivers • Obtain the latest MDAC • Only allow 5-7 simultaneous (per Processor) requests to Access • Uncheck ‘Maintain Database Connection’ in CF Administrator • Max Buffer should be set to 0