?
Solved

What's more efficient?  Manually inserting 1 million rows or SSIS?

Posted on 2011-03-23
5
Medium Priority
?
829 Views
Last Modified: 2012-05-11
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?

Comments?
0
Comment
Question by:ZuZuPetals
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 668 total points
ID: 35199468
if there is no data in the table already, then it is more effecient to do a bulk insert statement (which can be done either way).

If users are using the table, then smaller inserts of records to prevent locking the table.
0
 
LVL 5

Assisted Solution

by:idmedellin
idmedellin earned 664 total points
ID: 35199488
I suggest you to use insert sentences, but before, alter the indexes in the table defining the fill factor to about 70%.

The porcentage of efficiency depends from the structure of the table and indexes (number of fields, types, number of indexes, etc.)
0
 
LVL 2

Assisted Solution

by:KentMarsh
KentMarsh earned 668 total points
ID: 35207426
My $.02

1 BCP is far more efficient than 1 Million calls between the .NET application and the dbms.

MOST Efficient way I know:
1. Turn off access to the table from End Users
2. Remove all Indexes  (if possible)
3. Use a BCP process to add data
4. Restore indexes
5. Turn access back on.

There are other strategies that you may consider depending on your constraints. If the table has insert triggers then that will cause more complications and slow downs. If the data is organized in a particular fashion there are ways of adding Blocks of data at a time - say your data is all for the most recent date and all of the existing data is for previous dates.

0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 35207521
My other $.02
SSIS was specifically designed to be an efficient ETL solution.
Doing 1 million C# inserts from a .NET application will in no way be faster than importing a CSV file into SSIS.
SSIS will also give you a lot of powerful features that are easier to implement than rolling your own in a C# program.
0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 35207554
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question