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.