Do you plan to cache the query? If you run the big query once, and then cache it, I'd reckon that option 1 is going to be your faster option.
----- Original Message ----- From: "Geoffrey V. Brown" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 10, 2003 8:17 AM 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
