I worked on the Royal Mail PAF database a while ago, some 23 million postcode records.
I wrote an SQL script to split the data alphabetically across a number of tables. In my scenario I only needed to retrieve records matching a specific postcode, usualy a maximum of 20-30 records at a time (less if an optional house number was supplied for the search). I achieved postcode lookups in less than a second. Naturally, it needed an extra few lines of code in CF to query the right tables. Perform as many operations on your data in SQL as you can before returning just the data you need to Coldfusion. Hope this helps! Jenny Gavin-Wear Fast Track eCommerce http://www.ftol-ecommerce.com/ >>-----Original Message----- >>From: Torrent Girl [mailto:[email protected]] >>Sent: 17 June 2011 18:53 >>To: cf-talk >>Subject: Retrieve data in groups >> >> >> >>Hi All. >> >>Is there a way for me to retrieve records from a db in groups? >> >>I have a query that selects over 200,000 records and it times out >>on the cf output. >> >>Thanks >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:345420 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
