Import 100 million rows to MS SQL using C#

Asked Apr 18 '17 at 08:23

Active Feb 12 '19 at 10:11

Viewed 266 times

I'm trying to make a service that will sometimes import 100 million rows from csv file to database.

My code works very slow (0.5 seconds for every request) and I have to make many requests.

I have two columns, they are both a primary keys. Therefore it can be so slow because of indexing. But if I make an import using Microsoft SQL Server Management Studio (import wizard) - it works very fast.

Is there a way to make it faster or with fewer requests?

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    long i = 0;
    string result = "";
    StringBuilder sb = new StringBuilder();

    while (!sr.EndOfStream)
    {
        string s = sr.ReadLine();
        string[] data = s.Split(',');

        sb.Append(string.Format("('{0}',{1}),", data[0], data[1]));

        i++;
        if (i % 1000 == 0)
        {
            result = sb.ToString();

            string queryString = "USE[PassportExpiredTestBase]\r\n" +
                                 "INSERT INTO[dbo].[PassportList]([Series],[Number])VALUES" +
                                 result.Substring(0, result.Length - 1);

            SqlCommand command = new SqlCommand(queryString, connection);
            command.ExecuteNonQuery(); // The most expensive operation

            sb.Clear();
        }
    }

    connection.Close();
}

edited Feb 12 '19 at 10:11

radbyx

9,352
21
84
127

asked Apr 18 '17 at 08:23

Dennis Meissel

1,825
1
21
33

4

You can do bulk inserts i.e. 1k at a time or something like that. – Satish Prakash Garg Apr 18 '17 at 08:24
1

why dont you use ssms import wzard ? it is much faster than c# – Arash Apr 18 '17 at 12:38
@Arash, I can't because I have to do it every day at night – Dennis Meissel Apr 18 '17 at 14:28
@SatishGarg, thank you, I've made with Bulk Insert and DataSet and it works great! – Dennis Meissel Apr 18 '17 at 14:30
2

Well , In this case i Would Design Data Tranfrom Package Using SSIS And Then Use Sql Agent To Run it within Specific Interval. – Arash Apr 19 '17 at 06:13
1

@Arash thanks! It's the best way! – Dennis Meissel Apr 19 '17 at 09:42
1

would you so please up vote my comment . thanks – Arash Apr 19 '17 at 10:38
@Arash unfortunately I can't do it because of reputation :( – Dennis Meissel Apr 19 '17 at 13:34

Import 100 million rows to MS SQL using C#

0 Answers0