0

We've got a process that's importing a 7gig / 334 million row data file. The data file is sorted on the first column

The process is built on the "import first, index later" methodology. It uses BCP.

As written, it takes about 23 minutes to load the data, and about 45 minutes to create 2 indexes on the 334 million rows.

I tried the following things to speed things up: Add TABLOCK hint to bcp - cuts the load to 15 minutes Use BULK INSERT w/ TABLOCK hint - cuts the load to 10 minutes.

All the above are with a batch size of 5000

The thing that puzzled me is that I tried to leverage the sorted order of the input to get some of the work done up front. I added a clustered primary key on the file sort order and added an ORDER() hint on both the bcp and BULK INSERT loads - and I got only minimal to no gains.

When I was building that index separately it was about 22.5 minutes.

Adding the ORDER() hint with a clustered primary key declared at the beginning just moved that 22.5 minutes to the load instead of an after step.

Shouldn't the sorted nature of the file have produced some more efficiencies?

EDIT: I added trace flag 610 the BULK INSERT statement after noticing the line in the performance guide about BATCHSIZE != 0 would only be minimally logged for the first batch.

Loading with a clustered primary key to an empty table with BATCH=5000,TABLOCK,ORDER(1) and trace flag 610 took 19 minutes, compared to (10 + 22.5) for loading and indexing separately.

So that was a nice improvement but not "similar performance" as the performance guide indicated.

Improve this question
3
  • Thanks for responding. I did find this: "Similar performance was observed inserting into a clustered index and heap in this case. Note that unless you enable trace flag 610, the only way such an operation will be minimally logged is if the target table is empty and you specify a BATCHSIZE = 0. For a nonzero BATCHSIZE, only the first batch (commit) will be minimally logged." I did not observe similar load times but the next sentence about batchsize must be 0 might be it. It makes me a little nervous to say BATCHSIZE=0; wouldn't that blow up the transaction log for 334 million rows?
    – user1664043
    Commented May 6, 2020 at 15:08
  • Not if it is being minimally logged :)
    – LowlyDBA - John M
    Commented May 6, 2020 at 15:09
  • Your batch size is still pretty small - I'd try at least 50k-100k as your frequent commits may be hampering you still.
    – LowlyDBA - John M
    Commented May 6, 2020 at 17:40

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.