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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
