What I do, and it seems to scale pretty well, is to run 2 queries. The first query simply pulls the ID's of the matching rows, and the second query pulls all of the data only for the ID's that were queried in the first query. Example:
You want to get results 51-100 of your query: <cfset start = 51> <cfset end = 100> <cfquery name="ids"> SELECT ID FROM myTable WHERE a < 5 and b LIKE '%test%' </cfquery> <cfset toGet = ArrayNew(1)> <cfloop from="#start#" to="#end#" index="a"> <cfset ArrayAppend(toGet, ListGetAt(ValueList(ids.ID), a))> </cfloop> <cfquery name="all"> SELECT * FROM myTable WHERE ID IN (#ArrayToList(toGet)#) </cfquery> Yes, you do 2 trips to the DB, but the first query is quick and will match all of your search parameters. Then you just get the ID's that you are interested in (where in the results the user is). The second query is more time consuming, but gets only the records you want. This seems to scale well into the hundreds of thousands of rows, as long your first query doesn't return too many results. You can obviously make the CF code more efficient, but I just threw this together. Cedric >Like pretty much everyone else, I have always used the following method for >paging through result sets, > ><cfoutput query="whatever" startfrom="#startfrom#" maxrows="#maxrows#"> > >And altering those variables to allow 200 records per page or whatever, > >But this means querying ALL records form the database each time, which can >be very excessive when your dealing with a very large database. >Has anyone come up with a good solution of only querying the records you >actually need (rows 40 -59 for example). > > >Russ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216943 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
