>>2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys.
This is a pretty good method. I tested it on a database containing about 450000 records with a seach template. I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records. This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT. And for the next page, it takes another 50sec :-( A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string, again in the 44500 records! With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times. And the CFX_highlight is applied on only 10 records at a time. A big difference. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353790 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
