5

I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.

I split 150 mln row into 3 BCP Export files each of 50 million rows and 3 BULK INSERTS. It worked (15 min BCP out/ 40 min Bulk Insert), but I would rather export just one file and import as well one file.

Is it technically possible or I can have issues for the big amount of rows? I never Imported more than 50 mln rows from a BCP file and I am not sure how this can work out. Is there a limit for BCP export or Import?

Thanks,

Improve this question
4
  • How large were each of the exported files?
    – jcolebrand ♦
    Commented Feb 17, 2011 at 16:35
  • 50 mill or 150 mill per file is not going to do make any difference in my opinion. The difference will be with how Thomas is saying, having files on different plates, and make writing/reading test in parallel.
    – Marian
    Commented Feb 17, 2011 at 20:54
  • @jcolebrand - the files were around 10 GB each.
    – yrushka
    Commented Feb 18, 2011 at 7:58
  • then having 3 files vs 1 shouldn't affect the OS, as was my initial curiosity.
    – jcolebrand ♦
    Commented Feb 18, 2011 at 15:08

2 Answers 2

Reset to default
3

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Improve this answer
1
  • Thanks I will try it today and let you know about the results.
    – yrushka
    Commented Feb 18, 2011 at 7:56
3

If you make sure that your filesystems are on striped storage. That way you can prevent the io bottleneck. Just specifying different location without knowing what kind of storage they are on, does not make much sense. This workload typically has large scans and large writes so striping will certainly help you.

Ronald.

Improve this answer
1
  • yes I have RAID 10 disks but I am not really concerned about performance as this is done in a test server with low activity. Thanks for advice though.
    – yrushka
    Commented Feb 18, 2011 at 19:24

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.