代码之家  ›  专栏  ›  技术社区  ›  sean717

加快SQL插入速度

  •  6
  • sean717  · 技术社区  · 16 年前

    我有以下方法将数百万行数据插入到一个表中(我使用的是SQL 2008),这看起来很慢,有没有任何方法可以加快插入速度?

    这是代码段-我使用MS企业库

            public void InsertHistoricData(List<DataRow> dataRowList)
            {
                string sql = string.Format( @"INSERT INTO [MyTable] ([Date],[Open],[High],[Low],[Close],[Volumn])
                    VALUES( @DateVal, @OpenVal, @High, @Low, @CloseVal, @Volumn )");
    
                DbCommand dbCommand = VictoriaDB.GetSqlStringCommand( sql );
                DB.AddInParameter(dbCommand, "DateVal", DbType.Date);
                DB.AddInParameter(dbCommand, "OpenVal", DbType.Currency);
                DB.AddInParameter(dbCommand, "High", DbType.Currency );
                DB.AddInParameter(dbCommand, "Low", DbType.Currency);
                DB.AddInParameter(dbCommand, "CloseVal", DbType.Currency);
                DB.AddInParameter(dbCommand, "Volumn", DbType.Int32);
    
                foreach (NasdaqHistoricDataRow dataRow in dataRowList)
                {
                    DB.SetParameterValue( dbCommand, "DateVal", dataRow.Date );
                    DB.SetParameterValue( dbCommand, "OpenVal", dataRow.Open );
                    DB.SetParameterValue( dbCommand, "High", dataRow.High );
                    DB.SetParameterValue( dbCommand, "Low", dataRow.Low );
                    DB.SetParameterValue( dbCommand, "CloseVal", dataRow.Close );
                    DB.SetParameterValue( dbCommand, "Volumn", dataRow.Volumn );
    
                    DB.ExecuteNonQuery( dbCommand );
                }
            }
    
    4 回复  |  直到 15 年前
        1
  •  10
  •   Jorge Ferreira    15 年前

    考虑使用 bulk insert 相反。

    sqlbackcopy使您能够高效地批量 从中加载包含数据的SQL Server表 另一个来源。sqlblkcopy类 只能用于将数据写入SQL 服务器表。但是,数据 源不限于SQL Server; 任何数据源都可以使用,只要 因为数据可以加载到 数据表实例或用 IDataReader实例。对于这个例子 文件将包含大约1000个 记录,但此代码可以处理 大量数据。

    此示例首先创建一个 DataTable 并用数据填充它。这是保存在内存中的。

    DataTable dt = new DataTable();
    string line = null;
    bool firstRow = true;
    
    using (StreamReader sr = File.OpenText(@"c:\temp\table1.csv"))
    {  
          while ((line = sr.ReadLine()) != null)
          {
                string[] data = line.Split(',');
                if (data.Length > 0)
                {
                      if (firstRow)
                      {
                            foreach (var item in data)
                            {
                                  dt.Columns.Add(new DataColumn());
                            }
                            firstRow = false;
                       }
    
                       DataRow row = dt.NewRow();
                       row.ItemArray = data;
                       dt.Rows.Add(row);
                 }
          }
    }
    

    然后我们推动 可计算的 一次到服务器。

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))
    {
          cn.Open();
          using (SqlBulkCopy copy = new SqlBulkCopy(cn))
          {
                copy.ColumnMappings.Add(0, 0);
                copy.ColumnMappings.Add(1, 1);
                copy.ColumnMappings.Add(2, 2);
                copy.ColumnMappings.Add(3, 3);
                copy.ColumnMappings.Add(4, 4);
                copy.DestinationTableName = "Censis";
                copy.WriteToServer(dt);
          }
    } 
    
        2
  •  1
  •   Cruachan    16 年前

    对于任何关系数据库,在执行大量插入(甚至是任何数据更改)时的一个一般性提示是,先删除所有辅助索引,然后再重新创建它们。

    为什么会这样?对于二级索引,索引数据将在磁盘上的其他位置,而不是数据,因此对每个索引中写入表的每个记录,充其量强制进行额外的读/写更新。事实上,它可能比这更糟,因为有时数据库会决定它需要对索引执行更严重的重组操作。

    当您在插入操作结束时重新创建索引时,数据库将只执行一次完整的表扫描来读取和处理数据。您不仅可以在磁盘上得到组织得更好的索引,而且所需的总工作量也会更少。

    什么时候值得这样做?这取决于您的数据库、索引结构和其他因素(例如,如果您的索引位于数据的单独磁盘上),但我的经验法则是,如果我在一个包含100万条或更多记录的表中处理的记录超过10%,就要考虑它,然后检查测试插入是否值得。

    当然,在任何特定的数据库中都会有专门的批量插入例程,您也应该看看这些例程。

        3
  •  1
  •   H a z M a t I n k    15 年前

    仅供参考-在一个记录集中循环并在关系数据库上执行一百万个以上的插入,是加载表时最糟糕的情况。有些语言现在提供记录集对象。为了获得最快的性能,请使用大容量插入。以分钟而不是小时加载数百万行。数量级比任何其他方法都快。

    例如,我在一个电子商务项目中工作,该项目要求每晚刷新产品列表。在高端Oracle数据库中插入100000行,耗时10小时。如果我没记错的话,逐行插入时的最高速度大约是每秒10次。痛苦缓慢,完全没有必要。对于大容量插入-100k行应不到一分钟。

    希望这有帮助。

        4
  •  0
  •   eKek0    16 年前

    数据来自哪里?你能批量插入吗?如果是这样,那是你能选择的最佳选择。

    推荐文章