Speeding up a query

If you have a slow query that executes slowly, there are a few things you can do to try to speed it up.

• Create indexes on the fields specified in the WHERE clause.
• If the PK or index consists of multiple fields, refrence the fields in the same order in the WHERE clause.
• If a condition will eliminate a large amount of rows being returned, place it first in the where clause.
  From Ian Rutherford: Oracle reads the WHERE conditions from bottom to top so put your largest eliminator at the bottom.
• Group the conditions together by table in the where clause.
• If you are expecting to return a lot of data, add the blockfactor parameter to the CFQUERY statement (ie: blockfactor="1000"). By default, the datasource will return each record returned by the query one record at a time. If you are expecting a large amount of data, the blockfactor will instruct the datasource to return x number of records at once and not one by one.
  

Return to the Cold Fusion Tips-N-Tricks topic list