Paging and caching large record sets Support
How to optimise this process both for the person doing the viewing and for the server processing the page.
Today we look at viewing large record sets, and how to optimise this process both for the person doing the viewing and for the server processing the page. To handle the former, we'll use a technique known as paging, and for the latter, caching. This caching is not the client side browser caching we've all come to know and hate; this caching is done entirely on the server.
The code supplied is half the length of the Dreamweaver-generated code, and is hugely beneficial if you have a site where users look through large lists of data without amending it.
Advertisement DMXzone Paginator ASP
Add the coolest page navigation to your site and make browsing through large lists of items or tables easy and quick. Choose from 22 different styles, used by many sites such as Digg, Yahoo, Flickr and much more, to fit perfectly with your design and display the total number of items.
All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.
2. Create a Recordset
Now that we have a connection to the database, let's retrieve some data through it. We do this by creating a recordset, which is a collection of rows or records, each containing a number of columns, or fields. These row/column combinations each contain one distinct piece of data.
- Using the Insert bar, switch to the Application sub-set. Click the first icon in this group, Recordset.
- A Recordset dialog appears.
- Name the recordset 'Orders'.
- Select your PagingSample connection from the list.
- Select the Orders table from the Table list.
- For Columns, select 'Selected', and then holding down your Control key, click all but the Order ID columns.
- Set Sort to sort by Order Date in ascending order.
- Click Test to see whether the SQL query returns data.
- Click OK to close the Test SQL Statement dialog, and click OK again to save your new recordset.
Excellent! Now we're ready to display some data.
3. Use a Dynamic Table to display the Recordset
Let's use two SB's to display the data in our new Orders recordset, and add paging capability:
- Go to Design view by clicking Design on the Document toolbar.
- On the Application Insert bar, click the down arrow on the third icon from the left. Select the first option, Dynamic Table from the list.
- Name your new dynamic table 'OrdersView'.
- For 'Show', set it to display 2 records at a time. Why 2? Well, if it can work for 2 records, it can work for 10 or 20 or 50!
- Click OK to save the dynamic table.
- Again on the Application Insert bar, click the down arrow on the sixth icon from the left. Select the first option, Recordset Paging from the list.
- Your Orders recordset should already be selected in the Recordset list. Set 'Display using' to Text.
- Click OK to save the recordset paging controls.
Right! We now have a paged recordset. The user won't have to wait minutes for the entire recordset load into the browser. This makes browsing this data a more pleasant experience. And what's more, we got to this point in less than 10 minutes! That's powerful.
I live in Cape Town, South Africa. I've been pushing 1's and 0's ever since my first computer at the age of 11. Ever since then I've always known I'll be a geek. I now work for Wireframe Studio in sunny Cape Town, and have been for the last 3 years. I'm the database / ASP / dynamic Flash guy here. I'm also one of the XHTML / CSS guys. I have alot of fun in my work because I develop solutions in a variety of ways. I always have something to do, and one project is always different to the next. I actually do have a girlfriend amongst all that!