Hi, This wouldn't sound not too different than a stored procedure then. It is written in a pretty solid stored procedure at present.
Geoff B > -----Original Message----- > From: Ben Doom [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 10, 2003 2:25 PM > To: CF-Talk > Subject: RE: Paging Principals > > > I can't speak for Oracle, but in MS SQL you get some limited performance > gains, because the DB does the statement optimization once, then > stores it. > > Not a huge gain, to be sure, but some. > > > -- Ben Doom > Programmer & General Lackey > Moonbow Software, Inc > > : -----Original Message----- > : From: Andre Turrettini [mailto:[EMAIL PROTECTED] > : Sent: Wednesday, September 10, 2003 2:14 PM > : To: CF-Talk > : Subject: RE: Paging Principals > : > : > : My understanding of a view is that it is recreated when you run > : your query. > : At least in Oracle it does the joins under the hood at the time > : you request > : your data. Thus, there should be no performance gains there. > : > : DRE > : > : -----Original Message----- > : From: Mark Stewart [mailto:[EMAIL PROTECTED] > : Sent: Wednesday, September 10, 2003 11:53 AM > : To: CF-Talk > : Subject: RE: Paging Principals > : > : > : 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
