How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?

We have code like:

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

But the memory steam allocates a large buffer 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.

  • Using merge..output to get mapping between source.id and target.id
  • How to connect to SQL Server 2005 database through Ruby
  • Difference of create Index by using include column or not using
  • What does a timestamp in T-Sql mean in C#?
  • Order of execution in SQL Server variable assignment using SELECT
  • SQL query for parent-child chain
  • 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.

    Likewise for reading the data back…


    Some more background.

    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.)

    Therefore we serialize the object a lot more often then we de-serialize them.

    The objects we are serializing include very large arrays mostly of doubles as well as a lot of small “more normal” objects. We are pushing the memory limit on a 32 bit system and make the garage 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.)

    Often the serialization of the state is the last straw that courses an out of memory exception; our peak memory usage is while this serialization is being done.

    I think we get large memory pool fragmentation when we de-serialize the object, I expect there are also other problem 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.)

    Are 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.

    We can have many active models at a time (in different process, across many machines), each model can have many saved states. Hence the saved state is stored in a database blob rather then a file.

    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.

    Other related questions I have asked

    • How to Stream data from/to SQL Server BLOB fields?
    • Is there a SqlFileStream like class that works with Sql Server 2005?

  • Possible to view T-SQL syntax of a stored proc-based SqlCommand?
  • How to duplicate a SQL Server 2000 table programatically using .NET 2.0?
  • Encrypt In .NET / Decrypt in SQL Server
  • Wrong DateTime parsing when using other cultures
  • How does SqlConnection manage IsolationLevel?
  • insert record in one table by copying single row of a single column from another table
  • 7 Solutions collect form web for “How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?”

    There is no built-in ADO.Net functionality to handle this really gracefully for large data. The problem is two fold:

    • there is no API to ‘write’ into a SQL command(s) or parameters as into a stream. The parameter types that accept a stream (like FileStream) accept the stream to READ from it, which does not agree with the serialization semantics of write into a stream. No matter which way you turn this, you end up with a in memory copy of the entire serialized object, bad.
    • even if the point above would be solved (and it cannot be), the TDS protocol and the way SQL Server accepts parameters do not work well with large parameters as the entire request has to be first received before it is launched into execution and this would create aditional copies of the object inside SQL Server.

    So you really have to approach this from a different angle. Fortunately, there is a fairly easy solution. The trick is to use the highly efficient UPDATE .WRITE syntax and pass in the chunks of data one by one, in a series of T-SQL statements. This is the MSDN recommended way, see Modifying Large-Value (max) Data in ADO.NET. This looks complicated, but is actually trivial to do and plug into a Stream class.


    The BlobStream class

    This is the bread and butter of the solution. A Stream derived class that implements the Write method as a call to the T-SQL BLOB WRITE syntax. Straight forward, the only thing interesting about it is that it has to keep track of the first update because the UPDATE ... SET blob.WRITE(...) syntax would fail on a NULL field:

    class BlobStream: Stream
    {
        private SqlCommand cmdAppendChunk;
        private SqlCommand cmdFirstChunk;
        private SqlConnection connection;
        private SqlTransaction transaction;
    
        private SqlParameter paramChunk;
        private SqlParameter paramLength;
    
        private long offset;
    
        public BlobStream(
            SqlConnection connection,
            SqlTransaction transaction,
            string schemaName,
            string tableName,
            string blobColumn,
            string keyColumn,
            object keyValue)
        {
            this.transaction = transaction;
            this.connection = connection;
            cmdFirstChunk = new SqlCommand(String.Format(@"
    UPDATE [{0}].[{1}]
        SET [{2}] = @firstChunk
        WHERE [{3}] = @key"
                ,schemaName, tableName, blobColumn, keyColumn)
                , connection, transaction);
            cmdFirstChunk.Parameters.AddWithValue("@key", keyValue);
            cmdAppendChunk = new SqlCommand(String.Format(@"
    UPDATE [{0}].[{1}]
        SET [{2}].WRITE(@chunk, NULL, NULL)
        WHERE [{3}] = @key"
                , schemaName, tableName, blobColumn, keyColumn)
                , connection, transaction);
            cmdAppendChunk.Parameters.AddWithValue("@key", keyValue);
            paramChunk = new SqlParameter("@chunk", SqlDbType.VarBinary, -1);
            cmdAppendChunk.Parameters.Add(paramChunk);
        }
    
        public override void Write(byte[] buffer, int index, int count)
        {
            byte[] bytesToWrite = buffer;
            if (index != 0 || count != buffer.Length)
            {
                bytesToWrite = new MemoryStream(buffer, index, count).ToArray();
            }
            if (offset == 0)
            {
                cmdFirstChunk.Parameters.AddWithValue("@firstChunk", bytesToWrite);
                cmdFirstChunk.ExecuteNonQuery();
                offset = count;
            }
            else
            {
                paramChunk.Value = bytesToWrite;
                cmdAppendChunk.ExecuteNonQuery();
                offset += count;
            }
        }
    
        // Rest of the abstract Stream implementation
     }
    

    Using the BlobStream

    To use this newly created blob stream class you plug into a BufferedStream. The class has a trivial design that handles only writing the stream into a column of a table. I’ll reuse a table from another example:

    CREATE TABLE [dbo].[Uploads](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [FileName] [varchar](256) NULL,
        [ContentType] [varchar](256) NULL,
        [FileData] [varbinary](max) NULL)
    

    I’ll add a dummy object to be serialized:

    [Serializable]
        class HugeSerialized
        {
            public byte[] theBigArray { get; set; }
        }
    

    Finally, the actual serialization. We’ll first insert a new record into the Uploads table, then create a BlobStream on the newly inserted Id and call the serialization straight into this stream:

    using (SqlConnection conn = new SqlConnection(Settings.Default.connString))
                {
                    conn.Open();
                    using (SqlTransaction trn = conn.BeginTransaction())
                    {
                        SqlCommand cmdInsert = new SqlCommand(
    @"INSERT INTO dbo.Uploads (FileName, ContentType)
    VALUES (@fileName, @contentType);
    SET @id = SCOPE_IDENTITY();", conn, trn);
                        cmdInsert.Parameters.AddWithValue("@fileName", "Demo");
                        cmdInsert.Parameters.AddWithValue("@contentType", "application/octet-stream");
                        SqlParameter paramId = new SqlParameter("@id", SqlDbType.Int);
                        paramId.Direction = ParameterDirection.Output;
                        cmdInsert.Parameters.Add(paramId);
                        cmdInsert.ExecuteNonQuery();
    
                        BlobStream blob = new BlobStream(
                            conn, trn, "dbo", "Uploads", "FileData", "Id", paramId.Value);
                        BufferedStream bufferedBlob = new BufferedStream(blob, 8040);
    
                        HugeSerialized big = new HugeSerialized { theBigArray = new byte[1024 * 1024] };
                        BinaryFormatter bf = new BinaryFormatter();
                        bf.Serialize(bufferedBlob, big);
    
                        trn.Commit();
                    }
                }
    

    If you monitor the execution of this simple sample you’ll see that nowhere is a large serialization stream created. The sample will allocate the array of [1024*1024] but that is for demo purposes to have something to seralize. This code seralizes in a buffered manner, chunk by chunk, using the SQL Server BLOB recommeneded update size of 8060 8040 bytes at a time.

    All you need is .NET Framework 4.5 and streaming. Let’s assume we have a big file on HDD and we want to upload this file.

    SQL code:

    CREATE TABLE BigFiles 
    (
        [BigDataID] [int] IDENTITY(1,1) NOT NULL,
        [Data] VARBINARY(MAX) NULL
    )
    

    C# code:

    using (FileStream sourceStream = new FileStream(filePath, FileMode.Open))
    {
        using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE BigFiles SET Data=@Data WHERE BigDataID = @BigDataID"), _sqlConn))
        {
            cmd.Parameters.AddWithValue("@Data", sourceStream);
            cmd.Parameters.AddWithValue("@BigDataID", entryId);
    
            cmd.ExecuteNonQuery();
        }
    }
    

    Works good for me. I have successfully uploaded the file of 400 mb, while MemoryStream throwed an exception when I tried to load this file into memory.

    UPD: This code works on Windows 7, but failed on Windows XP and 2003 Server.

    You can always write to SQL Server at a lower level using the over the wire protocol TDS (tabular data stream) that Microsoft has used since day one. They are unlikely to change it any time soon as even SQLAzure uses it!

    You can see source code of how this works from the Mono project and from the freetds project

    Check out the tds_blob

    What does the graph look like?

    One problem here is the stream; the SQL 2005 requirement is a pain, as otherwise you could write directly to SqlFileStream, however, I don’t think it would be too hard to write your own Stream implementation that buffers 8040 (or some multiple) bytes and writes it incrementally. However, I’m not sure that it is worth this extra complexity – I would be hugely tempted to just use a file as the scratch buffer and then (once serialized) loop over the file inserting/appending chunks. I don’t think that the file system is going to hurt your overall performance here, and it will save you starting to write doomed data – i.e. you don’t talk to the database until you already know what data you want to write. It will also help you minimise the time the connection is open.

    The next problem is the serialization itself. Personally I don’t recommend using BinaryFormatter to write to persistent stores (only for transport), since it is implementation specific both in the encoder itself, and in your types (i.e. it is brittle if you make innocent-looking changes to your data types).

    If your data can be represented sufficiently as a tree (rather than a full graph), I would be very tempted to try protocol buffers / protobuf-net. This encoding (devised by Google) is smaller than the BinaryFormatter output, faster both for read and write, and is contract-based rather than field-based, so you can reliably rehydrate it again later (even if you switch platform entirely).

    The default options mean that it has to write the object-length before each object (which might be expensive in your case), but if you have nested lists of large (deep) objects you can use grouped encoding to avoid this need – allowing it to write the stream in a forwards-only, single-pass way; here’s a brief simple example using grouped encoding, but if you want to throw a more complex scenario at me, just let me know…

    using System;
    using System.Collections.Generic;
    using System.IO;
    using ProtoBuf;
    [ProtoContract]
    public class Foo {
        private readonly List<Bar> bars = new List<Bar>();
        [ProtoMember(1, DataFormat = DataFormat.Group)]
        public List<Bar> Bars { get { return bars;}}
    }
    [ProtoContract]
    public class Bar {
        [ProtoMember(1)]
        public int Id { get; set; }
        [ProtoMember(2)]
        public string Name { get; set; }
    }
    static class Program {
        static void Main() {
            var obj = new Foo { Bars = {
                new Bar { Id = 123, Name = "abc"},
                new Bar { Id = 456, Name = "def"},
            } };
            // write it and show it
            using (MemoryStream ms = new MemoryStream()) {
                Serializer.Serialize(ms, obj);
                Console.WriteLine(BitConverter.ToString(ms.ToArray()));
            }
        }
    }
    

    Note: I do have some theories on how to hack Google’s wire format to support full graphs, but it is going to need some time to try it. Oh, re the “very large arrays” – for primitive types (not objects) yuo can use “packed” encoding for this; [DataMember(..., Options = MemberSerializationOptions.Packed)]might be useful, but hard to say without visibility of your model.

    Why not implement your own system::io:stream derived class? which would allow you to attach it to the SQL column directly via UpdateText for writing.

    eg (pseudo-code)

    Insert DB Record with blob column
    ‘initialized’ (see above UpdateText
    article)
    Create Your Stream Type /
    Associate DB connection with the
    stream
    Pass the stream to the
    serialize call

    It could chunk up (Multiple of 8040 bytes at a time, i presume) the calls to it and on each full buffer pass that to the DB UpdateText call with the proper offset.

    On close of the stream you’d flush whatever was left that didn’t fill the buffer entirely via UpdateText.

    Likewise you could use the same/similar derived stream to allow reading from a DB column, passing that to be deserialized.

    Creating a derived Stream is not all that much work – i’ve done it in C++/CLI to provide interoperability with IStream’s — and if i can do it :)… (i can provide you the C++/CLI stream code i’ve done as a sample if that would be helpful)

    If you put the entire operation (Insert of initial row, calls to update the blob via the stream) into a transaction you would avoid any potential db inconsistencies if the serialization step fails.

    I would go with files. Basically use the file system as an intermediate between the SQL Server and your application.

    1. When serializing a large object, serialize it into a FileStream.
    2. To import it into the database instruct the database to use the file directly when saving the data. Would probably look something like this:

      INSERT INTO MyTable
      ( [MyColumn] )
      SELECT b.BulkColumn, FROM OPENROWSET(BULK N’C:\Path To My File\File.ext’, SINGLE_BLOB) as b

    3. When reading back the data, instruct the SQL to save the big column back into the file system as a temporary file, which you will delete after deserializing it into memory ( no need to delete it immediately, as possible caching can be done here ). Not really sure what the sql command is for that as I am for sure no DB expert, but I am pretty sure there must be one.

    4. Using again a FileStream object to deserialize it back into memory.

    This procedure can be generalized into a helper class to do it, which will know when to delete those temporary files, as you can reuse them if you know for sure that the value of the sql data record hasn’t changed.

    Note that since SQL Server 2012 there’s also FileTable what is similar to FILESTREAM except that allows non-transactional access too.

    https://msdn.microsoft.com/en-us/library/hh403405.aspx#CompareFileTable

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.