我需要获取并在网页上显示的数据，其记录的数量可以根据过滤器从大约500个记录100万条记录而有所不同。

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.

威尔缓存任何用处在这里，因为我想在内存中万人次的纪录是不是一个好主意。 SqlDataReader的？

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

寻呼是一个必须实现的课程。显示100万条记录是最坏的情况下（愚蠢的的使用情况！所有的过滤）。

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

我应该使用连接架构（SqlDataReader的）或断开的架构（数据集）？

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

推荐答案

首先，想一想这样的：显示100万条记录使得完全没有意义的任何用户。所以，你要想想用户希望看到的。也许一个总结？也许在分页说25或50或100条记录的网页的记录。无论这些方法不需要你持有的1M记录在内存中的时间。

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.

此外，当您对一个SQL数据库查询和使用SqlDataReader，你将不会被接受的所有记录，而是在SQL驱动程序将查询发送到SQL服务器，该服务器将执行查询，prepare一个结果集，并创建一个只进游标在服务器上。然后司机将获取一个记录一次，每次调用Read（）在你的SqlDataReader的时间。如果你使用LINQ到SQL，它使用延迟执行的行为非常相似。该结果集不调过来完全直到（或除非）你特别要求每一个行。

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.

所以，一个简​​单的分页查询，会做的伎俩。或在其他情况下的某种总结报告的汇总那些100万条记录一两页的相关数据。数据

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.

当然，如果你确实需要来回移动通过网页，某种形式的缓存可能是有意义的，但再仔细想想：如何往往会用户实际需要浏览100万条记录 - 也许永远

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.

随着最后一个音符，如果你实现分页 - 请确保您使用的方法来实现分页依靠SQL服务器在同一时间发送数据的一个网页上，而不是阅读所有的100万条记录到ASP.NET，然后分页因为该数据的本地副本将非常低效和缓慢的。下面是执行分页一个SQL Server查询的例子：SO问题＃109232

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