-1

I want to create 900.000 new rows in a database and in each of these rows the entry from the array should be written into the column Products.

My solution mentioned below works but it takes over an hour. How can this be done faster?

My very slow solution:

for (int i = 1; i <= 900000; i++) 
{
    string SQL_command = "INSERT INTO Database(Produkte) VALUES(" + ProductArray[i] + ")";

    SqlConnection con = new SqlConnection(connString);
    con.Open();

    SqlCommand cmd = new SqlCommand(SQL_command , con);
    cmd.ExecuteNonQuery();              

    con.Close();
}

I use SQL Server and C#

Dale K
  • 25,246
  • 15
  • 42
  • 71
Sebrahi
  • 39
  • 2
  • 2
    Look into [Table Valued Parameters](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15). – Dale K Dec 30 '20 at 19:49
  • 1
    https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code ... it talks about stored procedures but you can use `CommandType.Text` as well. – madreflection Dec 30 '20 at 19:50
  • Besides TVPs, another method is [SqlBulkCopy](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-5.0). If you must do singleton inserts, use a parameterized statement and batch all in a single transaction. – Dan Guzman Dec 30 '20 at 19:53
  • 3
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) – marc_s Dec 30 '20 at 19:54
  • You can use a loop to build an sql string with a thousand parameter placeholders in, `INSERT x VALUES (@p0), (@p1), ... (@p999)`, and put a thousand parameters in the command, then run a loop that sets a thousand parameter values at a time and call execute 900 times instead of 900000 and times. Once you have the code done, see if doing 5000, 10000 at a time helps.. not sure I'd go for Dan's "do all 900000 in one hit" but he's a very smart guy so it could work out well! – Caius Jard Dec 30 '20 at 19:56

1 Answers1

-3

Instead of opening and closing your connection with each call, open the connection once, then run all the INSERT statements, then close the connection:

using (SqlConnection con = new SqlConnection(connString))
{
    con.Open();
    for (i = 1; i <= 900000; i++) {
        string SQL_command = "INSERT INTO Database(Produkte) VALUES(" + ProductArray[i] + ")";
        using (SqlCommand cmd = new SqlCommand(SQL_command , con))
        {
            cmd.ExecuteNonQuery();
        }
    }
}
Russ
  • 4,091
  • 21
  • 32
  • But with connection pooling, the connection will not be closed (though it will be reset). Perhaps some slight benefit but the agony here is inserting 1 row at a time. – SMor Dec 30 '20 at 20:11