|
|
| Databases 101 | | | ASP.NET Chapter | | | Suggest Article | | | Discussions | | | Bookstore | | | Write for Us | | | OpenAuction | | | Our Authors | |
|
|
|
 Register Domains
Know your Web users
International Domains
Database Web Tools
Web System Integrator
Training Solutions
IT Solutions
Search Engine Experts
Buy Linux Products
Accept Credit Cards
|
 |
|
| Using Persisted Recordsets to Page Through Large Query Results | | by Pete Nelson | | | Skill level: Beginner | | | First posted: Thursday, June 01, 2000 | |  | | | | Overview
This article originally appeared as a suggestion called "Managing multiple requests to access a RecordSet without using Session object." I figured a persisted recordset would cover this topic, and that's what this article will cover - using a persisted recordset to page through large amounts of data.
What is a Persisted Recordset?
A persisted recordset is basically a recordset that has been disconnected from its data source and has been saved to disk as a file or to memory as stream (we'll be covering save to disk method). You can then later open the persisted recordset by passing the file name to the Open method. This works quite well for mobile computers such as laptops, or for storing static data such as lookup tables.
In our case, we're using it for temporay storage of a large amount of data. Recordset paging is a common method for presenting a small portion of a large amount of data at one time. You might write code to display twenty records at a time and add a Prev/Next navigation routine. One of the drawbacks of this method is that you need to re-query the database when the user moves to a different page of records. With a large amount of data (or a complex query), this can put uneccessary load on both the web and database server, not to mention any additional network traffic.
One way around this is to store the recordset in a Session variable. This is generally considered a no-no however. Imagine 100 users each storing 1MB of data in a 'sessioned' recordset. That's 100MB of memory overhead!
A good alternative is a persisted recordset. By using the Save method and supplying a file name, we can store the recordset to a physical file. Then to reload the recordset when the user moves to a different page, we just pass the file name to the Open method and all the data is available again without any trips to the database server. Note: Because we are reading and writing files to the file system, IIS needs to have the proper permission in whatever directory you decide to use.
Sample Code
In the attached sample code, we query the Titles table in the pubs database in SQL 7. We persit the recordset to disk, and as we navigate through the records, we load the recordset from disk rather than re-querying the server.
This system does have its drawbacks as well since you need some sort of routine to clean out old recordset files. Perhaps using Session ID as the file name and deleting the files in the Session_OnEnd event? Might work - it's up to you to weigh the benefits and drawbacks to this method.
|
| | | | |
This document can be found in these Encyclopedia chapters:
º ActiveX Data Objects (ADO)
|
|
|
|
Got something to add to this article?
Create a new discussion |
|
|
|
View Article Statistics
Authors...
Edit this article
View Preview Version
|
|
| Printable Copy of Article |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
|
|
