What's more efficient? Manually inserting 1 million rows or SSIS?
Posted on 2011-03-23
Let's say we have 1 million rows worth of data in a C# program we need to get into the database. Roughly, is it more efficient to do 1 million inserts in a loop or to write the data to a flat text file (.csv) on disk and import using SSIS?
The target database table has a few indexes on it and we're finding that the indexing is taking a significant amount of time.
We think this is a "pay now or pay later" situation. I would guess that there are economies of scale that come into play that make the SSIS overall much more efficient, even though the net indexing is the same either way.
Any guesses on if doing SSIS is 10% more efficient? 50% more efficient?