Performance test using simplified version of ValueStringBuilder on SQL statement string concatenation

BenchmarkSqlBuildWay.cs

using System.Buffers;
using System.Dynamic;
using System.Runtime.CompilerServices;
using System.Text;
using BenchmarkDotNet.Attributes;

namespace Benchmarks;

/* record
| Method        | Mean      | Error     | StdDev    | Gen0    | Gen1   | Allocated |
|-------------- |----------:|----------:|----------:|--------:|-------:|----------:|
| StringAdd     | 14.121 us | 0.2606 us | 0.5381 us | 15.0299 | 3.7537 |  61.45 KB |
| StringBuilder |  1.626 us | 0.0306 us | 0.0300 us |  0.9651 | 0.1373 |   3.95 KB |
| VSB           |  1.487 us | 0.0296 us | 0.0262 us |  0.6409 | 0.0916 |   2.63 KB |
*/

[MemoryDiagnoser]
public class BenchmarkSqlBuildWay
{
    IReadOnlyList<File> _samples = Array.Empty<File>();
    string[] _paramNames = Array.Empty<string>();

    [GlobalSetup]
    public void Setup()
    {
        _samples = Enumerable.Range(1, 16)
            .Select(index =>
            {
                var name = index.ToString("0##") + ".log";
                return new File
                {
                    FileName = name,
                    Url = "https://xxx.xxx.xxx/xxx/" + name
                };
            })
            .ToList();
        _paramNames = Enumerable.Range(0, short.MaxValue)
            .Select(i => "@p" + i.ToString())
            .ToArray();
    }

    [Benchmark]
    public (string sql, object parameter) StringAdd()
    {
        var comId = "abc";
        var type = 1;
        var parameters = new ExpandoObject() as IDictionary<string, object>;
        parameters.Add("com_id", comId);
        parameters.Add("type", type);

        var files = _samples;

        string sql = "";

        for (int i = 0; i < files.Count; i++)
        {
            sql += $@"
                        INSERT INTO `app`.`file`
                        (`id`,`type`,`name`,`url`)
                        VALUES
                        (@id, @type, @name_{i}, @url_{i});";

            parameters.Add($"name_{i}", Path.GetFileNameWithoutExtension(files[i].FileName));
            parameters.Add($"url_{i}", files[i].Url);
        }
        return (sql, parameters);
    }

    [Benchmark]
    public (string sql, object parameter) StringBuilder()
    {
        var comId = "abc";
        var type = 1;
        var files = _samples;
        var param = new Dictionary<string, object>(capacity: 2 + files.Count * 2, StringComparer.Ordinal)
        {
            ["@p0"] = comId,
            ["@p1"] = type,
        };
        var sqlBuilder = new StringBuilder(@"
INSERT INTO `app`.`file`
(`id`, `type`, `name`, `url`)
VALUES ");
        foreach (var file in files)
        {
            sqlBuilder.Append(Environment.NewLine);
            sqlBuilder.Append("(@p0, @p1");

            var paramName = _paramNames[param.Count];
            sqlBuilder.Append(", ");
            sqlBuilder.Append(paramName);
            param.Add(paramName, Path.GetFileNameWithoutExtension(file.FileName));

            paramName = _paramNames[param.Count];
            sqlBuilder.Append(", ");
            sqlBuilder.Append(paramName);
            sqlBuilder.Append("),");
            param.Add(paramName, file.Url);
        }
        sqlBuilder[^1] = ';';
        var sql = sqlBuilder.ToString();
        return (sql, param);
    }

    [Benchmark]
    public (string sql, object parameter) VSB()
    {
        var comId = "abc";
        var type = 1;
        var files = _samples;
        var param = new Dictionary<string, object>(capacity: 2 + files.Count * 2, StringComparer.Ordinal)
        {
            ["@p0"] = comId,
            ["@p1"] = type,
        };
        var sqlBuilder = new ValueStringBuilder(initialCapacity: short.MaxValue);
        sqlBuilder.Append(@"
INSERT INTO `app`.`file`
(`id`, `type`, `name`, `url`)
VALUES ");
        foreach (var file in files)
        {
            sqlBuilder.Append(Environment.NewLine);
            sqlBuilder.Append("(@p0, @p1");

            var paramName = _paramNames[param.Count];
            sqlBuilder.Append(", ");
            sqlBuilder.Append(paramName);
            param.Add(paramName, Path.GetFileNameWithoutExtension(file.FileName));

            paramName = _paramNames[param.Count];
            sqlBuilder.Append(", ");
            sqlBuilder.Append(paramName);
            sqlBuilder.Append("),");
            param.Add(paramName, file.Url);
        }
        sqlBuilder[^1] = ';';
        var sql = sqlBuilder.ToString();
        return (sql, param);
    }

    public class File
    {
        public string FileName { get; set; } = null!;

        public string Url { get; set; } = null!;
    }
}

internal ref struct ValueStringBuilder
{
    private char[]? _arrayToReturnToPool;

    private Span<char> _chars;

    private int _pos;

    public int Length
    {
        get => _pos;
        set => _pos = value;
    }

    public int Capacity => _chars.Length;

    public ref char this[int index] => ref _chars[index];

    public ValueStringBuilder(Span<char> initialBuffer)
    {
        _arrayToReturnToPool = null;
        _chars = initialBuffer;
        _pos = 0;
    }

    public ValueStringBuilder(int initialCapacity)
    {
        _arrayToReturnToPool = ArrayPool<char>.Shared.Rent(initialCapacity);
        _chars = _arrayToReturnToPool;
        _pos = 0;
    }

    public void EnsureCapacity(int capacity)
    {
        if ((uint)capacity > (uint)_chars.Length)
            Grow(capacity - _pos);
    }

    public override string ToString()
    {
        string result = _chars[.._pos].ToString();
        Dispose();
        return result;
    }

    public void Insert(int index, string s)
    {
        if (s != null)
        {
            int length = s.Length;
            if (_pos > _chars.Length - length)
                Grow(length);
            int length2 = _pos - index;
            _chars.Slice(index, length2).CopyTo(_chars[(index + length)..]);
            s.CopyTo(_chars[index..]);
            _pos += length;
        }
    }

    [MethodImpl(MethodImplOptions.AggressiveInlining)]
    public void Append(char c)
    {
        int pos = _pos;
        if ((uint)pos < (uint)_chars.Length)
        {
            _chars[pos] = c;
            _pos = pos + 1;
        }
        else
            GrowAndAppend(c);
    }

    [MethodImpl(MethodImplOptions.AggressiveInlining)]
    public void Append(string s)
    {
        if (s != null)
        {
            int pos = _pos;
            if (s.Length == 1 && (uint)pos < (uint)_chars.Length)
            {
                _chars[pos] = s[0];
                _pos = pos + 1;
            }
            else
                AppendSlow(s);
        }
    }

    private void AppendSlow(string s)
    {
        int pos = _pos;
        if (pos > _chars.Length - s.Length)
            Grow(s.Length);
        s.CopyTo(_chars.Slice(pos));
        _pos += s.Length;
    }

    public void Append(char c, int count)
    {
        if (_pos > _chars.Length - count)
            Grow(count);
        Span<char> span = _chars.Slice(_pos, count);
        for (int i = 0; i < span.Length; i++)
            span[i] = c;
        _pos += count;
    }

    public void Append(ReadOnlySpan<char> value)
    {
        int pos = _pos;
        if (pos > _chars.Length - value.Length)
            Grow(value.Length);
        value.CopyTo(_chars.Slice(_pos));
        _pos += value.Length;
    }

    [MethodImpl(MethodImplOptions.NoInlining)]
    private void GrowAndAppend(char c)
    {
        Grow(1);
        Append(c);
    }

    [MethodImpl(MethodImplOptions.NoInlining)]
    private void Grow(int additionalCapacityBeyondPos)
    {
        char[] array = ArrayPool<char>.Shared.Rent((int)Math.Max((uint)(_pos + additionalCapacityBeyondPos), (uint)(_chars.Length * 2)));
        _chars[.._pos].CopyTo(array);
        char[]? arrayToReturnToPool = _arrayToReturnToPool;
        _chars = _arrayToReturnToPool = array;
        if (arrayToReturnToPool != null)
            ArrayPool<char>.Shared.Return(arrayToReturnToPool);
    }

    [MethodImpl(MethodImplOptions.AggressiveInlining)]
    public void Dispose()
    {
        char[]? arrayToReturnToPool = _arrayToReturnToPool;
        this = default;
        if (arrayToReturnToPool != null)
            ArrayPool<char>.Shared.Return(arrayToReturnToPool);
    }
}