2

I need to import 10 billion rows into a database, daily.

SSIS, doesn't seem like its cutting it, and I'm starting to think that writing some kind of custom C/C++ code might be an option (I don't much know C/C++) to try and do it.

I'm working with a standard level production box, nothing fancy but decent. SQL 2012.

Any advice is welcome.

EDIT:

Source is a flat file. Say 20 columns. Not sure about size. Source data is on the same disk. (At the moment).

EDIT 2: While the columns are largely int/bigint, there are 5-7 VARCHAR(x) (the largest is VARCHAR(1000)).

The problem im finding with SSIS is that the read/write speeds are not what I'm hoping for. I've tried fastParse, etc, and various other techniques.

Other than tips, I'm hoping for techniques. Tips still welcome obviously.

Improve this question
14
  • 1
    So, we need more info like: What is the source data? Where is the source data? How wide is the source data?
    – JNK
    Apr 4, 2014 at 20:17
  • 1
    While 10 billion rows is not too shabby, it won't be the same to import 10 billion ints or 10 billion guids + char(200). Can you, please, provide a schema of your table(s)? PS: What about SSIS doesn't seem to make it worth to try? Others would beg to differ and that some years ago..
    – Marian
    Apr 4, 2014 at 20:20
  • 1
    Take a look at this We Loaded 1TB in 30 Minutes with SSIS, and So Can You. Perhaps you'll reconsider SSIS!
    – DenisT
    Apr 4, 2014 at 20:22
  • 2
    Since you "don't much know C/C++", how good are the chances that your solution will be faster than SSIS, developed by people who, I should assume, know C/C++ and SQL Server APIs better?
    – mustaccio
    Apr 4, 2014 at 20:31
  • 2
    Can you add to your question what your targets are please. Do you have 24 hours ("daily") to process your data or do you have a 1 hour ETL window each day. How fast do you expect/desire a 3TB-ish load to complete? What are you going to do with 1.1 petabytes per year of data?
    – Mark Storey-Smith
    Apr 4, 2014 at 22:25

1 Answer 1

Reset to default
6

Look into BCP or BULK INSERT, both of which can share format files.

Quick math based on pure guesses, since no hard details are given:

13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes.

5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = 1500 bytes

1500 + 78 = 1578 bytes per row average.

1578*10000000000/(1024*1024) = 15048981 Megabytes per day

15048981/(24*60*60) = 174MB/s EACH WAY (once from the source and once to the database) if you load it continuously, 24 hours a day, rain or shine, during index maintenance, during backups, during DBCC CHECKDB and OS level disk defragmentation.

If you have 7 VARCHAR fields at an average of 800 bytes each, that's 40 times the bandwidth requirements of 5 VARCHAR fields at an average of 20 bytes each; the latter can be done over gigabit ethernet, the former cannot given your 10 billion rows a day, every day criteria.

Figure out what your data sizes actually are, and what your time windows actually are, and then do the math on how fast you need it to go for real.

If you have to, you can try disabling or removing triggers, traces, constraints, foreign keys, indexes, etc. on whatever table you're putting this data into.

And watch out for backup space requirements as well :).

Improve this answer
4
  • The varchars would only be that big if they were free text. I can't imagine anyone keying 10 billion free text fields into anything on a daily basis, so they're much more likely to be alphabetic or alphanumeric codes, and probably only a few bytes long each.
    – ConcernedOfTunbridgeWells
    Apr 5, 2014 at 19:54
  • 2
    You might also want to run multiple bulk insert tasks in parallel. Scalzo's The Oracle DBA guide to data warehousing and star schemas discusses a technique for doing this on Oracle and its bulk load tool (SQL*Loader). You could do something similar with a .net wrapper that can spawn multiple processes.
    – ConcernedOfTunbridgeWells
    Apr 5, 2014 at 19:59
  • @ConcernedOfTunbridgeWells, they could also be summary or log-like snippets, i.e. "Row came from $Personname, processed on $systemname. Check 1 passed. Check 2 passed. Check 3 passed...". Yes, I've actually seen things like that - you'd be amazed at what kinds of denormalized data people want to store. Alternately, the VARCHAR fields are storing actual reports, perhaps comma delimited lists of data, or mainframe style fixed width, human-readable output. Until the original question is updated, there's no way to tell, but we do know that there is at least one VARCHAR(1000) field.
    – Anti-weakpasswords
    Apr 5, 2014 at 19:59
  • in that case you might want to pre-process the field, parse out the data you want and punt the fluff. This could be done off-line before loading it into the database.
    – ConcernedOfTunbridgeWells
    Apr 5, 2014 at 21:34

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