0

It's currently taking around 5 minutes per million records, I was just wondering if this is the expected speed.

I was wondering if my batch size is to small i.e. 500 ?

My current code looks something like this...

using (var destinationConnection = new SqlConnection(Settings.Default.BatchOutputConnectionString))
{
    var tableName = mergedFileName.Substring(0, mergedFileName.IndexOf('.'));
    destinationConnection.Open();

    try
    {
        using (var createAndDropTableCommand = new SqlCommand("sp_DropAndCreateTable", destinationConnection))
        {
            createAndDropTableCommand.CommandType = CommandType.StoredProcedure;
            createAndDropTableCommand.Parameters.Add("@TableToCreate", SqlDbType.VarChar).Value = tableName;
            createAndDropTableCommand.ExecuteNonQuery();
        }

        foreach (var file in txtFiles)
        {
            using (var lineIterator = File.ReadLines(file).GetEnumerator())
            {
                while (lineIterator.MoveNext())
                {
                    var line = lineIterator.Current;
                    var dataColumns = line.Split('\t');
                    var dr = dt.NewRow();

                    for (var i = 0; i < dataColumns.Length; i++)
                    {
                        dr[i] = dataColumns[i].Trim().Length == 0 ? null : dataColumns[i];
                    }

                    dt.Rows.Add(dr);
                    recordCounter++;
                }
                using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName = "dbo." + tableName;
                    bulkCopy.BatchSize = 500;
                    bulkCopy.ColumnMappings.Clear();
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
                    }

                    bulkCopy.WriteToServer(dt);
                }
                dt.Clear();
            }
        }
    }
    finally
    {
        destinationConnection.Close();
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
StevieB
  • 6,263
  • 38
  • 108
  • 193
  • Why not try out table value parameter. Also let us see what is written in sp_DropAndCreateTable? – Kamran Shahid Apr 14 '14 at 08:55
  • 2
    Yes, that is much lower than you should expect, assuming there are no really really wide columns. Have you isolated (simple time outputs would suffice, in this case) where the time is spent? is it in the `while(lineIterator.MoveNext())` ? or is it in the `WriteToServer` ? As a side note: try simply not specifying a batch size at all - try it with the defaults. – Marc Gravell Apr 14 '14 at 08:58
  • 1
    On a completely unrelated note, [you should not use `sp_` to prefix your stored procedures](http://msdn.microsoft.com/en-us/library/dd172115%28v=vs.100%29.aspx). The prefix sp actually denotes special, not stored procedure. – GarethD Apr 14 '14 at 09:31
  • Thought sp_ meant System Procedure? – Charleh Apr 14 '14 at 09:41
  • So did I, but [read in an article by Aaron Bertrand](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix) that it meant special. – GarethD Apr 14 '14 at 10:36
  • Consider streaming the rows into WriteToServer and use one batch for the entire process. This will be the fastest option. See the docs for how to stream. – usr Apr 14 '14 at 11:03

0 Answers0