SQL:按顺序对 VarBinary 列执行 UPDATE .WRITE [英] SQL: Sequentially doing UPDATE .WRITE on VarBinary column

问题描述

我正在尝试创建一个小测试应用程序，它读取 FileStream 的块并将其附加到 SQL Server 2005 Express 上的 VarBinary(max) 列.

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.

我正在使用以下代码 (C#):

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;
                }
            }
        }
    }
}

谁能告诉我为什么它将文件缓冲到内存中?我正在使用的 byte[] 缓冲区大小只有将近 2 MB.

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

我可以为每个块创建一个新缓冲区，但这似乎也浪费了 CPU/内存...

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

推荐答案

它会缓冲它，因为当你将它保存到 varbinary 列时，它会成为 sql server 中 LOB 数据缓存的一部分.这就是它的工作原理.

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?

这篇关于SQL:按顺序对 VarBinary 列执行 UPDATE .WRITE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！