Investigate/improve perf for CommandBehavior.SequentialAccess (and ExecuteScalar()) #2053
Description
I'm doing some performance testing with some simple select * from test_table statements.
(table with ~20 columns with different datatypes (all native) with 200 rows)
first i was using CommandBehavior.SequentialAccess code like below
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
for(var i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
var fieldValue = reader.GetValue(i);
// do some mapping work
}
}
}Removing the CommandBehavior.SequentialAccess seems to make the code run faster, on my pc the CommandBehavior.SequentialAccess has used ~860ms for 200 calls, the command.ExecuteReader() without parameters ~780ms. I was thinking SequentialAccess would perform better because its more restrictive and mentioned in http://www.npgsql.org/doc/performance.html
But reading the code i noticed the CommandBehavior.SequentialAccess is made to reduce memory consumption, but not to improve performance, right?
If yes, its strange why NpgsqlCommand.ExecuteScalar uses the CommandBehavior.SequentialAccess? Would ExecuteScalar not perform better on buffered ExecuteReader?
NOTE: some ORM like dapper are using CommandBehavior.SequentialAccess everywhere, even if it maybe slower.