Define a custom buffer size to streaming data to SQL Server

[AugustoDeveloper]

Hey, i have a question: Is there a way to define a custom buffer size to streaming data to SQL Server?

The code below uses a Stream to insert a large file into column called Data. The file size is 800MB .

const string Statement =
"""
INSERT INTO [dbo].[Stream]
    (Id, Data)
VALUES
    (@Id, @Data);
""";

using var input = File.OpenRead("/tmp/dfs/data/1/sample.txt");
using var connection = new SqlConnection(ConnectionString);

connection.Open();

using var cmd = new SqlCommand(Statement, connection);

cmd.Parameters.AddWithValue("@Data", input);
cmd.Parameters.AddWithValue("@Id", 1);

cmd.ExecuteNonQuery();

I noticed when i run, the chunk size used to streaming is 4096 and cannot find a way to set a different size. Debugging the code i found the line where is create the buffer with a constant size:

https://github.com/dotnet/SqlClient/blob/fc514f6b8ffe0e0b664daaf5dad4f7f883a8a975/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/TdsParser.cs#L12872C12-L12880C55

Can you help me ?

Thanks

[apoorvdeshmukh]

@AugustoDeveloper There is no API that allows configuring this buffer size. You can refer to the streaming support documentation here.

[AugustoDeveloper]

Yeah, i already readed this doc... no much helpful for this case. Is there any chance in future to create some api for this?

@AugustoDeveloper There is no API that allows configuring this buffer size. You can refer to the streaming support documentation here.

[Wraith2]

Have you benchmarked it and seen that the buffer size is a bottleneck? If so can you share the bench setup?
I've have thought that the limit would be the network for this since it'll be sending to the server over the network. Unless the packet size is slightly larger than the buffer it'd complicate the packetization slightly but not enough to cause a performance problem.

[AugustoDeveloper]

Ok, I'll benchmark and bring the results.

Have you benchmarked it and seen that the buffer size is a bottleneck? If so can you share the bench setup?
I've have thought that the limit would be the network for this since it'll be sending to the server over the network. Unless the packet size is slightly larger than the buffer it'd complicate the packetization slightly but not enough to cause a performance problem.