.NET - Begin reading from DataReader before query is complete?

I'm currently working on a program that is converting an old database to a newer style and one of the tasks I've been assigned is to remove some old binary columns and convert those into documents in our repository.

We have about 1.1 million rows in the table and my process takes about 12 hours to complete.

I'm looking for ways to make this all go a little faster and one thing I'm looking at i开发者_StackOverflow中文版s getting at the data quicker. If there a way I can get to the results of my query before it has completed? I've noticed that when I run a "select *" statement against this table in Query Analyzer I start getting results back immediately, but when I use ExecuteReader in .NET that query seems to take around 30 minutes before I can start reading the rows.

---

It may help to use command.ExecuteReader(CommandBehavior.SequentialAccess). However, doing so requires you to read columns in order, and requires you to read binary values in a different way. I recommend reading the documentation on SequentialAccess so you know what other changes to your code might need to be made.

Consider that the same query runs instantly in Query Analyzer. That tells me that the query does not inherently take 30 minutes to return the first result, or it would do so in Query Analyzer. I think the handling of large binary values is a likely explanation for this discrepancy. One quick way to test would be to change the query to select everything EXCEPT the binary columns, and see if that changes the time that ExecuteReader takes. If the execution time is the same without binary columns, then it's unlikely that SequentialAccess will help. If it's faster without the binary columns, then SequentialAccess may be worth using.

---

One bit of advice is to not use select * if you don't need all the columns in the query. Perhaps this question will help too: Performance optimization strategies of last resort