title: How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?
body: <p>We have code like:</p>
  <pre><code>ms = New IO.MemoryStream
  bin = New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
  bin.Serialize(ms, largeGraphOfObjects)
  dataToSaveToDatabase = ms.ToArray()
  // put dataToSaveToDatabase in a Sql server BLOB
  </code></pre>
  <p>But the memory steam allocates a <strong>large buffer</strong> from the large memory heap that is giving us problems.  So how can we stream the data without needing enough free memory to hold the serialized objects.</p>
  <p>I am looking for a way to get a Stream from SQL server that can then be passed to bin.Serialize() so avoiding keeping all the data in my processes memory.</p>
  <p><em>Likewise for reading the data back...</em></p>
  <hr />
  <p><strong>Some more background.</strong></p>
  <p>This is part of a complex numerical processing system that processes data in near real time looking for equipment problems etc, the serialization is done to allow a restart when there is a problem with data quality from a data feed etc.  (We store the data feeds and can rerun them after the operator has edited out bad values.)</p>
  <p>Therefore we serialize the object a lot more often then we de-serialize them.</p>
  <p>The objects we are serializing include <strong>very large arrays</strong> mostly of doubles as well as a lot of small “more normal” objects.  We are pushing the memory limit on 32 bit systems and make the garbage collector work very hard.   (Effects are being made elsewhere in the system to improve this, e.g. reusing large arrays rather then create new arrays.)</p>
  <p>Often the serialization of the state is the <a href="http://www.usingenglish.com/reference/idioms/last+straw.html" rel="nofollow noreferrer">last straw</a> that causes an out of memory exception; the peak of our memory usage is always during
  this serialization step.</p>
  <p>I <em>think</em> we get large memory pool fragmentation when we de-serialize the object, I expect there are also other problems with large memory pool fragmentation given the size of the arrays.  (This has not yet been investigated, as the person that first looked at this is a numerical processing expert, not a memory management expert.)</p>
  <p>Our customers use a mix of SQL Server 2000, 2005 and 2008 and we would rather not have different code paths for each version of SQL Server if possible.</p>
  <p>We can have many active models at a time (in different processes, across many machines), each model can have many saved states.  Hence the saved state is stored in a database blob rather then a file.</p>
  <p>As the spread of saving the state is important, I would rather not serialize the object to a file, and then put the file in a BLOB one block at a time.</p>
  <p><strong>Other related questions I have asked</strong></p>
  <ul>
  <li><a href="https://stackoverflow.com/questions/2101346/how-to-stream-data-from-to-sql-server-blob-fields">How to Stream data from/to SQL Server BLOB fields?</a></li>
  <li><a href="https://stackoverflow.com/questions/2116291/is-there-a-sqlfilestream-like-class-that-works-with-sql-server-2005">Is there a SqlFileStream like class that works with Sql Server 2005?</a></li>
  </ul>