C# Datasets, paging and large amounts of data.

Question

I want to show a large amount of data in a dataset, 100,000 records approx 10 columns, this consumes a large amount of ram 700MB. I have also tried using paging which reduces this by about 15-20% but I don't really like the Previous/Next buttons involved when using paging. I'm not writing the data to disk at present, should I be? If so what is the most common method? The data isn't to be stored forever just whilst it is being viewed, then a new query may be run and a nother 70,000 records could be viewed. What is the best way to proceed?

Thanks for the advice.

Answer 1

I would suggest memory mapped files...not sure if .NET includes support for it yet.

Answer 2

The reality is that the end-user rarely needs to see the totality of their dataset, so I would use which method you like for presenting the data (listview) and build a custom pager so that the dataset is only fed with the results of the number of records desired. Otherwise, each page load would result in re-calling the dataset.

The XML method to a temp file or utilizing a temp table created through a stored proc are alternatives but you still must sift and present the data.

Answer 3

That is a lot of data to be working with and keeping aroudn in memory.

Is this a ASP.NET app? Or a Windows app?

I personally have found that going with a custom pager setup (to control, next previous links) and paging at the database level to be the only possible way to get the best performance, only get the data needed....

Answer 4

implement paging in SQL if you want to reduce the memory footprint

Answer 5

An important question is where this data comes from. That will help determine what options are available to you. Writing to disk would work, but it probably isn't the best choice, for three reasons:

1. As a user, I'd be pretty annoyed if your app suddenly chewed up 700Mb of disk space with no warning at all. But, then, I'd notice such things. I suppose a lot of users wouldn't. Still: it's a lot of space.
2. Depending on the source of the data, even the initial transfer could take longer than your really want to allow.
3. Again, as a user, there's NO WAY I'm manually digging through 700Mb worth of data. That means you almost certainly never need to show it. You want to only load the requested page, one (or a couple) pages at a time.