Improving Database Performance

1
Improving Database Performance

• Derrick Rapley
• adrapley_at_rapleyzone.com
• www.cfbookmark.com

2
Agenda

• Query Life Cycle
• Caching
• Variable-based
• Query Caching
• ltCFQueryParamgt
• 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 ltcfquerygt
• 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

• ltCFQUERY nameApplication.doctypes
  datasourcedatasourcegt
• SELECT type_id, doc_type
• FROM doctype
• lt/CFQUERYgt
• ltselect namedoctypegt
• ltcfoutput queryApplication.doctypesgt
• ltoption valuetype_idgtdoc_type
• lt/cfoutputgt
• lt/selectgt

6
Query Caching

• Two attributes can be used in ltCFQUERYgt
• 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 wont cache and operate as
  normal.
• Perform a Q of Q on a Cached Query for Next N
  records and search results

7
ltCFQUERYPARAMgt

• 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
• Its a waste of time and space to cache dynamic
  queries, perfert for cfqueryparam.
• SELECT id, FirstName, LastName
• FROM Employees
• WHERE ID ltcfqueryparam value150"
  cfsqltype"CF_SQL_NUMERIC"gt
• ltCFQUERYPARAMgt helps differentiate what is
  dynamic in the SQL statement

8
ltCFQUERYPARAMgt

• Must be used within ltCFQUERYgtlt/CFQUERYgt
• Supports Bind Parameters
• Improves Performance
• Provides some benefits of Stored Procedures
  without using them
• Can be used with Stored Procedures when using
  ltCFQUERYgt to call them
• Can not be used with Cached Queries
• ltCFQUERYPARAM VALUE150 CFSQLTYPECF_SQL_INTEGE
  Rgt

9
ltCFQUERYPARAMgt

• ltCFQUERY namegetEmployee datasourcedatasourc
  egt
• SELECT id, FirstName, LastName
• FROM Employees
• WHERE ID ltCFQUERYPARAM VALUE150
  CFSQLTYPECF_SQL_INTEGERgt
• lt/CFQUERYgt

10
ltCFQUERYPARAMgt

• 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

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 ltCFQUERYgt and ltCFSTOREDPROCgt

12
ltCFQUERYgt OR ltCFSTOREDPROCgt

• ltCFQUERYgt
• 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
• ltCFSTOREDPROCgt 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