> Well I think the count() example is sort of outside of the scope of
> what was being described... The specific question (as I understood
> it) was whether a pair of queries could be faster for presenting a
> sub-set of records in a list page -- the count() query isn't part
> of the task of fetching and presenting those records, so it's sort
> of moot. And in that scenario, if you use the 2-query approach, you
> can use query1.recordcount to get the total number of records (and
> divide that to get the number of pages), so that eliminates the
> need to perform a separate count() query if you want to display
> total # of available records on the page.
You can still do it in one query, returning both the total record count and
just the "window" of records you are interested in (example for oracle):
<cfquery blah blah>
SELECT COUNT(*) AS TotalRecords,
*
FROM (SELECT col1,
col2,
col3,
ROWNUM AS myRownum
FROM -- tables
WHERE -- where clause
ORDER BY -- orderby clause)
</cfquery>
you then have a single "small window" query with an extra column (TotalRecords)
that tells you how many rows are in the "big" query (which you left on the db
server.
This would be a good approach if the problem with the dataset is the cost of
bringing it over, not the cost of calculating it.
/t
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217043
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
