To use Cache or not

I have a page that I need to show individual record values from a SQL Server database (not a datagrid) and buttons to navigate through the records.

This happens to be a very large DataSet.

I have two options:

1) Cache the Dataset and get a single DataRow for every navigation

2) Use a stored procedure to return one row at a time with a select statement. An example would be navigating through the records in order such as "SELECT TOP 1 * FROM Table WHERE Key_Value > 'Current_Value' ORDER BY Key_Value ASC"

What are the tradeoffs of both and are there any better ways to do this?

Caching sounds like a viable solution but how often does the data change? If it does change frequently, you may be able to get away with refreshing the cached data say every 30 minutes or so. Then again, if performance is not a huge requirement of the application, you could make a database hit for each navigation. Alot depends on the app requirements and you gotta weight out all your options. Me personally, I would cache the data and either invalidate the cache whenever the data is changed through the application UI or invalidate every 30 minutes.

I didn't think about a complication with the stored procedure idea. If the select statment had complicated joins and where conditions, then I think it would be a waste to query that way every time just for a single record.

I think a non expiring cache will be the best way because this will be a read only application and the data won't be updated very frequently. I think that will give a faster response as well.

Does cache store in client or web server memory and can you specify which you want?

Caching is stored on the webserver, but Microsoft have released a Caching application block where you can specify other persistence mediums. Since this is a read-only app and data changes are infrequent, caching is an optimal solution.

I'm still a little confused about cache. I understand once a DatSet is cached there won't be any more trips to the database until the cache expires.

But there would still be a lot of network traffic sending the cached DataSet to each of the clients on every record (datarow) move. It seems like a waste to keep sending the entire DataSet just for a single row.

The client wanted single record navigation but it looks like the DataGrid would be a better solution.

The dataset doesn't get sent to the client, only the html (and viewstate) that is spit out, the one record. The dataset is kept in memory on the server.

Right! That makes more sense now.

So the server would store a shared DataSet in cache and send only the HTML code to clients.

Without cache, every time a client requested a row a new DataSet would be created on the server. So there would be as many DataSets as clients stored in server memory, right?

I don't understand your project requirements (is this homework?)

Last I looked networks moved data along at around 100mbps

Obviously, congestion, noise and other externals fact that speed.

But even if you had 10,000 records... you could display that in a repeater , datalist, what have you.... and the page itself would get cached in server memory. So there would be no need for the server to recreate the html from the dataset every time someone looks at the page, and no need to go to the database everytime someone requested a page.

Now, you would probably want to limit it to 10 records on screen at a time, and use paging....

This is for a client that requires single record navigation (similar to a bound form in MS Access) and yes I know it's old-school with things like DataGrids and repeaters.

But either with or without a repeater, caching would be the way to go.