Post reply

SQL Insert performance tuning

  • January 12, 2009 at 12:16 pm

    #925472

    Hi,

    As a starter ... I think I will try the SQlBulkCopy class ... since writing the SSIS package etc. and figure it all out will take time ... but for sure something I will look into.

    I will post any new stuff that I find, as the programmering of the new stuff goes on.

    Thanks for the answers until now.

    mvh

  • October 8, 2011 at 9:13 pm

    #1392152

    Hi there,

    I notice this thread is ages old, but just to add some additional hints for future readers:

    - Increase ADO.NET BatchSize

    - Choose the target table's clustered index wisely, so that inserts won't lead to clustered index node splits (e.g. autoinc column)

    - Insert into a temporary heap table first, then issue one big "insert-by-select" statement to push all that staging table data into the actual target table

    - Apply SqlBulkCopy

    - Choose "Bulk Logged" recovery model instad of "Full" recovery model

    - Place a table lock before inserting (if your business scenario allows for it)

    Taken from http://arnosoftwaredev.blogspot.com/2011/10/tips-for-lightning-fast-insert.html

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply