处理大型SQL查询选择/读取的块SQL数据 [英] Handling large SQL select queries / Read sql data in chunks

问题描述

我使用.NET 4.0和SQL Server 2008 R2。

I'm using .Net 4.0 and SQL server 2008 R2.

我正在返回结果的数以百万计，并采取了很长的一个大的SQL SELECT查询时间充分运行。

I'm running a big SQL select query which returns millions of results and takes up a long time to fully run.

有谁知道我怎么只能读一些查询返回的结果，而不必等待整个查询完成？

Does anyone know how can I read only some of the results returned by the query without having to wait for the whole query to complete?

在换句话说，我想通过10000条记录块，而查询仍然运行和读取的第一个获得下一个结果。

In other words, I want to read the first by 10,000 records chunks while the query still runs and getting the next results.

推荐答案

这部分取决于查询本身是否正在流，还是做大量的临时表工作的然后的（最终）开始返回数据。你不能做太多，除了第二个方案重新编写查询;然而，在第一种情况下迭代器块通常会有所帮助，即

It depends in part on whether the query itself is streaming, or whether it does lots of work in temporary tables then (finally) starts returning data. You can't do much in the second scenario except re-write the query; however, in the first case an iterator block would usually help, i.e.

public IEnumerable<Foo> GetData() {
     // not shown; building command etc
     using(var reader = cmd.ExecuteReader()) {
         while(reader.Read()) {
             Foo foo = // not shown; materialize Foo from reader
             yield return foo;
         }
     }
}

这现在是一个流迭代器 - 您可以的foreach 过它，它会检索记录从传入TDS数据活不下去首先缓冲所有的数据

This is now a streaming iterator - you can foreach over it and it will retrieve records live from the incoming TDS data without buffering all the data first.

如果你（也许是明智的）不想写自己的物化的代码，有工具，会为你做到这一点 - 例如，LINQ到SQL的的executeQuery< T>（TSQL，参数）会做上述无痛苦。

If you (perhaps wisely) don't want to write your own materialization code, there are tools that will do this for you - for example, LINQ-to-SQL's ExecuteQuery<T>(tsql, args) will do the above pain-free.

这篇关于处理大型SQL查询选择/读取的块SQL数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！