none
Duda sobre INSERT BULK e integridad referencial RRS feed

  • Pregunta

  • Hola a todos.

    Una duda sobre la carga de tablas con INSERT BULK. Antes de nada, comentar que no tengo opción de usar otro medio (el cliente es una entidad financiera y solo tiene certificado éste método, nada de SSIS. No me pregunteis por qué ¡ya quisiera yo usar SSIS!).

    Tengo tablas que tienen integridad referencial consigo mismas, es decir, tiene una FK que apunta a otro campo de la misma tabla. Los ficheros a cargar pueden ser bastantes grandes, con lo que se configuran lotes de 300.000 registros para hacer commit.

    La pregunta es ¿me podría dar esto problemas de integridad referencial? sí un registro que depende de otro se carga antes de digamos el "padre" ¿me podría dar errores de integridad referencial? ¿sería interesante deshabilitar la FK antes de empezar y volver a habilitarla al terminar?

    ¡Gracias!

    Salu2,


    José Mª Fueyo

    jueves, 27 de julio de 2017 8:36

Respuestas

Todas las respuestas

  • Chema,

    La sentencia BULK INSERT tiene la opcion WITH CHECK_CONSTRAINTS la cual si no es pecificada ignora las restricciones CHECK y FOREIGN KEY marcando estas como no confiables (not trusted) al terminar la carga.  Eso significa que podras hacer el chequeo de estas por codigo o DBCC CHECKCONSTRAINTS (Transact-SQL) y si todo esta ok entonces marcarlas como confiables usando ALTER TABLE.

    ALTER TABLE TuTabla WITH CHECK CHECK CONSTRAINT TuRestriccion;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    jueves, 27 de julio de 2017 12:41
  • Gracias Alejandro. Siempre al quite ;-)

    Una duda. Aunque voy a diseñar el orden de carga para que no se produzcan problemas de integridad referencial, ¿debería (como bien dices) marcar la tabla tratada como de confianza mediante la instrucción ALTER TABLE?

    ¡Gracias!

    Salu2,


    José Mª Fueyo

    viernes, 28 de julio de 2017 7:31
  • Chema,

    Correcto, si invocas BULK INSERT sin la opcion WITH CHECK_CONSTRAINTS entonces al final de la carga deberas validar y marcar cualquier restriccion CHECK y/o FOREIGN KEY como confiable.

    Dejar esas restricciones como desconfiables puede traer consecuencias en cuanto a desempenio.

    Ejemplo:

    Fijate como en el primer plan la tabla T1 es recorrida debido a que la restriccion de clave foranea esta marcada como no confiable.

    CREATE TABLE [1dc\f5ykcjh].T1 (col1 int NOT NULL PRIMARY KEY);
    GO
    CREATE TABLE [1dc\f5ykcjh].T2 (
    col1 int NOT NULL IDENTITY PRIMARY KEY, 
    col2 int NOT NULL CONSTRAINT FK_T2_T1 FOREIGN KEY (col2) REFERENCES [1dc\f5ykcjh].T1(col1),
    col3 char(10) NOT NULL DEFAULT ('AAAAAAAAAA')
    );
    GO
    INSERT INTO [1dc\f5ykcjh].T1(col1) VALUES (1), (2), (3);
    INSERT INTO [1dc\f5ykcjh].T2 WITH (TABLOCK) (col2)
    -- Itzik's VATN
    SELECT n % 3 FROM dbo.ufn_GetNums(1, 100000) WHERE n % 3 <> 0;
    
    SELECT * FROM [1dc\f5ykcjh].T1;
    SELECT * FROM [1dc\f5ykcjh].T2;
    GO
    SELECT * FROM sys.foreign_keys WHERE name = 'FK_T2_T1'
    GO
    ALTER TABLE T2 NOCHECK CONSTRAINT FK_T2_T1
    GO
    SELECT * FROM sys.foreign_keys WHERE name = 'FK_T2_T1'
    GO
    SELECT T2.* FROM [1dc\f5ykcjh].T2 INNER JOIN [1dc\f5ykcjh].T1 ON T1.col1 = T2.col2
    WHERE T2.col3 > 'B';
    GO
    ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1
    GO
    SELECT * FROM sys.foreign_keys WHERE name = 'FK_T2_T1'
    GO
    SELECT T2.* FROM [1dc\f5ykcjh].T2 INNER JOIN [1dc\f5ykcjh].T1 ON T1.col1 = T2.col2
    WHERE T2.col3 > 'B';
    GO
    DROP TABLE [1dc\f5ykcjh].T2, [1dc\f5ykcjh].T1;
    GO

    Ahora mira los planes despues de marcar la restriccion como no confiable y despues de marcarla como confiable.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    viernes, 28 de julio de 2017 13:18
  • Perfecto, Alejandro, muy ilustrativo.

    Una última duda. Respecto a las tablas con relaciones reflexivas ¿habría que verificar la integridad referencial a posteriori, mediante ALTER TABLE? el resto de tablas se podría verificar con el comando BULK INSERT, al estar diseñado el orden para que no existan problemas de integridad referencial. Pero el en éste tipo de tablas, como commit por lotes, ahí me entra duda de sí hacer la comprobación a posteriori. 

    Y entrando en temas de rendimiento ¿que sería mejor, configurar el BULK INSERT con la opción CHECK_CONSTRAINTS o hacerlo a posteriori con un ALTER TABLE? y en éste último caso, sí hay varias FK ¿es mejor validarlo (dentro del ALTER TABLE) cada FK independiente o todas a la vez mediante ALTER TABLE TuTabla WITH CHECK CHECK CONSTRAINT ALL;

    ¡Gracias!

    Salu2,


    José Mª Fueyo



    martes, 1 de agosto de 2017 11:57
  • Chema,

    Si te refieres a tablas con restricciones de integridad referencial apuntando a si misma, estas se tratan de la misma manera.

    Cual opcion sera mejor en cuanto a desempeño?

    Eso dependera de cuantas filas este insertando, pues la operacion de chequeo de integridad se hara por cada fila.  Ademas, que deseas hacer si la integridad falla?

    En caso de que eches hacia detras la transaccion entonces preferible se haga en cuanto detecte alguna, de lo contrario se hara al final.

    Otra solucion es volcar las filas hacia una tabla intermedia (staging) y chequear toda integridad antes de insiertar en la tabla final.  Este metodo se usa con ferecuencia en los Data Warehouses.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 1 de agosto de 2017 12:41
  • Hola Alejandro.

    Si, me refiero a tablas con restricciones de integridad referencial que apuntan a si mismas. Lo que me causa duda es en el caso de configurar carga por lotes (parámetro BATCHSIZE). En caso de error, ya tengo configurado un TRY...CATCH para notificarlo. Los archivos se cargan en una base digamos intermedia. Una vez verificado que todo ha ido bien, ser realiza la copia a una BBDD de producción.

    Gracias,


    José Mª Fueyo

    martes, 1 de agosto de 2017 12:56
  • Una vez que usas BULK INSERT sin la opcion CHECK_CONSTRAINTS entonces las restricciones CHECK y FOREIGN_KEY quedan desabilitadas.  No importa cuantos batches se inserten.  Lo mismo si se usa la opcion, no importa cuantos batches se hagan seguiran chequeando las restricciones.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 1 de agosto de 2017 14:24
  • Hola Alejandro, y gracias por todo

    A lo que me refiero, ¿cuando se verifican las restricciones de FK? ¿al finalizar el proceso de BULK INSERT (sea el número de lotes que lo componga)? ¿al terminar cada lote? si es esto último, quizás me interese verificar la integridad con un ALTER TABLE después del proceso. Si se verifica al final de la ejecución del comando BULK INSERT, pienso que entonces delegándolo al mismo es suficiente. Una cosa, la cantidad de registros puede ser bastante grande, y cada día se inicializaN la tablas

    Salu2


    José Mª Fueyo


    miércoles, 2 de agosto de 2017 6:55
  • Chema,

    Si chequeas la integridad en el comando (usando CHECK_CONSTRAINTS) entonces por cada fila insertada se buscara que exista una la fila referenciada en la tabla padre.  Si usas lotes, entonces cada lote que no contenga error sera grabado pero si existe error abortara el comando quedando grabado los lotes previous sin errores.

    Si no chequeas la integridad en el comando entonces lo tendras que hacer al final y por lo tanto tendras que buscar una solucion para aquellas filas que no cumplan la restriccion (elimnarlas o asignarles un padre por defecto).

    Cual solucion seria mejor en tu caso?

    La respuesta estara en el resultado de tus pruebas.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    miércoles, 2 de agosto de 2017 14:55
  • Perfecto, Alejandro. Creo que está todo claro

    Gracias, como siempre

    Un abrazo,


    José Mª Fueyo

    jueves, 3 de agosto de 2017 12:55