System Troubleshooting - Pro SQL Server Internals - page 532

Listing 27-12. Reading and processing of the data: Incorrect implementation

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(cmdText, connection);
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ProcessRow((IDataRecord)reader);
}
}
}

The correct way of handling such a situation is by reading all rows first as fast as possible and processing them
after all rows have been read. Listing 27-13 illustrates this approach.

Listing 27-13. Reading and processing of the data: Correct implementation
List<Orders> orderRows = new List<Orders>();

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(cmdText, connection);
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
orderRows.Add(ReadOrderRow((IDataRecord)reader));
}
}
}

ProcessAllOrderRows(orderRows);

■ You can easily duplicate such behavior by running a test in management Studio connecting to a SQl Server
instance locally. it would use the Shared Memory protocol without any network traffic involved. You can clear wait
statistics on the server using the DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) command, and run a select
statement that reads a large amount of data displaying it in the result grid. if you checked the wait statistics after
execution, you would see a large number of ASYNC_NETWORK_IO waits due to the slow grid performance, even though
management Studio is running locally on a SQl Server box. after that, repeat the test with the Discard Results After
Execution configuration setting enabled. You should see the ASYNC_NETWORK_IO waits disappear.

Note