This would actually be more work than what I am doing now, which is 1 query and only output the rows I want. Your method would produce the same results, but with 1 more unneccisary trip to the database. If you already have all the data you need in the first query, there realy is no need to create the second query, when you can simply output the rows you want using my previous example.
<cfoutput query="whatever" startfrom="#startfrom#" maxrows="#maxrows#"> -- Snake -----Original Message----- From: Cedric Villat [mailto:[EMAIL PROTECTED] Sent: 31 August 2005 03:13 To: CF-Talk Subject: Re: Paging through records 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216952 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
