The topic Sample: Streaming to SQL Server does show how parameters can be set from a stream.
Whilst System.Text.Json can serialize to a stream it wasn't apparent to me if/how it is possible to get it to serialize an enumerable and have the evaluation of the enumerable deferred until used as a parameter value for the equivalent stored proc.
CREATE OR ALTER PROC dbo.TestJsonPerf
@JsonData NVARCHAR(MAX)
AS
SELECT COUNT(*)
FROM OPENJSON(@JsonData)
WITH (Prop1 int, Prop2 datetime2, Prop3 varchar(1000))
I did find one possible solution for this in this StackOverflow answer though. The below is (now very loosely) based on this and defers evaluating the enumerable until after the ExecuteScalarAsync is called. In the client process memory is very low throughout.
⚠️ Performance of this method is still worse
than using TVPs though. With the TVP code in the question on my local machine a typical run with 5 million rows took 8 to 9 seconds, the JSON streaming version took around 45 seconds. Of which around 25
seconds appeared to be spent sending the parameter value, so both the parameter passing and query execution times were much worse!
The parameter passing code likely still has some performance improvements that can be made but that won't help the query performance.
The TVP query ran at DOP 4 in batch mode on my machine and averaged 80ms per durations shown in query store whereas the JSON one got a serial plan and > 20 seconds average duration. In practice though it is unlikely that anyone will be sending 5 million JSON elements to SQL Server just to count them and the difference could well be less stark in more realistic use cases.
using System.Data;
using System.Text;
using System.Text.Json;
using Microsoft.Data.SqlClient;
const string connectionString =
@"...";
await JsonTest();
return;
static async Task JsonTest()
{
await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();
await using var cmd = new SqlCommand("dbo.TestJsonPerf", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
var data = GetEnumerableOfRandomDto(5_000_000);
var stream = new JsonArrayStream<Dto>(data);
cmd.Parameters.Add("@JsonData", SqlDbType.NVarChar, -1).Value =
new StreamReader(stream, Encoding.UTF8, bufferSize: 1_048_576);
Console.WriteLine($"calling ExecuteScalarAsync at {DateTime.Now:O}");
var result = await cmd.ExecuteScalarAsync();
Console.WriteLine($"writing result at {DateTime.Now:O}. Result was {result}");
}
static IEnumerable<Dto> GetEnumerableOfRandomDto(uint length)
{
var rnd = new Random();
for (var i = 0; i < length; i++)
{
yield return new Dto(i,
DateTime.UtcNow.AddMinutes(rnd.Next(1, 10000)),
Guid.NewGuid().ToString()
);
if ((i + 1) % 100_000 == 0)
Console.WriteLine($"Generated enumerable {i + 1} at {DateTime.Now:O}");
}
}
internal record Dto(int Prop1, DateTime Prop2, string Prop3);
public class JsonArrayStream<T> : Stream, IDisposable
{
private readonly IEnumerator<T> _input;
private bool _arrayClosed;
private byte[] _bytesToAppendNextRead = [(byte) '['];
private bool _firstElement = true;
private bool _disposed;
public JsonArrayStream(IEnumerable<T> input)
{
_input = input.GetEnumerator();
}
public override bool CanRead => true;
public override bool CanSeek => false;
public override bool CanWrite => false;
public override long Length => 0;
public override long Position { get; set; }
void IDisposable.Dispose()
{
if (_disposed)
return;
_disposed = true;
_input.Dispose();
}
public override int Read(byte[] buffer, int offset, int count)
{
var bytesWrittenThisRead = 0;
if (!TryAppendToBuffer(_bytesToAppendNextRead, buffer, ref offset, count, ref bytesWrittenThisRead))
return bytesWrittenThisRead;
while (_input.MoveNext())
{
if (!_firstElement)
{
//regardless of result of this call will proceed to the next step to ensure bytes for _input.Current are saved before MoveNext called
TryAppendToBuffer([(byte) ','], buffer, ref offset, count, ref bytesWrittenThisRead);
}
_firstElement = false;
if (!TryAppendToBuffer(JsonSerializer.SerializeToUtf8Bytes(_input.Current), buffer, ref offset, count, ref bytesWrittenThisRead))
return bytesWrittenThisRead;
}
if (!_arrayClosed)
{
TryAppendToBuffer([(byte)']'], buffer, ref offset, count, ref bytesWrittenThisRead);
_arrayClosed = true;
}
return bytesWrittenThisRead;
}
//Appends to stream buffer if possible. If no capacity saves any excess in _bytesToAppendNextRead
private bool TryAppendToBuffer(byte[] bytesToAppend, byte[] buffer, ref int offset, int count,
ref int bytesWrittenThisRead)
{
var bytesToAppendLength = bytesToAppend.Length;
if (bytesToAppendLength > 0)
{
var lengthToWrite = Math.Min(bytesToAppendLength, count - bytesWrittenThisRead);
Buffer.BlockCopy(bytesToAppend, 0, buffer, offset, lengthToWrite);
offset += lengthToWrite;
bytesWrittenThisRead += lengthToWrite;
if (bytesToAppendLength > lengthToWrite)
{
_bytesToAppendNextRead = _bytesToAppendNextRead.Length == 0 ?
bytesToAppend[lengthToWrite..] :
_bytesToAppendNextRead.Concat(bytesToAppend[lengthToWrite..]).ToArray();
return false;
}
_bytesToAppendNextRead = [];
}
return true;
}
public override long Seek(long offset, SeekOrigin origin)
{
throw new NotSupportedException();
}
public override void SetLength(long value)
{
throw new NotSupportedException();
}
public override void Write(byte[] buffer, int offset, int count)
{
throw new NotSupportedException();
}
public override void Flush()
{
throw new NotSupportedException();
}
}
