Question
I want to insert data into sqlserver from access .mdb file, which is the fastest way to perform the data inserts SQL BulkCopy or OPENDATASOURCE using OLEDB.
Data would be like 100,000s of records for about 20 tables.
Thanks.
I am doing this from c# code,where i need which one performs better,I am not able to compare because OPENDATASOURCE is asking for admin permissions.
Popular Answer
In my Opinion the very best insert performance for inserting large volumes of data can be attained by using SQL Bulk Copy as this method bypasses the SQL query language entirely and streams data directly to SQL.
Further Performance Improvements
- By defaults SqlBulkCopy uses row locks during the bulk copy operation. If TableLock option in the SqlBulkCopyOptions is specified, it obtains a bulk update lock for the duration of the bulk copy operation and performance will be improved.
- Clustered Table: if it is feasible, the best way to load it is to remove the clustered index.
- Indexes: Indexes create huge problems with the parallelism so is it always a good idea to load without any indexes active
You may read more on Whitepaper: performance of SqlBulkCopy http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why