What would be the best way to fetch around a million record from DB?

Question

I need to fetch and show data on a webpage whose number of records may vary based on filters from around 500 records to 1 million records.

Will caching be of any use here as I think million record in memory is not a good thought. SqldataReader?

Paging is a must to implement of course. Showing 1 million records is a worst case scenario(the stupid All filter in use-cases!).

Should I use connected architecture(SqlDataReader) or disconnected architecture(DataSets)?

Answer 1

First of all, think about it like this: displaying 1 million records makes absolutely no sense to any user. So, you have to think about what the user expects to see. Maybe a summary?! Maybe paginate the records in pages of say 25, or 50 or 100 records. Either of these approaches will not require you to hold 1 M records at a time in memory.

Also, when you run a query against a SQL database and use a SqlDataReader you will not be receiving all the records but instead the SQL driver will send the query to the SQL server, the server will execute the query, prepare a result set and create a forward-only cursor on the server. Then the driver will fetch a record at a time, every time you call Read() on your SqlDataReader. The behavior is very similar if you use LINQ to SQL which uses deferred execution. The result set is not transferred over in full until (or unless) you specifically request each and every row.

So, a simple pagination query will do the trick. Or in other cases some sort of summary report that aggregates the data from those 1 million records one or two pages of relevant data.

Of course if you do need to move back and forth through the pages, some sort of caching might make sense but again, think about it: how often will a user actually want to browse through 1 million records - probably never.

As a last note, if you do implement pagination - make sure that the method you use to implement the pagination relies on the SQL server sending data one page at a time and not reading all 1 million records into ASP.NET and then paginating the local copy of the data because that would be very inefficient and slow. Here is an example of a SQL Server query that performs pagination: SO Question #109232