Is there any performance gain from CommandBehavior.SequentialAccess?

Question

I realized I always read my fields in the order they are returned by index (using constants). So my code is already compatible with CommandBehavior.SequentialAccess as far as i understand.

Would there be any benefits if i turn it on? DataReader is already forward only, read only which is the real performance gain right?

Answer 1

The main usage of this is when you are reading very large CLOB (nvarchar(max) etc) or BLOB (varbinary(max)) fields. In the default usage, it buffers the entire row of data before letting you near it - which could mean it has to allocate a large buffer for any BLOB / CLOB fields. When using sequential mode, it does not buffer the row; you can use the regular API for small fields (as long as you access them in the correct order), but for the CLOB / BLOB fields you can use the chunk-based APIs (GetBytes and GetChars) to access fractions of the data in turn. By doing this you could, for example, process a 40 MB image using only a 1k or 4k buffer.

MSDN says the same

Provides a way for the DataReader to handle rows that contain columns with large binary values. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. You can then use the GetBytes or GetChars method to specify a byte location to start the read operation, and a limited buffer size for the data being returned.

Answer 2

Yes, there is supposed to be at least some performance gain for using CommandBehavior.SequentialAccess, even when not accessing BLOBs. The Microsoft KB article, An "Invalid attempt to read from column ordinal" error occurs when you use DataReader in Visual C#, states:

Setting the CommandBehavior.SequentialAccess flag causes the DataReader to read both rows and columns sequentially. The rows and columns are not buffered. After you read past a column, it is dropped from memory. Any attempt to re-read the column or read previously read columns results in an exception.

Using the CommandBehavior.SequentialAccess flag provides a performance benefit, especially when using Binary Large Object (BLOB) fields. If you do not use SequentialAccess, all the BLOB data is copied to the client. This can consume a lot of resources.

CommandBehavior.SequentialAccess also improves performance when accessing non-BLOB fields. When CommandBehavior.SequentialAccess is not set, you can access a column out of order; however, you incur the following overhead:

• The column is checked to see if the column is later than a previous accessed column.
• The data for all of the previously accessed columns is retrieved and then cached for potential later retrieval.

Columns must be checked and cached because when you use the DataReader, the underlying stream is forward-only for rows as well as column access.