1 / 19

Improving Database Performance

Improving Database Performance. Derrick Rapley adrapley@rapleyzone.com www.cfbookmark.com. Agenda. Query Life Cycle Caching Variable-based Query Caching <CFQueryParam> Stored Procedures Block Factor Caching Variable-based Query Caching Using SQL JOINS Microsoft Access.

nenet
Download Presentation

Improving Database Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related