How to do faster insert query in c#?

Question

I would like to insert all the id's in a sql table. The following way works but this take very long. What is the best or better way to do this to increase the speed.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
    string query = "";
    foreach (var id in ids) // count = 60000
    {
        {
            query += "INSERT INTO [table] (id) VALUES (" + id + ");";
        }
    }

    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        reader.Close();
    }
    connection.Close();
}

marc_s · Accepted Answer · 2015-07-30 10:57:55Z

5

You can use the SqlBulkCopy to insert large amounts of data - something like this:

// define a DataTable with the columns of your target table
DataTable tblToInsert = new DataTable();
tblToInsert.Columns.Add(new DataColumn("SomeValue", typeof (int)));

// insert your data into that DataTable
for (int index = 0; index < 60000; index++)
{
    DataRow row = tblToInsert.NewRow();
    row["SomeValue"] = index;
    tblToInsert.Rows.Add(row);
}

// set up your SQL connection     
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
    // define your SqlBulkCopy
    SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);

    // give it the name of the destination table WHICH MUST EXIST!
    bulkCopy.DestinationTableName = "BulkTestTable";

    // measure time needed
    Stopwatch sw = new Stopwatch();
    sw.Start();

    // open connection, bulk insert, close connection
    connection.Open();
    bulkCopy.WriteToServer(tblToInsert);
    connection.Close();

    // stop time measurement
    sw.Stop();
    long milliseconds = sw.ElapsedMilliseconds;
}

On my system (PC, 32GB RAM, SQL Server 2014) I get those 60'000 rows inserted in 135 - 185 milliseconds.

edited Jul 30, 2015 at 10:57

answered Jul 30, 2015 at 10:20

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick.Mc Over a year ago

The only person in the entire thread that provided metrics :)

Chris Schubert · Accepted Answer · 2015-07-30 10:45:38Z

Consider Table-Valued Parameters. They are an easy way to send a batch of data into a stored procedure that will then handle them on the SQL side, and they aren't restricted in most of the other approaches you will see are (insert limits, etc).

In the database create a custom Type that has the schema of your table.
```
CREATE TYPE dbo.TableType AS TABLE
( ID int )
```
Create a DataTable that matches your table schema (including column name and order).
```
DataTable newTableRecords = new DataTable();
// Insert your records, etc.
```

Create a stored procedure that receives a table parameter, and inserts the records from that parameter into your real table.

CREATE PROCEDURE usp_InsertTableRecords
(@tvpNewTableRecords dbo.TableType READONLY)
AS
BEGIN
    INSERT INTO dbo.Table(ID)
    SELECT tvp.ID FROM @tvpNewTableRecords AS tvp;
END

Call the procedure from your application code, passing in your data table as a parameter.

using (connection)
{

    // Configure the SqlCommand and SqlParameter.
    SqlCommand insertCommand = new SqlCommand(
        "usp_InsertTableRecords", connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
        "@tvpNewTableRecords", newTableRecords);
    tvpParam.SqlDbType = SqlDbType.Structured;

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

I've had really great performance at very large volumes with this approach, and it is nice because it allows everything to be set-based without any arbitrary insert limits like the INSERT INTO (Table) VALUES (1),(2),(3)... approach.

Collectives™ on Stack Overflow

How to do faster insert query in c#?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related