A while back, I was asked which of the following two bulk insert options would be faster:
- Copy/Bulk Insert: First copy the data file from server B to server A, and then bulk insert the data into a table on server A
- Bulk Insert across Network: Directly bulk insert the data from server B into the table on server A
Intuitively, the first option--Copy/Bulk Insert--would be slower than the second option--Bulk Insert across Network, because the Copy/Bulk Insert option would write the data to disk twice, first for the copy operation and the second time for the bulk insert operation, and read the data file from disk twice, first from server B and then from server A.
So the question is really not which one is faster, but how much faster the second option may be. To get a feel for the difference, I conducted several tests, and I thought some of you might be interested in the results.
In these tests, I used two data files--orders.txt and stock.txt, representing a relatively small data file and a relatively large data file, respectively. Their sizes and row counts are as follows:
- orders.txt: size ~= 130MB and row count = 3,000,000
- stock.txt: size ~= 3GB and row count = 10,000,000
The schema of the two tables are as follows:
CREATE TABLE orders
CREATE TABLE stock
For the tests, I ran the attached batch files multiple times. One batch file was for loading orders data, and the other for loading stock data. Each batch file performed Copy/Bulk Insert once, followed by Bulk Insert across Network once. The table below shows the average results:
(smaller data file)
(larger data file)
|Bulk Insert across Network
So in this particular test environment, for the orders data file, the Bulk Insert across Network option is ~24% faster than the Copy/Bulk Insert option, and for the stock data file, the Bulk Insert across Network option is ~35% faster than the Copy/Bulk Insert option.
The performance difference between the two options is obviously more pronounced for the larger data file. But I wouldn't want to over-generalize this observation, because a number of factors may influence the results, and these factors may vary from environment to environment. For instance, two key factors are (1) the performance of the drive to which the data file is copied, and (2) whether any database file resides on this same drive. Nevertheless, it is safe to say that the performance difference between the two bulk insert options can be quite significant.
One argument in favor of the Copy/Bulk Insert option that I sometimes hear is that it's safer or more robust to copy the data file to a local disk first as the exposure on the network is shorter. I don't think this is a compelling argument because network connectivity these days is rather reliable, and if it isn't, you've got a bigger problem.
The other argument in favor of the Copy/Bulk Insert option is a permission issue. This sometimes is more compelling because you may be restricted as to what security privileges you can assign to an account.