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?

techresearch7777777 1,981 Reputation points
2022-09-21T16:47:04.947+00:00

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.

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
    2022-09-21T21:05:09.107+00:00

    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.

    0 comments No comments

7 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,771 Reputation points
    2022-09-21T17:17:37.317+00:00

    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

    0 comments No comments

  2. techresearch7777777 1,981 Reputation points
    2022-09-21T17:44:19.58+00:00

    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.)

    0 comments No comments

  3. Tom Phillips 17,771 Reputation points
    2022-09-21T20:37:48.077+00:00
    0 comments No comments

  4. techresearch7777777 1,981 Reputation points
    2022-09-21T21:34:48.16+00:00

    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?


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.