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
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
