有效地将大量的文本到数据库 [英] Efficiently adding large amounts of text to a database

问题描述

我有一个C＃应用程序，需要大量的文字添加到SQL Server数据库（一个行可能有多达文字1GB）。所以，我有一些code，看起来像：

I have a C# app that needs to add large amounts of text to a SQL Server database (one row could have up to 1GB of text). So, I have some code that looks like:

SqlParameter param = command.Parameters.Add("@text", SqlDbType.NVarChar);
param.Value = new string(buffer);

的文本块中添加，所以缓冲区说，20 MB。我试着通过重复使用的缓冲区，以减少内存pressure（而不是做一个新的char [千万]每次我需要增加一大块时间）。我所注意到的是，ADO.NET似乎被分配一个新的缓冲区，每次我的存储过程调用。每次存储过程被调用时，我看到我的进程的工作集上升了几乎整整20 MB。

The text is added in chunks, so the buffer is say, 20 MB. I've tried to reduce memory pressure by re-using buffers (as opposed to doing a new char[10000000] every time I need to add a chunk). What I'm noticing is that ADO.NET seems to be allocating a new buffer for every time my sproc is called. Every time the sproc is called, I see my process's working set go up by almost exactly 20 MB.

现在，你可能会认为是因为新的字符串（缓冲）（我一样），但我得到了相同的行为，当我做

Now, you might think that's because of the "new string(buffer)" (I did), but I get the same behavior when I do

param = command.Parameters.Add("@text", SqlDbType.Text, buffer.Length);
param.Value = buffer;

有没有什么办法prevent ADO.NET从我每次添加一个文本块到数据库做额外的内存分配？

Is there any way to prevent ADO.NET from doing the extra memory allocation every time I add a chunk of text to the database?

谢谢！

推荐答案

如果您使用 SqlDbType.VarBinary 与字节[] 磁盘阵列，然后ADO.NET将使用您提供无复制的缓冲区。

If you use SqlDbType.VarBinary with a byte[] array, then ADO.NET will use the buffer you provide without copying.

这篇关于有效地将大量的文本到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！