DataTable in Session or Stored procedure paging in ASP.NET?

Which is preferred:

1. keeping 1000 records in a datatable in session and do paging using that?

Or

1. doing paging in stored procedure level and requesting 35 records each time?

more info:

• datatable of 40 columns

• number of users is 200 users

• 7 application are running on IIS

• Oracle DB server is not the same server for IIS

any other solution/suggestion/recommendation are welcomed

Edit: the user may update a row, and submit it to database. which will refresh the grid by requesting the whole data from database.

Edit2: Every user is loading different datatable which coming from same table in database

I would tend to go with the stored proc route, with the caveat of giving the user the ability to filter the results to what they are looking for. Then perhaps caching the results along with it's query parameters and looking against the cache before executing the stored proc to make sure you don't already have results for that specific search criteria.

Putting that amount of data into session isn't a good idea, because that information won't be shared across multiple users (since it's in session), plus it would hurt performance in the long run. And if you broaden your scope to the application level, it would be better to use a caching mechanism to alleviate the memory footprint in your application.

Just my 2c,

Hope this helps.

Edit: If I can remember correctly, DataTables aren't Serializable, so you wouldn't be able to store them in session anyhow, you'd have to use a dataview or dataset.

If you have a boatload of memory on the server, keeping the data in memory will be much faster than going to the database each time.

Rough calculation: 1000 * 40 * 200 * bytes-per-column + overhead

If the DataTable is shared between users, you could put it in the Application object.