SQL: Sequentially doing UPDATE .WRITE on VarBinary column

Question

Hi all,

I'm trying to create a little test application which reads chunks of a FileStream and appends it to a VarBinary(max) column on an SQL Server 2005 Express.

Everything works - the column gets filled as it's supposed to, but my machine still seems to buffer everything into memory and I just can't see why.

I'm using the following code (C#):

using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString))
{
    connection.Open();

    string id = Guid.NewGuid().ToString();

    using (IDbCommand command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO [BLOB] ([Id],[Data]) VALUES (@p1,0x0)";

        SqlParameter param = new SqlParameter("@p1", SqlDbType.VarChar);
        param.Value = id;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

    if (File.Exists(textBox1.Text))
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "UPDATE [BLOB] SET [Data].WRITE(@data, @offset, @len) WHERE [Id]=@id";

            SqlParameter dataParam = new SqlParameter("@data", SqlDbType.VarBinary);
            command.Parameters.Add(dataParam);

            SqlParameter offsetParam = new SqlParameter("@offset", SqlDbType.BigInt);
            command.Parameters.Add(offsetParam);

            SqlParameter lengthParam = new SqlParameter("@len", SqlDbType.BigInt);
            command.Parameters.Add(lengthParam);

            SqlParameter idParam = new SqlParameter("@id", SqlDbType.VarChar);
            command.Parameters.Add(idParam);
            idParam.Value = id;

            using (FileStream fs = new FileStream(textBox1.Text, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                byte[] buffer = new byte[2090400]; //chunk sizes that are multiples of 8040 bytes.
                int read = 0;
                int offset = 0;

                while ((read = fs.Read(buffer, 0, buffer.Length)) > 0)
                {
                    dataParam.Value = buffer;
                    offsetParam.Value = offset;
                    lengthParam.Value = read;

                    command.ExecuteNonQuery();

                    offset += read;
                }
            }
        }
    }
}

Can anybody tell me why it buffers the file into memory? The byte[] buffer I'm using is only almost 2 MB in size.

I could create a new buffer for each chunk, but that seems like a waste of CPU/memory also...

Answer 1

it buffers it because when you save it into the varbinary column it becomes part of the LOB data cache in sql server. that's how it works.

or do you mean it gets buffered somewhere else?

Answer 2

The FileStream class buffers input and output. You can call the Flush() method after each update to clear the internal buffers.

To be clear, it will only buffer up to the buffer size (4 KB).

In this case, I think your culprit is SqlExpress. When I executed your code and wrote to my local copy of SqlExpress, the memory usage by the sqlsrvr process jumped by around 1 GB. When I wrote to a non-local database, my memory usage remained flat.