Orafusion.com: Performance Tips

Tips on Optimizing Query Response Times:

Note: The Oracle-related material presented here is largely ColdFusion-version independent. However, please note that most of the ColdFusion code has been developed and tested on older versions of ColdFusion (4.0 through 6.0). Consequently, some CFML code snippets may need to be modified to work optimally on more recent versions of the product.

Hint the Oracle Optimizer directly from your ColdFusion templates:
When Oracle parses your query, the optimizer produces an execution plan which describes the data access path that Oracle will use in order to retrieve your data. Most times the optimizer comes up with a good query plan. However, if your query is complex, it is possible that Oracle will come up with a sub-optimal path. In this case you can pass hints to the optimizer, instructing it to follow a particular (better) access path. What's interesting for the CF developer is that these hints can be passed to Oracle directly from CF templates, like so:

<cfquery name="HintedQuery"
   datasource="yourDSN"
   username="yourUsername"
   password="yourPwd">

   select /*+ INDEX(t2 PK_ORGS) USE_MERGE(t1)*/
      t1.project_name,
      t2.org_name
   from
      projects t1,
      organizations t2
   where
      t1.org_id=t2.org_id
   and
      t2.org_id=3

</cfquery>

In the query above, we have passed the hint that the data should be accessed through the index on the primary key of ORGANIZATIONS, and the result set obtained should be merge joined with the PROJECTS table. The syntax is very important: the hint, which appears right after the SELECT keyword, must be enclosed in an Oracle comment, with a + sign right after the comment start. Space constraints prevent me from going into hint syntax and explanation details here. Please see the Oracle docs, or the excellent book by Guy Harrison: Oracle SQL Performance Tuning (2nd Edition), Prentice Hall (2001), for further information.

Use the Oracle job queue to improve apparent response time:

In some situations you may be faced with the need to do some database processing that takes a few minutes. Your users are going to be annoyed if you make them wait that long for a response. One way to improve the apparent response time is to use the Oracle job queue. The strategy, in a nutshell, is as follows

1) Create a procedure to do whatever it is you need.
2) Create an envelope procedure that submits the procedure in (1) to the Oracle job queue
3) Invoke the envelope procedure, which should take just a few seconds to run, from your ColdFusion template.
4) Inform the user offline (using CFMAIL) user once the queued procedure completes.

Here's a simple example that you can use as a template:

Envelope Procedure

create or replace procedure submit_to_queue(p_param number, p_job_number out number) as

begin

  dbms_job.submit(p_job_number,'main_proc('||p_param||');''');

  commit;

end submit_to_queue;

Main Procedure

create or replace procedure main_proc(p_param number) as

begin

  for i in 1..p_param loop

    null;

  end loop;

  insert into test_table
    (username, remarks)
  values
    (user,'loop executed '||p_param||' times');

commit;

end main_proc;

You could then run a CFSCHEDULE job to check the test_table for newly inserted rows, and then send the user an email message informing her that the job is done.

Notes:
1) You will need to create a test table (cols: username varchar2(24), remarks varchar2(64)) in order to run the test.
2) Check the Oracle docs for more information on the dbms_job built in package.
2) The user must be granted execute privileges on the envelope procedure.
3) Although the procedure runs in the owner's schema, the logged username (user) is the that of the user invoking the procedure. This is nice because we don't have to explicitly pass the user id to the procedure.

Use cfqueryparam for variable parameters in queries (ColdFusion 4.5+):

ColdFusion 4.5 introduces the cfqueryparam tag, which allows you to specify parameter datatypes in your queries. When you do this ColdFusion sends the parameter to Oracle as a bind, or substitution, variable. Oracle then parses the query and caches the resulting execution plan, noting that the bind variable is a parameter. This yields a performance gain for subsequent executions of the query (even for different values of the parameter), because Oracle does not need to reparse the statement.

Here's a simple example using a numeric datatype:

<cfquery name="yourQuery"
   datasource="yourDSN"
   username="yourUsername"
   password="yourPwd">

   select
      first_name,
      last_name
   from
      employees
   where
      employee_id=<cfqueryparam value="#form.emp_id#" cfsqltype="cf_sql_numeric">

</cfquery>

Cache Queries:

Cache relatively static queries on the CF server. This is done using the cachedwithin attribute of cfquery. Here's a sample:

<cfquery name="yourQuery"
   datasource="yourDSN"
   username="yourUsername"
   password="yourPwd"
   cachedwithin="#CreateTimeSpan(0,0,10,0)#">

   select
      name, code
   from
      products

</cfquery>

This caches yourQuery for 10 minutes. Re-execution of this query within 10 minutes of the first execution will retrieve the query cached in the CF server, thereby saving a trip to the DB server.

Notes:
a. CreateTimeSpan is a CF function that creates a time span according to: CreateTimeSpan(days, hours, minutes, seconds).
b. To flush a cached query re-execute the exact same query with the time span set to zero. i.e. cachedwithin=#CreateTimeSpan(0,0,0,0)#.

Shift heavy duty computations from CF to the Oracle Server:

With the increased efficiency and functionality of CF, there's a great temptation to do computationally intensive tasks (such as parsing strings) on the CF server. However, it is usually much better to do this on the Oracle server as it is far more efficient to do so.Use Oracle's rich set of character functions to help you do this. I have improved execution times by up to a factor of 4 by shifting much of the computational workload on to the database.

Use the "blockfactor" attribute of cfquery:

By default cfquery retrieves data in single record blocks. If you use a native driver you can override this using the "blockfactor" attribute. This specifies how many records should be returned in each block. An example:

<cfquery name="yourQuery"
   datasource="yourDSN"
   username="yourUsername"
   password="yourPwd"
   blockfactor=100>

   select
      this, that, other
   from
      trivia

</cfquery>

Note: The maximum value for blockfactor is 100.

Use native Oracle drivers instead of ODBC:

Use the native Oracle drivers supplied with CF enterprise edition for improved performance.

Back to the top