C# handling result sets from SQL Server

Question

Hi,

I have large wait times in ASYNC_NETWORK_IO on my SQL Server and I found this interesting article

http://blogs.msdn.com/joesack/archive/2009/01/09/troubleshooting-async-network-io-networkio.aspx

the part i find interesting is:

• Identify large result sets and verify with the application team (or developers) how this is being consumed. Red flags include the application querying large results sets but not processing more than a few rows at a time

If you are receiving a result set back from a linq query then you have processed it(?) How would you process it a few rows at a time(?) is this meaning an sql reader?

How would you go about finding if the application is causing the ASYNC_NETWORK_IO problem?

curious

Answer 1

Many developers tend to query large results from LINQ to SQL and then trim them down instead of relying on a pattern that leverages IQueryable which means you are building a more refined query instead of refined data subset.

You may be seeing this happening: they need only a subset but they are coding to pull everything, then they filter the data out to what they actually need.

The repository pattern with IQueryable will ensure the most optimized use of SQL Server server by allowing the developer to query a large resultset and trim it down at many levels until it's actually needed. By that time the query isn't:

1. Get all
2. Get data
3. Filter subset
4. Get data
5. Filter another subset
6. Get data
7. Display

It's:

1. From all apply a filter and then another filter
2. Get data
3. Display

Just my thoughts.