Kev Wisbey-443237 wrote:
I am tasked with writing a package to import a series of files. One of the files is 7.4gb and has 229 columns (look I didn't design this). From previous experience I suspect this file will take ages to import. My thought is to split the file into smaller chunks or partition it. I would appreciate some ideas on how to improve the performance.
Do you know about when and how to make "Minimally Logged" imports happen and why they'll usually make your imports more than twice as fast without going through all the "chunking" strokes you're getting ready to go through? And, don't say "Yes" just because you may have heard the term... do you actually know the requirements for "Minimally Logging" and have ever had code that met the requirements for "Minimal Logging" and successfully executed?
One string hint here is that just being in the SIMPLE Recovery Model does NOT guarantee "Minimal Logging" and importing "chunks" to the same table means that only the first "chunk" CAN be (there are other things that must happen to be "Minimally Logged") if the direct target is a single table. Partitioning and using "SWITCH" can certainly help there (especially if you "GO Parallel" to multiple "switch" tables) but you still haven't determined if you're actually correct about how long the import will take.
You've also not mentioned the automatic sequestration of bad rows for repair/reimport, etc, etc.
If I were doing this, Step 1 would be to make sure I could do it in a "Minimally Logged" fashion and then see how much time and how many resources it actually takes. Like they say, one good test is worth a thousand expert opinions... even if if the expert is yourself. Like I tell folks, the best thing history will tell you is that you need to test again. 😀
Also, do you already have a target table setup? If so, using a BCP Format file will also improve performance even if you're using SSIS. If the target table already exists, it's pretty easy to generate one instead of trying to create one by hand (which is also not as difficult as many would have you believe).