一些想法：

1. 在整个批处理插入期间保持相同的连接打开。

   
   



2. 不要重新创建 SqlCommand，请在开始时将其打开。

   
   
s在每个循环迭代期间。首先创建一次，然后只更新参数的值： cmd.Parameters [ @ x]。Value =…; 。



3. 您正在通过 foreach 循环（插入单个记录）将其插入第二个表（B）。您可以考虑用单个插入到TableB中来替换它（x，y，z）SELECT x，y，z FROM @tvp ，其中 @ tvp 是表值参数 。本质上，这意味着您可以填充例如 DataTable ，其中要插入第二个表中的行，然后将该 DataTable 传递为 @tvp 。从SQL Server 2008开始，IIRC支持TVP。设置其中一个设置需要进行一些首次研究。

   
   

   （我不确定上面的 INSERT 语句是否会真正起作用，或者TVP是否仅充当存储过程的参数（请参见例如本示例）。

   $ b＃b



4. 要比＃3进一步，将插入到表A和B中的内容移动到DB存储过程中。该SP将具有进入表A的值作为参数，以及具有进入表B的记录的表值参数。

   
   

I am completely clueless with this problem, Any help would be highly appreciated:

I have two tables, one is the master data table (Table A), the other table (Table B) has a foreign key relationship with multiple entries (to be specific 18) for one entry in Table A.

I am getting the data in a list and wish to insert it in SQL Server database.

I am currently using the below pattern but is taking 14 minutes for inserting 100 rows in Table A and corresponding 18*100 rows in Table B.

using (SqlConnection conn = new SqlConnection(conStr))
{
    foreach (var ticket in Tickets)
    {
        sql = string.Format(@"INSERT INTO dbo.Tickets([ColumnA], [ColumnB] ,..." + @")
                              VALUES(@ColumnA, @ColumnB,@ColumnC, @ColumnD, .... +
                            @"SELECT SCOPE_IDENTITY();");

        using (cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@ColumnA", (object)ticket.Id ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ColumnB", (object)ticket.Address ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ColumnC", (object)ticket.Status?? DBNull.Value);
            ....

            conn.Open();
            TableA_TicketId = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
} 

I use SCOPE_IDENTITY() to get the latest identity from table A for each record inserted and use it for insertion in second table

sql = string.Format(@"INSERT INTO Tickets_Fields ([TableA_TicketId], [FieldName], [Key],[Value]) 
                      VALUES (@TableA_TicketId, @FieldName, @Key, @Value);");

using (cmd = new SqlCommand(sql, conn))
{
    foreach (var customField in ticket.CustomFields)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@TableA_TicketId", (object)TicketId ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@FieldName", (object)"CustomField" ?? DBNull.Value);
        ...
        cmd.ExecuteNonQuery();
    }
}

conn.Close();

Please suggest if I can improve the performance of this code by any means. Or is their any better/faster way of doing it?

Some ideas:

1. Keep the same connection open during the whole batch insertion. Open it at the beginning, then only close it when you're done.

2. Don't recreate the SqlCommands during each loop iteration. Create them once at the very beginning, then only update the parameters' values: cmd.Parameters["@x"].Value = …;.

3. You're inserting into the 2nd table (B) via a foreach loop that inserts single records. You could look into replacing this with a single INSERT INTO TableB (x, y, z) SELECT x, y, z FROM @tvp, where @tvp is a table-valued parameter. Essentially, this means that you can populate e.g. a DataTable with rows you want to insert into the 2nd table, then pass that DataTable over as @tvp. TVPs are supported from SQL Server 2008 onwards, IIRC. Setting one of these up takes a little study the first time.

   (I'm not quite certain if the above INSERT statement would actually work, or whether TVPs only work as parameters to stored procedures (see e.g. this example).)

4. Going further than #3, move the insertions into tables A and B into a DB stored procedure. This SP would have as parameters the values that go into table A, as well as a table-valued parameter with the records that go into table B.