sql server 2005 - 如何将缓慢的参数化插入更改为快速批量复制(即使是从内存中)




sql-server-2005 insert (8)

我在我的代码中有这样的东西(.Net 2.0,MS SQL)

SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial
Catalog=DataBase;Integrated Security=True");
  connection.Open();

  SqlCommand cmdInsert = connection.CreateCommand();
  SqlTransaction sqlTran = connection.BeginTransaction();
  cmdInsert.Transaction = sqlTran;

  cmdInsert.CommandText =
     @"INSERT INTO MyDestinationTable" +
      "(Year, Month, Day, Hour,  ...) " +
      "VALUES " +
      "(@Year, @Month, @Day, @Hour, ...) ";

  cmdInsert.Parameters.Add("@Year", SqlDbType.SmallInt);
  cmdInsert.Parameters.Add("@Month", SqlDbType.TinyInt);
  cmdInsert.Parameters.Add("@Day", SqlDbType.TinyInt);
  // more fields here
  cmdInsert.Prepare();

  Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);

  StreamReader reader = new StreamReader(stream);
  char[] delimeter = new char[] {' '};
  String[] records;
  while (!reader.EndOfStream)
  {
    records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);

    cmdInsert.Parameters["@Year"].Value = Int32.Parse(records[0].Substring(0, 4));
    cmdInsert.Parameters["@Month"].Value = Int32.Parse(records[0].Substring(5, 2));
    cmdInsert.Parameters["@Day"].Value = Int32.Parse(records[0].Substring(8, 2));
    // more here complicated stuff here
    cmdInsert.ExecuteNonQuery()
  }
  sqlTran.Commit();
  connection.Close();

cmdInsert.ExecuteNonQuery()注释掉这段代码在不到2秒的时间内执行。 SQL执行需要1分20秒。 有大约0.5百万条记录。 表是空的。 具有类似功能的SSIS数据流任务大约需要20秒。

  • 大容量插入不是一个选项(见下文)。 我在这个导入过程中做了一些花哨的东西。
  • 我的测试机器是2 GB RAM的Core 2 Duo。
  • 当在任务管理器中查找CPU没有完全的直到。 IO似乎也没有被充分利用。
  • 模式很简单,就像地狱一样:一个表以AutoInt作为主索引,少于10个整数,小整数和字符(10)。

在这里回答一些问题之后,我发现可以从内存中执行批量复制 。 我拒绝使用批量复制,因为我认为它必须从文件中完成...

现在我用这个,需要20秒(比如SSIS任务)

  DataTable dataTable = new DataTable();

  dataTable.Columns.Add(new DataColumn("ixMyIndex", System.Type.GetType("System.Int32")));   
  dataTable.Columns.Add(new DataColumn("Year", System.Type.GetType("System.Int32")));   
  dataTable.Columns.Add(new DataColumn("Month", System.Type.GetType("System.Int32")));
  dataTable.Columns.Add(new DataColumn("Day", System.Type.GetType("System.Int32")));
 // ... and more to go

  DataRow dataRow;
  object[] objectRow = new object[dataTable.Columns.Count];

  Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);

  StreamReader reader = new StreamReader(stream);
  char[] delimeter = new char[] { ' ' };
  String[] records;
  int recordCount = 0;
  while (!reader.EndOfStream)
  {
    records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);

    dataRow = dataTable.NewRow();
    objectRow[0] = null; 
    objectRow[1] = Int32.Parse(records[0].Substring(0, 4));
    objectRow[2] = Int32.Parse(records[0].Substring(5, 2));
    objectRow[3] = Int32.Parse(records[0].Substring(8, 2));
    // my fancy stuf goes here

    dataRow.ItemArray = objectRow;         
    dataTable.Rows.Add(dataRow);

    recordCount++;
  }

  SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null);
  bulkTask.DestinationTableName = "MyDestinationTable"; 
  bulkTask.BatchSize = dataTable.Rows.Count;
  bulkTask.WriteToServer(dataTable);
  bulkTask.Close();

1分钟听起来相当合理的50万条记录。 这是每个0.00012秒的记录。

表格是否有任何索引? 如果这是一个选项,删除这些并在批量插入后重新应用它们可以提高插入的性能。


不要单独插入每个记录,请尝试使用SqlBulkCopy类一次批量插入所有记录。

创建一个DataTable并将所有记录添加到DataTable,然后使用SqlBulkCopyWriteToServer一次批量插入所有数据。


如果我不得不猜测,我会首先在tbTrafficLogTTL表上查找太多或错误的索引。 没有查看表的模式定义,我不能说,但我遇到类似的性能问题:

  1. 主键是GUID,主索引是CLUSTERED。
  2. 在一组字段上有一些UNIQUE索引。
  3. 桌子上有太多的索引。

当您开始索引50万行数据时,创建和维护索引花费的时间就会增加。

我还会注意到,如果您有任何选项将年,月,日,时,分,秒字段转换为单个日期时间2或时间戳字段,您应该。 你为数据架构增加了很多复杂性,没有任何收获。 我甚至会考虑使用这样一个拆分字段结构的唯一原因是,如果你正在处理一个不能由于任何原因而被改变的预先存在的数据库模式。 在这种情况下,它就是你。


如果某种形式的批量插入不是一个选项,则另一种方式是多个线程,每个线程都有自己的连接到数据库。

现在的系统的问题是,你有50万往返数据库,并且在开始下一个回合之前等待第一个回合 - 任何类型的延迟(即机器之间的网络)意味着大部分你的时间花在等待。

如果你可以分工,也许使用某种形式的生产者/消费者设置,你可能会发现你可以更多地利用所有的资源。

但是,要做到这一点,你将不得不失去一个伟大的事务 - 否则第一个作者线程将阻止所有其他人,直到其交易完成。 你仍然可以使用交易,但是你必须使用很多小的交易,而不是一个大的交易。

SSIS会很快,因为它使用的是批量插入方法 - 首先执行所有复杂的处理,生成最终插入的数据列表并同时批量插入。


我最后一份合同有类似的问题。 你正在做500,000次到SQL插入你的数据。 为了显着提高性能,您需要调查SQL名称空间中的BulkInsert方法。 一旦我实现了批量导入,我已经“重新加载”从2+小时起恢复了几十个表到31秒的进程。


我认为每秒处理8,333条记录似乎不合理......你期待什么样的吞吐量?


需要交易吗? 使用事务比简单的命令需要更多的资源。

另外,如果您确定,插入的值是正确的,则可以使用BulkInsert。


首先在所有记录上做数据上的花哨的东西。 然后批量插入它们。

(因为你没有选择后插入..我没有看到在BulkInsert之前应用所有操作的数据的问题





bulk