1

Given a database connection via TCP
When I execute following code
Then I see in Task Manager that SQL Server always sends the queried data (when the Reader closes) over the network - even when we do not Read/Fetch them!

var command = new SqlCommand("SELECT TOP 100 Stamp, Blob", connection) { CommandTimeout = commandTimeout };
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection)
{
    // even if we do not read anything here
    //while (reader.Read())
    //{
        //var values = reader.GetValues();
        //if (check condition on values)
        //   break;
    //}
}

Is it normal? What is the cause? Any solutions?

We do not want to download all data returned by the query.

Pellared
  • 1,242
  • 2
  • 14
  • 29

1 Answers1

2

I believe that TDS does not have the ability to skip results. You cannot tell the server to skip sending them. Instead, the client must skip over them as they stream in.

Here are some options:

  1. Live with this behavior
  2. Cancel the command
  3. Close the connection
  4. Modify the query to not return results you don't need (why can't you do this? Seems like the obvious solution.)
usr
  • 168,620
  • 35
  • 240
  • 369
  • 3. What do you mean? To close connection before closing the command? 4. We know that we do not need further data only after analyzing the previous result (some conditions on the Blob data). – Pellared Mar 28 '14 at 17:24
  • 1
    3. Yes, that's possible. 4. Makes sense. Consider pulling data in smaller batches (i.e. 10 batches of 10 rows. That way you only need to skip over at most 9 superfluous rows). – usr Mar 28 '14 at 17:34
  • Great! Thanks! And what about: `SqlCommand.Cancel` http://msdn.microsoft.com/pl-pl/library/system.data.sqlclient.sqlcommand.cancel(v=vs.110).aspx Would it also work? I will test everything on Monday. – Pellared Mar 28 '14 at 17:44
  • 1
    That should work. It does the same thing that pressing the stop button in SSMS does. It appears I have already written about it: http://stackoverflow.com/a/19689451/122718 You should test whether Cancel aborts the active transaction or not. I don't know that. – usr Mar 28 '14 at 17:53
  • http://stackoverflow.com/questions/3505981/sqlcommand-cancel-causes-a-performance-boost It should work like a charm! Thanks for your help. – Pellared Mar 28 '14 at 17:56