Andrew, Don't use RecordCount on a query that returns the actual rows. Instead, perform a query that just returns the Count(*) of the rows that would satisfy your WHERE clause. It's much more performant and requires a lot less of your server resources. Respectfully,
Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# & ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com ----- Original Message ----- From: Andrew Grosset To: CF-Talk Sent: Tuesday, March 28, 2006 8:53 PM Subject: Re: DB Pagination Question Dan, thanks for posting. Presumably you need to do another query initially to determine how many pages potentially to display dependent on the recordcount? (ie if there were 1000 records (recordcount?) and you elected to display 10 records/page that would be 100 pages). Andrew. >You can do it with w/pure SQL server, but it can be pretty expensive >depending on the query. > >The idea is to do: > >select top 10 > * >from > ( > select top 40 > * > from > table > order by > tableId asc > ) >order by > tableId desc > >You have to match up the where clauses exactly. The idea is the virtual >table grabs the current page number and add one then multiply by the number >of records to display (records*(page+1) = 10*(3+1).) This would pull out the >top 40 records. You then order by descending grabbing out just the number of >items on the page. This will grab out records 31-40. > >Hope this helps! > >-Dan > >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236380 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
