Handling large SQL select queries / Read sql data in chunks

Question

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

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?

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

Answer 1

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;
         }
     }
}

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.

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.