20'000 objects isn't very much.
You should be able to do it in less than five seconds 700ms.
Your way is very slow because you do
foreach(jsonObject)
connection.open()
cmd.execute() // Individual insert-statement
connection.close()
Opening and Closing a connection is expensive, as is executing each command separately.
You should instead do
int batchSize = 100;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
connection.Open();
transaction.Begin();
for(int i = 0; i < Count;++i)
{
sb.Append("SQL-Insert");
if(i%batchSize == 0 && i != 0)
{
execute(sb.ToString())
sb.Length = 0;
}
}
execute(sb.ToString())
transaction.commit();
// TODO: Try/Catch + Rollback
connection.Close();
A much easier way is to fetch the table you want to insert your objects to with SELECT * FROM table_name WHERE (1=2)
then add the entries to the datatable, and then call DataAdaper.Update()
If your table has a primary-key, you can even auto-generate the insert command using SqlCommandBuilder.
C#
public static void InsertUpdateDataTable(string strTableName, System.Data.DataTable dt)
{
string strSQL = string.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]"));
using (System.Data.SqlClient.SqlDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter(strSQL, getConnectionString())) {
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daInsertUpdate);
daInsertUpdate.InsertCommand = cmdBuilder.GetInsertCommand();
daInsertUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand();
daInsertUpdate.Update(dt);
}
}
VB.NET:
Public Shared Sub InsertUpdateDataTable(strTableName As String, dt As System.Data.DataTable)
Dim strSQL As String = String.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]"))
Using daInsertUpdate As New System.Data.SqlClient.SqlDataAdapter(strSQL, getConnectionString())
Dim cmdBuilder As New SqlCommandBuilder(daInsertUpdate)
daInsertUpdate.InsertCommand = cmdBuilder.GetInsertCommand()
daInsertUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand()
daInsertUpdate.Update(dt)
End Using
End Sub
You can set the batch-size on the DataAdaper, and it generates the SQL for you as well.
And in a database-agnostic way:
private static System.Data.Common.DbProviderFactory m_factory = System.Data.Common.DbProviderFactories.GetFactory(typeof(System.Data.SqlClient.SqlClientFactory).Namespace);
public static void InsertUpdateDataTable(string strTableName, System.Data.DataTable dt)
{
if (dt == null)
throw new System.ArgumentNullException("DataTable dt may not be NULL.");
// https://msdn.microsoft.com/en-us/library/aadf8fk2(v=vs.110).aspx
using (System.Data.Common.DbDataAdapter daInsertUpdate = m_factory.CreateDataAdapter())
{
using (System.Data.Common.DbConnection conn = m_factory.CreateConnection())
{
conn.ConnectionString = getConnectionString();
daInsertUpdate.SelectCommand = conn.CreateCommand();
daInsertUpdate.SelectCommand.CommandText = string.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]"));
using (System.Data.Common.DbCommandBuilder cmdBuilder = m_factory.CreateCommandBuilder())
{
cmdBuilder.DataAdapter = daInsertUpdate;
daInsertUpdate.InsertCommand = cmdBuilder.GetInsertCommand();
daInsertUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand();
} // End Using cmdBuilder
daInsertUpdate.Update(dt);
} // End Using conn
} // End Using daInsertUpdate
System.Console.WriteLine(dt);
}
And using a transaction (assuming ls is a list of numbers):
private static System.Data.Common.DbProviderFactory m_factory = System.Data.Common.DbProviderFactories.GetFactory(typeof(System.Data.SqlClient.SqlClientFactory).Namespace);
public static string getConnectionString()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.InitialCatalog = "TestDb";
csb.IntegratedSecurity = true;
return csb.ConnectionString;
}
public static System.Data.Common.DbConnection GetConnection()
{
var con = m_factory.CreateConnection();
con.ConnectionString = getConnectionString();
return con;
}
public static int BatchedInsert(System.Collections.IList ls)
{
int iAffected = 0;
int batchSize = 100; // Each batch corresponds to a single round-trip to the DB.
using (System.Data.IDbConnection idbConn = GetConnection())
{
lock (idbConn)
{
using (System.Data.IDbCommand cmd = idbConn.CreateCommand())
{
lock (cmd)
{
if (cmd.Connection.State != System.Data.ConnectionState.Open)
cmd.Connection.Open();
using (System.Data.IDbTransaction idbtTrans = idbConn.BeginTransaction())
{
try
{
cmd.Transaction = idbtTrans;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
for (int i = 0; i < ls.Count; ++i)
{
sb.Append("INSERT INTO T_TransactionInsertTest(TestValue) VALUES ( ");
sb.Append(ls[i].ToString());
sb.AppendLine(");");
if (i % batchSize == 0 && i != 0)
{
cmd.CommandText = sb.ToString();
iAffected += cmd.ExecuteNonQuery();
sb.Length = 0;
}
}
if (sb.Length != 0)
{
cmd.CommandText = sb.ToString();
iAffected += cmd.ExecuteNonQuery();
}
idbtTrans.Commit();
} // End Try
catch (System.Data.Common.DbException ex)
{
if (idbtTrans != null)
idbtTrans.Rollback();
iAffected = -1;
//if (Log(ex))
throw;
} // End catch
finally
{
if (cmd.Connection.State != System.Data.ConnectionState.Closed)
cmd.Connection.Close();
} // End Finally
} // End Using idbtTrans
} // End lock cmd
} // End Using cmd
} // End lock idbConn
} // End Using idbConn
return iAffected;
} // End Function BatchedInsert
