AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sqlbulk copy log1/6/2024 The data source doesn’t have to be a DataTable either. use the TableLock option to maximise throughput.If you add indexes at the start, you will just unnecessarily hinder the insert speed add suitable indexes on to the staging table after the data has been bulk loaded, that will help with subsequent processing/querying.bulk loading into a new, empty table (staging table).Usually in my experience, this is not an issue. Of course, this lock can block out other processes until the loading is complete. SqlBulkCopy with TableLock Option: 0.8229s This further reduces the time to load 100,000 records: By default, it would otherwise obtain row level locks. This will obtain a bulk update lock on the destination table during the bulk copy process. SqlBulkCopy bulkCopy = new SqlBulkCopy ( connection, SqlBulkCopyOptions. For maximum performance, specify the TableLock option: One of the SqlBulkCopy constructor overloads provides the ability to specify SqlBulkCopyOptions. That’s about a 93% drop in time taken when using the SqlBulkCopy class over SqlDataAdapter approach. the times to complete bulkCopy.WriteToServer and insertAdapter.Update respectively): The times recorded are average measurements of the actual time to persist the data (i.e. FieldA is just a GUID, FieldB is an incrementing number. I populated the DataTable with 100,000 records containing some random data. If they didn’t match, then when using SqlBulkCopy, you need to map which columns in the DataTable map to which columns in the destination table using the ColumnMappings method on the SqlBulkCopy instance. The columns in myDataTable match those in the table which keeps it nice and simple. Plus I haven’t shown the generation of the DataTable ( myDataTable) as that’s not particularly exciting either.įor this test, I’m loading 2 columns of data to the table named “BulkLoadTable” in my database. I’ve left out the boring stuff like the instantiation and opening of the SqlConnection ( connection). Using ( SqlCommand insertCommand = new SqlCommand ( "INSERT BulkLoadTable(FieldA, FieldB) VALUES, connection ))
0 Comments
Read More
Leave a Reply. |