When I had something like this, I used the full query but only retrieved the
top n, where n is the highest index to display. So, if you are displaying
10 per page...
page 1, n=10
page 2, n=20
and so on. Maybe not a perfect solution, but dropped average processing
time a lot, since people would generally either narrow the search or only
look at the first page or two.
If you think people might be flipping through a large number of pages, look
at query caching.
-- Ben Doom
Programmer & General Lackey
Moonbow Software, Inc
: -----Original Message-----
: From: Geoffrey V. Brown [mailto:[EMAIL PROTECTED]
: Sent: Wednesday, September 10, 2003 9:18 AM
: To: CF-Talk
: 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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
