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服务器中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屋！