none
TABLE LOCK & CHECK CONSTRAINTS in OLEDB Destiation

    Question

  • Hi Friends,

    I am migrating data from Source to Destination Server using SSIS Packages. I am implementing Transaction in my package. I am using the fast load option to load the data to the tables in Destination using OLEDB Connection Manager.

    This option has TABLE LOCK and CHECK CONSTRAINTS options checked by default. I would like to know if it would be safe for me uncheck the TABLE LOCK option since I am using Transaction in the package. Would this impact the package or the data load process in any ways.

    Few of the tables used as destination do not have any constraints. Would it make any drastic difference if I uncheck CHECK CONSTRAINTS option for the tables without any constraints.

    Any help would be appreciated.


    Murali Krishnan

    Tuesday, January 08, 2013 12:01 PM

Answers

  • Hi,

    Tablock make difference in how table will be accessed. If you uncheck tablock it will lock only blocks smaller than table (probably data pages) which can cause slower writing.

    Otherwise "Check Contraints" to off can cause faster writing but can cause data inconsistency simillary to nocheck option in TSQL.

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:21 AM
    Tuesday, January 08, 2013 1:14 PM
  • Would not recommend removing Check Constraints.  This will ignore any foreign key relationships and will open the possibility of integrity mistakes.

    Tablock checked I have played with back and forth.  Sometimes I have to disable it because it will cause blocking and cause deadlocks-especially in the case when you are running a parallel update statement(logical split in dataflow) while a destination compponent is performing an insert.  It should not be necessary if you are using transactions-but as Zdenek pointed out, it is a bit slower.

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:21 AM
    Tuesday, January 08, 2013 4:56 PM

All replies

  • Hi,

    Tablock make difference in how table will be accessed. If you uncheck tablock it will lock only blocks smaller than table (probably data pages) which can cause slower writing.

    Otherwise "Check Contraints" to off can cause faster writing but can cause data inconsistency simillary to nocheck option in TSQL.

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:21 AM
    Tuesday, January 08, 2013 1:14 PM
  • Would not recommend removing Check Constraints.  This will ignore any foreign key relationships and will open the possibility of integrity mistakes.

    Tablock checked I have played with back and forth.  Sometimes I have to disable it because it will cause blocking and cause deadlocks-especially in the case when you are running a parallel update statement(logical split in dataflow) while a destination compponent is performing an insert.  It should not be necessary if you are using transactions-but as Zdenek pointed out, it is a bit slower.

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 7:21 AM
    Tuesday, January 08, 2013 4:56 PM