Are there any benefits of reading each field async from a SqlDataReader?
Repeating what Marc wrote, with a snippet from the official ADO.NET docs - that unfortunately have been moved to Microsoft Blog Archives:
Read vs ReadAsync – It is also a good idea to call ReadAsync: in non-sequential mode this will read in all column data, which can potential span multiple packets, allowing faster access to the column values. In sequential mode ADO.NET will need to finish reading the data for the current row (if it hasn’t been read entirely), and there is the potential for some TDS tokens to sit between rows which can then be read asynchronously.
IsDBNull and GetFieldValue vs IsDBNullAsync and GetFieldValueAsync - If you had previously called ReadAsync and are using non-sequential access, then calling the synchronous versions of these methods will provide the best performance since the column data has already been read and processed (so calling the asynchronous method just adds the overhead of wrapping the value in a Task). However, if you called Read in non-sequential access mode, or if you are using sequential access mode, then the decision is much harder as you need to consider how much data you need to read to get to your desired column and how much data that column may contain. If you’ve read the previous column, and the target column is small (like a Boolean, a DateTime or a numeric type) then you may want to consider using a synchronous method. Alternatively, if the target column is large (like a varbinary(8000)) or you need to read past large columns, then using an asynchronous method is much better. Finally, if the target column is massive (like a varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML) then you should consider the new GetStream, GetTextReader or GetXmlReader methods instead.
After some peeking at reflector, the interesting methods here (GetFieldValueAsync<T>
, IsDBNullAsync
, and the internal
method GetBytesAsync
) only do "interesting" code for the CommandBehavior.SequentialAccess
scenario. So: if you're not using that: don't bother - the row data is already buffered in memory, and Task<T>
is pure overhead (although it will at least be an already-completed task result, i.e. Task.FromResult<T>
- which is handled efficiently by await
, without a context switch).