从数据库中获取大约一百万条记录的最佳方法是什么？

Question

我需要在一个网页上获取和显示数据，这些数据的记录数可能会因为筛选器而变化，从大约500个记录到100万个记录不等。

由于将百万条记录存入内存并不明智，所以缓存是否有用呢？SqldataReader？

当然必须实现分页。显示100万条记录是最坏的情况（使用场景中愚蠢的All filter！）。

我应该使用连接架构（SqlDataReader）还是离线架构（DataSets）？

- Manish

---

我只是试图计算一天中是否有足够的时间翻阅一百万行数据。假设每页有40行，那么需要25000页，每页浏览1分钟，则需要25000分钟或大约17天的时间 - 哎呀。 - Jon Black

如果你问我，展示一百万条记录毫无意义。这里的情景是什么？ - Brian Rasmussen

如果你需要处理数百万条记录，一定要使用SqlDataReader。由于架构的原因，DataSets可能会变得非常无效。请参见下面的答案。 - Mike Dinescu

去掉“全部”筛选器。启用更高级的筛选功能，使用户可以钻取到他们想要的东西。 - Chase Florell

个人而言，我会使用离线的数据表（而不是集合），或者自己编写可序列化的业务对象，通过数据读取器填充。因为在并发负载下，它们比数据读取器更具可扩展性。- http://msdn.microsoft.com/en-us/library/ms978388.aspx - Jon Black

Answer 1

首先，要这样考虑：向用户展示一百万条记录对任何用户都没有意义。因此，你必须考虑用户想要看到什么。也许是摘要？！也许将记录分页，每页显示25、50或100条记录。这些方法之一都不需要在内存中同时保存1M条记录。

此外，当你针对 SQL 数据库运行查询并使用 SqlDataReader 时，你将不会收到所有记录，而是 SQL 驱动程序将查询发送到 SQL 服务器，服务器将执行查询，准备一个结果集并创建一个仅可向前的游标。然后驱动程序将每次在你的 SqlDataReader 上调用Read()时抓取一条记录。如果你使用延迟执行的 LINQ to SQL，行为非常类似。结果集直到（或除非）你明确请求每一行才会全部传输过来。

因此，简单的分页查询就能搞定了。或者在其他情况下，可以生成某种汇总报告，从这1百万条记录中聚合数据并显示一两页相关数据。

当然，如果您需要在页面之间来回浏览，可能需要某种缓存，但再次考虑一下：用户有多经常需要浏览1百万条记录 - 可能永远不需要。

最后，请确保你使用的分页方法依赖于 SQL 服务器一次只发送一页数据，而不是将所有1百万条记录读入 ASP.NET 中，然后对本地副本的数据进行分页，因为那将非常低效和缓慢。这里有一个执行分页的 SQL Server 查询示例：SO Question #109232

- Mike Dinescu

---

非常详细的解释。谢谢！ 但还有一个问题：在这种情况下，数据集(DataSets)会不会有帮助？ - Manish

一个 DataSet 的工作方式不同。与 SqlDataReader 不同，它具有单向行为，DataSet 在操作数据之前必须将所有数据从服务器本地获取。这使得在某些情况下更容易使用，但如果您的查询可能返回 100 万条记录，则 DataSet 不是解决方案。最终一切取决于查询。 - Mike Dinescu

Answer 2

我同意其他回答者的观点。展示1M条记录是荒谬的。但是，您可以显示前X条记录，并进行分页。

关键在于执行提取数据的存储过程中。

ALTER PROCEDURE [dbo].[MyHugeTable_GetWithPaging] 
( 
        @StartRowIndex      int, 
        @MaximumRows        int 
) 

AS 
SET NOCOUNT ON 

Select 
    RowNum, 
    [UserName]
From 
    (Select 
        [ID], 
        [UserName]
        Row_Number() Over(Order By [ID] Desc) As RowNum 
        From dbo.[MyHugeTable] t) 
As DerivedTableName 
Where RowNum Between @StartRowIndex And (@StartRowIndex + @MaximumRows) 

- Chase Florell

---

实际上，该数据库是 MS-SQL Server 2008。 - Manish

Answer 3

如果您的服务器无法缓存100万条记录，那么您认为用户的Web浏览器如何处理价值100万条记录的HTML呢？

考虑分页（这里有一个包含100万条记录的示例）

还要考虑到用户从来不想要超过30到50条记录。您要么显示了太低级别的细节，要么需要更多的筛选。

- tster

---

那个链接很棒。有点像我想要的。但它在后台使用的是什么.. DataSets吗？我看到检索非常快。 - Manish

@Manish，背景与该网格无关。使用任何语言，您都可以连接到任何数据库。然后，诀窍在于编写SQL查询，正确利用索引，以便快速返回行n-m。 - tster

Answer 4

我建议使用带分页的动态查询。这样，当您点击特定页面时，只获取那些页面的记录。要从数据库中获取特定范围的记录，请使用以下查询语句。

像这样。

Create proc Test

@take smallint,
@skip smallint,
@orderBy nvarchar(20),
@subscriptionid smallint,


as 

DECLARE @SQLQuery AS NVARCHAR(max)

SET @SQLQuery=' Select ROW_NUMBER() OVER (ORDER BY P.ProductId desc) as RowNum,* from product"

set @SQLQuery=@SQLQuery + ' and Subscriptionid='+CONVERT(nvarchar, @subscriptionid) 

set @SQLQuery= ';WITH Results_CTE AS ( '+@SQLQuery

    set @SQLQuery= @SQLQuery +' ) SELECT * FROM Results_CTE WHERE RowNum > '+CONVERT(nvarchar, @skip)+' AND RowNum <= '+CONVERT(nvarchar, @skip+@take)  --//paging';
END

EXECUTE sp_executesql @SQLQuery 

- virender