none
DataTable - Great performance, did I really need all of this code? RRS feed

  • Question

  • after conversing with the MSFT expert at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3680755&SiteID=1  and following his recommendation, we proved that using a DataTable out performs a batch of concatenated insert commands (40,000 of them) by 2 to 1.  We're not interested in the Bulk Insert features.

     

    Because it wasnt immediately obvious what the bare mimimum amount of code was for batch inserting to a db from a datatable, I'm posting the way I coded this approach and wonder if folks can tell me if I've coded too much (for example specifying both .net and sql data types), if another DT with namespaces could have been joined and thus further improved performance, if I really needed to name the DT etc etc?  This seems like an excessive amount of code, and I didnt even follow the pattern suggested by one author where the SELECT command is also coded to "ensure compatibility" even though I'd never use it.

     

    Code Snippet

    DataTable dT = new DataTable();

    dT.TableName = "BatchInserts";

    string[] colNames = {"c1","c2","c3","c4","c5","c6","c7","c8","c9","c10","Namespace"};

    string[] colTypes = {"System.Guid","System.String","System.Int64","System.Int64",

    "System.Int64","System.Boolean","System.Int16","System.String","System.Guid",

    "System.String","System.String" };

    for (int i = 0; i < colTypes.Length; i++)

    {

    dT.Columns.Add(new DataColumn(colNames[i],Type.GetType(colTypes[i])));

    }

    dT.Columns[7].AllowDBNull = true;

    foreach (ClassA classA in message.Entries)

    {

    dT.Rows.Add((Guid)classA.prop1, classA.prop2, classA.prop3, classA.prop4,

    classA.prop5, classA.prop6, classA.prop7, classA.prop8,

    (Guid)classA.prop9, classA.prop10, classA.Namespace);

    }

    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.InsertCommand = new SqlCommand("INSERT INTO [dbo].[table1] " +

    "SELECT @c1, b.[Id], a.[Id], @c2, a.[anotherId], @c3, " +

    "@c4, @c5, @c6, @c7, @c8 " +

    "FROM [dbo].[table2] a, [dbo].[table3] b, [dbo].[Namespace] c " +

    "WHERE a.[c9] = @c9 and b.[c10] = @c10 and b.[NamespaceId] = c.[Id] and c.[Uri] = @Namespace");

    adapter.InsertCommand.Parameters.Add("@c1",SqlDbType.UniqueIdentifier,0,"c1");

    adapter.InsertCommand.Parameters.Add("@c2",SqlDbType.VarChar,255,"c2");

    adapter.InsertCommand.Parameters.Add("@c3",SqlDbType.BigInt,0,"c3");

    adapter.InsertCommand.Parameters.Add("@c4",SqlDbType.BigInt,0,"c4");

    adapter.InsertCommand.Parameters.Add("@c5",SqlDbType.SmallInt,0,"c5");

    adapter.InsertCommand.Parameters.Add("@c6",SqlDbType.Bit,0,"c6");

    adapter.InsertCommand.Parameters.Add("@c7",SqlDbType.SmallInt,0,"c7");

    //the xml column hasnt been tested yet, may be incompatible with string

    adapter.InsertCommand.Parameters.Add("@c8",SqlDbType.Xml,0,"c8");

    adapter.InsertCommand.Parameters.Add("@c9",SqlDbType.UniqueIdentifier,0,"c9");

    adapter.InsertCommand.Parameters.Add("@c10",SqlDbType.VarChar,255,"c10");

    adapter.InsertCommand.Parameters.Add("@Namespace",SqlDbType.VarChar,255,"Namespace");

    try

    {

    TransactionOptions transactionOptions = new TransactionOptions();

    //TODO get rid of transactionOptions in next 2 statements

    transactionOptions.Timeout = new TimeSpan(0, 5, 30);

    using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))

    {

    SqlConnection sqlConnection = new SqlConnection(dBConnectionString);

    adapter.InsertCommand.Connection = sqlConnection;

    adapter.InsertCommand.Connection.Open();

    adapter.Update(dT);

    adapter.InsertCommand.Connection.Close();

    transactionScope.Complete();

    ...etc

     

     

     

     

     

     

     

    Wednesday, August 27, 2008 9:01 PM

Answers