Geoff we do something like this with a large record-set. We run the full query at the outset and put it into a Session based Structure then page through that.
Kind Regards - Mike Brunt Webapper Services LLC Web Site http://www.webapper.com Blog http://www.webapper.net Webapper <Web Application Specialists> -----Original Message----- From: Geoffrey V. Brown [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 6:47 AM To: CF-Talk Subject: RE: Paging Principals Hi, Good idea. A cached query wouldn't work for us, unfortunately, as the data changes almost by the minute. Geoff B > -----Original Message----- > From: Ben Doom [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 10, 2003 9:43 AM > To: CF-Talk > Subject: RE: Paging Principals > > > When I had something like this, I used the full query but only > retrieved the > top n, where n is the highest index to display. So, if you are displaying > 10 per page... > > page 1, n=10 > page 2, n=20 > > and so on. Maybe not a perfect solution, but dropped average processing > time a lot, since people would generally either narrow the search or only > look at the first page or two. > > If you think people might be flipping through a large number of > pages, look > at query caching. > > > -- Ben Doom > Programmer & General Lackey > Moonbow Software, Inc > > : -----Original Message----- > : From: Geoffrey V. Brown [mailto:[EMAIL PROTECTED] > : Sent: Wednesday, September 10, 2003 9:18 AM > : To: CF-Talk > : Subject: Paging Principals > : > : > : Hi, > : I have an app that queries a table and displays a subset of the query > : results in a paged format, showing only 10 results per page. > Some queries > : will return upwards of 1,000 rows. > : > : This query joins quite a few tables - say 8 or so, however the > : query itself > : only requires one table to obtain the basis of the dataset(the > rest of the > : tables in the join are used for display info or 'cosmetic' type > : information). > : > : Generally speaking, which of the following would be more efficient: > : > : Option 1: > : - Query database, joining 8 tables, get entire dataset (1000 rows) > : - CFOUTPUT / Loop limited to start row (page ordinal) and maxrows (10) > : > : Option 2: > : - Query database only based on the results of one table (1000 rows) > : - Cfoutput / Loop limited to start row (page ordinal) and maxrows (10) > : - within output loop, perform second query joining the 8 tables, > : based on a > : single primary key returned from the initial dataset. (returns > single row) > : > : In essence, the question revolves around whether it is more expensive to > : pull an entire 1,000 record joined dataset in one shot, or to make the > : initial query lean, and then do ten consecutive queries > : performing the join. > : The former likely incurs more database load, while the latter > would incur > : more interaction with odbc. > : > : Whew. Interested in your answers! > : > : Thanks, > : Geoffrey Brown > : ________________________ > : Online Operations Manager > : Deerfield.com > : 989.732.8856 ext. 227 (Gaylord) > : 231.935.4640 ext. 123 (Traverse City) > : [EMAIL PROTECTED] > : > : http://www.deerfield.com > : Personal Service with a :-) > : > : VisNetic (viz-net-ik) 2002: vision of the Internet http://www.visnetic.com : : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
