2

Speaking to developers about the use of SqlDataAdapter vs SqlDataReader and reading this: https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader

Yields little in the way of explaining the effect on SQL Server of switching from SqlDataAdapter to SqlDataReader.

I understand SqlDataReader potentially increases the length of time a connection is open to SQL Server, and that certainly is a concern when it comes to efficient use of pooled connections.

Aside from this, what is the effect on SQL Server of using SqlDataReader instead of SqlDataAdapter? Has anyone observed the effect of this change on SQL Server?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Peter
  • 1,500
  • 1
  • 16
  • 36

2 Answers2

6

In my experience, I think of SqlDataAdapter sort of like a .net SELECT * INTO #TEMP except the #TEMP is on the client computer. The entire result set is read from SQL Server and that connection is no longer needed. From a SQL Server perspective, the query comes in, runs and gets out. From a client perspective, the entire result set is loaded into memory on the client computer. This can be extremely problematic when reading large amounts of data.

On the other hand, I think of SqlDataReader as a cursor mechanism whereby a SQL Server connection is opened and the SqlDataReader serves up one row at a time. The application might do a bunch of work between each read and that can cause the connection to SQL Server to stay open for an extended amount of time. This technique can allow the application to process a large result set without having to cache the entire set of data on the client computer.

Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52
2

There is usually not a significant difference between the two, from a SQL Server point-of-view.

The worry is that the DataReader the queries will run longer, since the client will perform some work while fetching results, instead of fetching all the results into memory and then processing them.

But typically even when using a DataReader, the application will quickly process the result rows, and not significantly extend the lifetime of the query. It's simply an unusual thing for an application to sequentially process a large result set, performing expensive work as it goes. And even in applications where that does happen, it's probably not happening very frequently.

And in those scenarios where it does happen, it's probably because it's more efficient or convenient to not buffer the results on the client. Copying a large result set into client memory is not free, and not always possible.

Even so, having sessions blocked in a ASYNC_NETWORK_IO wait shouldn't be a big deal. If your query plan involves spooled results (either through a temp table or the query plan) any S locks will already be released. But hopefully, you're using READ COMMITTED SNAPSHOT isolation and queries don't require S locks at all.

It's possible that you could use more concurrent connections and more memory on SQL Server if clients fetch slowly, but IMO it doesn't rise to the level to support a general recommendation about how applications should read data. Any problems caused by slow fetching can be identified and remediated on a case-by-case basis.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102