tipo - ¿Cómo serializo un gran gráfico de un objeto.NET en un BLOB de SQL Server sin crear un gran buffer?

subir imagenes a sql server

Question

Tenemos código como:

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

Pero el vapor de memoria asigna un gran buffer del gran montón de memoria que nos está dando problemas. Entonces, ¿cómo podemos transmitir los datos sin necesitar suficiente memoria libre para contener los objetos serializados?

Estoy buscando una manera de obtener un Stream del servidor SQL que luego se pueda pasar a bin.Serialize () para evitar guardar todos los datos en la memoria de mi proceso.

Lo mismo para leer los datos de vuelta ...

Un poco más de fondo.

Esto es parte de un complejo sistema de procesamiento numérico que procesa datos casi en tiempo real buscando problemas de equipos, etc., la serialización se realiza para permitir un reinicio cuando hay un problema con la calidad de los datos de un feed de datos, etc. (Almacenamos los datos alimentados y puede volver a ejecutarlos una vez que el operador haya editado los valores incorrectos).

Por lo tanto, serializamos el objeto mucho más a menudo que luego lo serializamos.

Los objetos que estamos serializando incluyen matrices muy grandes, en su mayoría de dobles, así como una gran cantidad de pequeños objetos "más normales". Estamos presionando el límite de memoria en un sistema de 32 bits y hacemos que el colector de garaje trabaje muy duro. (Los efectos se están realizando en otras partes del sistema para mejorar esto, por ejemplo, la reutilización de grandes matrices en lugar de crear nuevas matrices.)

A menudo, la serialización del estado es la última gota que cursa una excepción de falta de memoria; nuestro uso máximo de memoria es mientras se realiza esta serialización.

Creo que obtenemos una gran fragmentación de la agrupación de memoria cuando deserializamos el objeto, también espero que haya otro problema con la fragmentación de grandes grupos de memoria dado el tamaño de las matrices. (Esto aún no se ha investigado, ya que la persona que primero miró esto es un experto en procesamiento numérico, no un experto en administración de memoria).

¿Los clientes usan una combinación de Sql Server 2000, 2005 y 2008 y preferimos no tener diferentes rutas de código para cada versión de Sql Server si es posible?

Podemos tener muchos modelos activos a la vez (en procesos diferentes, en muchas máquinas), cada modelo puede tener muchos estados guardados. Por lo tanto, el estado guardado se almacena en un blob de base de datos en lugar de en un archivo.

Como la extensión de guardar el estado es importante, preferiría no serializar el objeto en un archivo, y luego poner el archivo en un BLOB bloque por bloque.

Otras preguntas relacionadas que he hecho

• ¿Cómo transmitir datos desde / a los campos BLOB de SQL Server?
• ¿Hay una clase como SqlFileStream que funciona con Sql Server 2005?

Answer 1

¿Cómo se ve el gráfico?

Un problema aquí es la corriente; el requisito de SQL 2005 es un problema, ya que de lo contrario podría escribir directamente en SqlFileStream , sin embargo, no creo que sería demasiado difícil escribir su propia implementación de Stream que almacena 8040 bytes (o algunos múltiples) y lo escribe incrementalmente. Sin embargo, no estoy seguro de que valga la pena esta complejidad adicional. Me sentiría tremendamente tentado de usar un archivo como el búfer de rayado y luego (una vez serializado) recorrer el archivo insertando / añadiendo fragmentos. No creo que el sistema de archivos vaya a perjudicar su rendimiento general aquí, y le ahorrará comenzar a escribir datos condenados, es decir, no hablará con la base de datos hasta que sepa qué datos desea escribir. También lo ayudará a minimizar el tiempo que la conexión está abierta.

El siguiente problema es la serialización en sí misma. Personalmente , no recomiendo usar BinaryFormatter para escribir en tiendas persistentes (solo para el transporte), ya que es específico de la implementación tanto en el codificador como en sus tipos (es decir, es frágil si realiza cambios inocentes en sus tipos de datos). )

Si sus datos pueden representarse suficientemente como un árbol (en lugar de un gráfico completo), estaría muy tentado de probar los búferes de protocolo / protobuf-net. Esta codificación (ideada por Google) es más pequeña que la salida BinaryFormatter , más rápida tanto para lectura como escritura, y está basada en contrato en lugar de en el campo, por lo que puede volver a rehidratarla confiablemente de nuevo más adelante (incluso si cambia de plataforma por completo).

Las opciones predeterminadas significan que tiene que escribir la longitud del objeto antes de cada objeto (lo que puede ser costoso en su caso), pero si tiene listas anidadas de objetos grandes (profundos) puede usar la codificación agrupada para evitar esta necesidad, permitiéndola para escribir la secuencia de una sola vez, de ida y vuelta; Aquí hay un breve ejemplo simple usando codificación agrupada, pero si quieres arrojarme un escenario más complejo, házmelo saber ...

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

Nota: Tengo algunas teorías sobre cómo hackear el formato de cable de Google para soportar gráficos completos, pero va a necesitar algo de tiempo para probarlo. Oh, re the "arrays muy grandes": para tipos primitivos (no objetos), puedes usar codificación "empaquetada" para esto; [DataMember(..., Options = MemberSerializationOptions.Packed)] - puede ser útil, pero difícil de decir sin visibilidad de su modelo.

Answer 2

¿Por qué no implementar su propio sistema :: io: clase derivada de la secuencia? que le permitiría adjuntarlo a la columna SQL directamente a través de UpdateText para escribir.

ej. (pseudo-código)

Insertar registro de BD con la columna de blob ''inicializada'' (ver artículo anterior de UpdateText)
Cree su tipo de secuencia / Asociar conexión de base de datos con la secuencia
Pasar la transmisión a la llamada de serialización

Podría dividirse (varios bytes de 8040 a la vez, supongo) las llamadas y en cada búfer completo pasar a la llamada DB UpdateText con el desplazamiento adecuado.

Al final de la secuencia, se enjuaga todo lo que quedaba que no llenaba completamente el búfer a través de UpdateText.

Del mismo modo, podría usar la misma secuencia derivada / similar para permitir la lectura desde una columna DB, pasando por la que se deserializará.

Crear un Stream derivado no es tanto trabajo, lo he hecho en C ++ / CLI para proporcionar interoperabilidad con IStream, y si puedo hacerlo:) ... (Puedo proporcionarte el código de secuencia de C ++ / CLI he hecho como muestra si eso fuera útil)

Si coloca toda la operación (Insertar fila inicial, llamadas para actualizar el blob a través de la secuencia) en una transacción, evitaría cualquier posible incoherencia de DB si falla el paso de serialización.

Answer 3

No hay funcionalidad incorporada de ADO.Net para manejar esto con gran gracia para datos grandes. El problema es doble:

• no hay una API para ''escribir'' en un comando o comandos SQL como en una secuencia. Los tipos de parámetros que aceptan una secuencia (como FileStream ) aceptan la transmisión para LEER de ella, lo que no está de acuerdo con la semántica de serialización de escritura en una secuencia. No importa en qué sentido lo hagas, terminas con una copia en la memoria de todo el objeto serializado, mal.
• incluso si el punto anterior fuera resuelto (y no puede serlo), el protocolo TDS y la forma en que SQL Server acepta los parámetros no funcionan bien con parámetros grandes ya que la solicitud completa debe recibirse antes de iniciarse en ejecución y esto sería crear copias adicionales del objeto dentro de SQL Server.

Entonces realmente tienes que acercarte a esto desde un ángulo diferente. Afortunadamente, hay una solución bastante fácil. El truco consiste en utilizar la sintaxis UPDATE .WRITE altamente eficiente y pasar los fragmentos de datos uno por uno, en una serie de sentencias T-SQL. Esta es la forma recomendada de MSDN; consulte Modificación de datos de gran valor (máximo) en ADO.NET . Esto parece complicado, pero en realidad es trivial y se conecta a una clase Stream.

La clase BlobStream

Este es el pan y la mantequilla de la solución. Una clase derivada de Stream que implementa el método Write como una llamada a la sintaxis T-SQL BLOB WRITE. En línea recta, lo único interesante es que debe hacer un seguimiento de la primera actualización porque la sintaxis UPDATE ... SET blob.WRITE(...) fallaría en un campo NULL:

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
 }

Usando el BlobStream

Para usar esta clase de flujo blob recién creado, conéctelo a un BufferedStream . La clase tiene un diseño trivial que maneja solo escribir la secuencia en una columna de una tabla. Reutilizaré una tabla de otro ejemplo:

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

Agregaré un objeto ficticio para ser serializado:

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

Finalmente, la serialización real. Primero insertaremos un nuevo registro en la tabla de Uploads , luego crearemos un BlobStream en el ID recién insertado y llamaremos a la serialización directamente a esta secuencia:

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

Si supervisa la ejecución de esta muestra simple, verá que en ninguna parte se crea una gran secuencia de serialización. La muestra asignará la matriz de [1024 * 1024], pero eso es para propósitos de demostración para tener algo que serializar. Este código se serializa de forma amortiguada, fragmento por fragmento, utilizando el tamaño de actualización recomendado de SQL Server BLOB de 8040 bytes a la vez.

Answer 4

Siempre puede escribir en SQL Server en un nivel inferior utilizando el protocolo TDS (flujo de datos tabulares) que Microsoft ha utilizado desde el primer día. ¡Es poco probable que lo cambien pronto ya que incluso SQLAzure lo usa!

Puede ver el código fuente de cómo funciona esto desde el proyecto Mono y desde el proyecto freetds

Mira el tds_blob

• http://www.mono-project.com/TDS_Generic

• http://www.mono-project.com/SQLClient

• http://www.freetds.org/

Answer 5

Tenga en cuenta que desde SQL Server 2012 también hay FileTable que es similar a FILESTREAM, excepto que también permite el acceso no transaccional.

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

Answer 6

Todo lo que necesita es .NET Framework 4.5 y la transmisión. Supongamos que tenemos un archivo grande en HDD y queremos subir este archivo.

Código SQL:

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

Funciona bien para mí He cargado correctamente el archivo de 400 mb, mientras que MemoryStream arrojó una excepción cuando traté de cargar este archivo en la memoria.

UPD: Este código funciona en Windows 7, pero falló en Windows XP y 2003 Server.

Answer 7

Yo iría con los archivos. Básicamente use el sistema de archivos como un intermediario entre SQL Server y su aplicación.

1. Al serializar un objeto grande, serialícelo en un FileStream .

2. Para importarlo a la base de datos, indique a la base de datos que use el archivo directamente al guardar los datos. Probablemente se vería algo como esto:

   INSERT INTO MyTable ([MyColumn]) SELECT b.BulkColumn, FROM OPENROWSET (BULK N''C: / Path to My File / File.ext '', SINGLE_BLOB) como b

3. Cuando vuelva a leer los datos, solicite al SQL que guarde la columna grande nuevamente en el sistema de archivos como un archivo temporal, que eliminará después de deserializarla en la memoria (no es necesario borrarla inmediatamente, ya que aquí se puede hacer el almacenamiento en caché). No estoy muy seguro de cuál es el comando sql para eso, ya que estoy seguro de que no hay un experto en DB, pero estoy bastante seguro de que debe haber uno.

4. Usando nuevamente un objeto FileStream para deserializarlo nuevamente en la memoria.

Este procedimiento se puede generalizar en una clase auxiliar para hacerlo, que sabrá cuándo eliminar esos archivos temporales, ya que puede reutilizarlos si está seguro de que el valor del registro de datos sql no ha cambiado.