Further investigate and improve perf for CommandBehavior.SequentialAccess #3453
Description
Continuation of #2053 and #3428.
This is a benchmark results with #3428 on main:
BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19041.685 (2004/?/20H1)
AMD Ryzen 5 2400G with Radeon Vega Graphics, 1 CPU, 8 logical and 4 physical cores
.NET Core SDK=5.0.101
[Host] : .NET Core 5.0.1 (CoreCLR 5.0.120.57516, CoreFX 5.0.120.57516), X64 RyuJIT
DefaultJob : .NET Core 5.0.1 (CoreCLR 5.0.120.57516, CoreFX 5.0.120.57516), X64 RyuJIT
| Method | NumChars | Mean | Error | StdDev | Median |
|---|---|---|---|---|---|
| Read | 2000 | 651.4 μs | 12.76 μs | 30.33 μs | 641.4 μs |
| ReadSequential | 2000 | 630.4 μs | 8.31 μs | 6.49 μs | 630.4 μs |
| Read | 4000 | 1,130.3 μs | 16.59 μs | 25.33 μs | 1,130.5 μs |
| ReadSequential | 4000 | 1,136.8 μs | 19.16 μs | 24.23 μs | 1,131.9 μs |
| Read | 8000 | 2,212.2 μs | 33.61 μs | 28.07 μs | 2,212.3 μs |
| ReadSequential | 8000 | 2,347.9 μs | 76.12 μs | 219.63 μs | 2,249.6 μs |
| Read | 16000 | 4,358.6 μs | 83.35 μs | 85.59 μs | 4,349.0 μs |
| ReadSequential | 16000 | 4,334.0 μs | 79.89 μs | 133.48 μs | 4,312.8 μs |
Benchmark code
using BenchmarkDotNet.Attributes;
using System.Data;
namespace Npgsql.Benchmarks
{
public class ReadBigRows
{
[Params(2000, 4000, 8000, 16000)]
public int NumChars { get; set; }
NpgsqlCommand Command { get; set; } = default!;
[GlobalSetup]
public void Setup()
{
var conn = BenchmarkEnvironment.OpenConnection();
Command = new NpgsqlCommand($"SELECT REPEAT('x', {NumChars}) FROM generate_series(1, 100)", conn);
Command.Prepare();
}
[Benchmark]
public void Read()
{
using var reader = Command.ExecuteReader(CommandBehavior.Default);
while (reader.Read())
{
reader.GetValue(0);
}
}
[Benchmark]
public void ReadSequential()
{
using var reader = Command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
reader.GetValue(0);
}
}
}
}And the same test but with a commented reader.GetValue(0);
| Method | NumChars | Mean | Error | StdDev | Median |
|---|---|---|---|---|---|
| Read | 2000 | 651.0 μs | 11.91 μs | 23.78 μs | 643.8 μs |
| ReadSequential | 2000 | 636.0 μs | 10.32 μs | 8.62 μs | 636.9 μs |
| Read | 4000 | 1,132.4 μs | 14.25 μs | 11.90 μs | 1,131.4 μs |
| ReadSequential | 4000 | 1,211.8 μs | 28.76 μs | 79.70 μs | 1,184.3 μs |
| Read | 8000 | 2,287.6 μs | 45.01 μs | 109.57 μs | 2,247.7 μs |
| ReadSequential | 8000 | 2,294.2 μs | 45.69 μs | 89.12 μs | 2,282.1 μs |
| Read | 16000 | 4,319.4 μs | 76.78 μs | 64.12 μs | 4,334.2 μs |
| ReadSequential | 16000 | 4,351.1 μs | 85.65 μs | 140.73 μs | 4,312.6 μs |
There is at least a few potential optimizations:
- add an ability for
NpgsqlDataReader.ConsumeRowto consume row non-sequentially (while reading sequentialy), if we've already read everything there is - add an ability for
NpgsqlDataReader.SeekToColumnto seek a column non-sequentially (while reading sequentialy), if it's already in the buffer