By Scott Mitchell
|A More Up-To-Date Paging Solution|
|For a more up-to-date look at efficiently paging through large resultsets, see Greg Hamilton's article A More Efficient Method for Paging Through Large Result Sets.|
One of ASP's great assests is that it can connect to a database on the server and report the results of a query to a user. If the queries are small, the page loads quickly, and there isn't too much data to wade through. However, if there are hundreds of thousdands of results being returned by the database, attempting to display all that data on one page would not only be difficult for the reader to utilize, but would also be very slow to execute!
There is a solution, however. Using ADO you can page through data N records at a time. The records are retrieved N at a time, so the query time will be shortened considerably, plus the data will be easier to examine and use. We will be using ClientSide cursors (meaning that the recordset cursor will be stored on the client's side, as opposed on the server (which is the default), the CacheSize property (to specify how many records to cache on the cursor), and the paging properties: AbsolutePage, PageSize, and PageCount.
Let's start by looking at some code. Remember to include ADOVBS.inc. (If you don't have it, it can be downloaded here. This program assumes that ADOVBS is located in your web application's root directory and that the file is named "thisfile.asp".)
Let's quickly go over some of those ADO properties we used. Setting PageSize to an integer value tells ADO how many records per page you are wanting. PageCount, then, tells you how many pages total there are. Obviously you need to set PageSize before PageCount will yield a sensible result. (This is not strictly true, since PageSize defaults to 10, but always be sure to set PageSize explicitly if you plan on using paging!) AbsolutePage is a value you can set to tell ADO to get a certain page. So, if you have pages x through y with N records per page, you could say, AbsolutePage = M, and the recordset cursor will start at the M*Nth record (assuming M < y).
The CacheSize tells ADO to cache N number of rows on the cursor. Since we chose to have 10 records per page, I set the cache size to 10. ADO will grab the first 10 records, and cache those. It won't hit the database until you request any past the cached limit. Since we are only displaying 10 records per page, we are only having one quick database hit per page. The caching property is vital to the performance of the script above.
Hopefully you now understand how to page through data using ADO / ASP. Be forewarned, you can only page through certain cursor types. For example, you cannot page through a forward-only cursor (which is the default for server side cursors). Paging allows a quick and visually appealing way for you to display results from a query to your users!