Since most people are not going to browse through all 100 pages of records, pulling a 1000 records for everyone, has some obvious inefficiencies.
I'd go for individual queries on each page, but take the 8 join query out of CF, and make it a view. The view will help lessen the impact of that query, and make your code less complex at the same time. -- jon mailto:[EMAIL PROTECTED] Wednesday, September 10, 2003, 9:17:43 AM, you wrote: GVB> Hi, GVB> I have an app that queries a table and displays a subset of the query GVB> results in a paged format, showing only 10 results per page. Some queries GVB> will return upwards of 1,000 rows. GVB> This query joins quite a few tables - say 8 or so, however the query itself GVB> only requires one table to obtain the basis of the dataset(the rest of the GVB> tables in the join are used for display info or 'cosmetic' type GVB> information). GVB> Generally speaking, which of the following would be more efficient: GVB> Option 1: GVB> - Query database, joining 8 tables, get entire dataset (1000 rows) GVB> - CFOUTPUT / Loop limited to start row (page ordinal) and maxrows (10) GVB> Option 2: GVB> - Query database only based on the results of one table (1000 rows) GVB> - Cfoutput / Loop limited to start row (page ordinal) and maxrows (10) GVB> - within output loop, perform second query joining the 8 tables, based on a GVB> single primary key returned from the initial dataset. (returns single row) GVB> In essence, the question revolves around whether it is more expensive to GVB> pull an entire 1,000 record joined dataset in one shot, or to make the GVB> initial query lean, and then do ten consecutive queries performing the join. GVB> The former likely incurs more database load, while the latter would incur GVB> more interaction with odbc. GVB> Whew. Interested in your answers! GVB> Thanks, GVB> Geoffrey Brown GVB> ________________________ GVB> Online Operations Manager GVB> Deerfield.com GVB> 989.732.8856 ext. 227 (Gaylord) GVB> 231.935.4640 ext. 123 (Traverse City) GVB> [EMAIL PROTECTED] GVB> http://www.deerfield.com GVB> Personal Service with a :-) GVB> VisNetic (viz-net-ik) 2002: vision of the Internet http://www.visnetic.com GVB> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
