SQL Server 2014 is there any ways to improve bulk insert or bcp for large data sets from flat files or other methods that are faster?

Question

Hello, for SQL Server 2014 is there any ways to improve bulk insert or bcp for large data sets from flat files or other methods that are faster?

Thanks in advance.

Answer 1

Which is faster bulk insert or bcp and why?

I think you would need to run a benchmark to find out. I would expect performance to be about the same, but BULK INSERT uses OLE DB and BCP uses ODBC. And BULK INSERT is inside the engine, and BCP is a separate program. But it is also going to matter where the files are. What you should not to is to have the files on one machine, SQL Server on a second machine and run BCP from a third machine. But apart from that I would not make any guesses.

Simple or bulk-logged recovery should not matter that much. The same rules for minimally logged operations apply. TF 610 applies to plain INSERT statements in clustered indexes as I recall. With BCP/BULK INSERT you get minimally logged operations, as long as you use a hint to enforce tablock, as I recall.

Answer 2

You would need to look at the wait states and determine where the bottleneck is.

It is always a good idea to set a batch size on large data sets.
https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16#b

Answer 3

Thanks TomPhillips-1744 for your reply.

Which is faster bulk insert or bcp and why?

Is it correct to say that putting the DB into SIMPLE Recovery Model would be better than Bulk-logged since SIMPLE would do the less logging of Transactions when bulk loading?

Enabling Trace flag 610...can this be used with either bulk insert or bcp?
(I came across that using TABLOCK hint helps increases performance for both bulk insert or bcp.)

Answer 4

I suggest you take a look at this:

https://www.databasejournal.com/ms-sql/sql-server-bulk-copy-optimization-tips/
https://www.mssqltips.com/sqlservertip/3434/options-to-improve-sql-server-bulk-load-performance/

Answer 5

Thanks ErlandSommarskog for your reply as well.

Realizing the Target table has Indexes is another factor to consider...would the INSERT BULK or bcp load be better to DROP or DISABLE the Indexes on Target tabe?