Enhance Performance for Bulk Inserts in SQL SERVER

Enhance Performance for Bulk Inserts: 

TABLOCK Hint:

Use the TABLOCK hint to lock the entire table during the bulk insert operation.

This minimizes logging and improves performance by reducing lock contention.

 

Simple Recovery Model:

Switch the database to Simple Recovery Mode to reduce transaction log overhead.

This helps enhance performance by minimizing logging during large operations.

ALTER DATABASE ShoppingDatabase

SET RECOVERY SIMPLE;

 

Disable Indexes:

Temporarily disable non-clustered indexes to prevent index updates during the bulk insert.

This reduces overhead associated with maintaining indexes during the insert operation.

 

Batch Processing (MAXERRORS):

Use MAXERRORS = 100 to allow up to 100 errors before the process fails.

This provides tolerance for occasional errors without aborting the entire bulk insert operation.

 

Disable Foreign Keys, Constraints, and Triggers:

Temporarily disable foreign keys, check constraints, and triggers to avoid performance overhead during bulk inserts.

Example command: ALTER TABLE Orders NOCHECK CONSTRAINT ALL;

 

Wrap Operations in a Transaction(BEGIN TRANSACTION, COMMIT, and ROLLBACK statements):

Wrap bulk insert operations in a transaction to ensure data consistency.

This ensures either all rows are inserted or none are, maintaining integrity.

 

 

Use Staging Tables and MERGE:

Insert data into temporary staging tables first.

Move data into target tables using efficient methods like MERGE to reduce the load on primary tables and improve performance.

 

 

Example:

CREATE PROCEDURE BulkInsertOrdersAndDetails

    @OrdersFilePath NVARCHAR(255),

    @OrderDetailsFilePath NVARCHAR(255)

AS

BEGIN

    BEGIN TRANSACTION;

    BEGIN TRY

 

        -- Disable foreign keys, constraints, and triggers temporarily (optional)

        ALTER TABLE Orders NOCHECK CONSTRAINT ALL;

        ALTER TABLE OrderDetails NOCHECK CONSTRAINT ALL;

        

        -- Disable indexes

        ALTER INDEX ALL ON Orders DISABLE;

        ALTER INDEX ALL ON OrderDetails DISABLE;

        

        BULK INSERT Orders

        FROM @OrdersFilePath

        WITH (

            FIELDTERMINATOR = ',',

            ROWTERMINATOR = '\n',

            FIRSTROW = 2,

            TABLOCK,

            MAXERRORS = 1000

        );

 

        BULK INSERT OrderDetails

        FROM @OrderDetailsFilePath

        WITH (

            FIELDTERMINATOR = ',',

            ROWTERMINATOR = '\n',

            FIRSTROW = 2,

            TABLOCK,

            MAXERRORS = 1000

        );

 

        COMMIT TRANSACTION;

        

        -- Re-enable foreign keys, constraints, and triggers

        ALTER TABLE Orders CHECK CONSTRAINT ALL;

        ALTER TABLE OrderDetails CHECK CONSTRAINT ALL;

        

        -- Rebuild indexes

        ALTER INDEX ALL ON Orders REBUILD;

        ALTER INDEX ALL ON OrderDetails REBUILD;

 

    END TRY

    BEGIN CATCH

        ROLLBACK TRANSACTION;

        PRINT 'Error occurred : ' + ERROR_MESSAGE();

    END CATCH

END;

 

Conclusion:

By applying these strategies, we can significantly improve the performance of bulk data uploads in SQL Server while minimizing downtime and ensuring data integrity.

Comments

Popular posts from this blog

Multiline to singleline IN C# - CODING

EF Core interview questions for beginners

EF Core interview questions for experienced