You need to be careful with this scenario because it may not be more efficient, it depends upon your database schema. I tried this on an application we were doing and our situation was that it was possible to bring back 200 - 300k records (not likely, but possible). Our database was created in such a way that even though we tried bringing back the top n records, the query still had to "touch" every record in all the joined tables. The result was minimal to no performance increase. The moral of the story would be, it largely depends on your indexing and schema whether a particular technique will work or not.
A view, as Jon mentioned, would be a very good idea since you wouldn't have all the joins involved. The only thing you need to worry about with a view is the inserting and updating on the underlying tables and what the performance hit will be on back-end processing updating the view, especially if you're relying on CF to do the inserting and updating. Mark -----Original Message----- From: jon hall [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 12:57 PM To: CF-Talk Subject: Re: Paging Principals 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
