CODEWITHSUNDEEP
c#.netsql-serversqlbulkcopy
Lalitya
Lalitya

Reputation: 729

SQL BulkCopy vs OPENDATASOURCE which one is faster

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.

Upvotes: 2

Views: 579

Answers (2)

Rajeev Bera
Rajeev Bera

Reputation: 2019

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

Upvotes: 1

Ehsan
Ehsan

Reputation: 32681

have you tried importing data to sql database from access file? Database->Tasks->Import, Microsoft Access as data source, mdb file as parameter

If you want to do this via C# then you should consider using User Defined Table Types with stored procedures.

Upvotes: 0

Related Questions