在ADO.net中使用INSERT into语句时，我可以加快插入SQL Server的速度吗

我想看看是否可以做些什么来显著加快ADO.net的插入速度，但仍然可以使用多个INSERT INTO table values (...)命令进行插入。我正在将数据从专有的、不可查询的数据库文件转移到SQL Server中。我正在编写的实用程序将从脚本中使用。

我意识到，为了获得尽可能好的性能，INSERT INTO是错误的选择，但我仍然想知道是否应该尝试一些ADO.net或SQL Server方法。

我正在使用C#2010连接到SQL Server 2008。我将我的性能与使用Microsoft的ODBC 3.5驱动程序和Native Client 10.0将数据插入SQL Server的非托管代码进行比较。期望能够与ODBC的性能相匹配是否合理？

目标表没有索引或其他约束、触发器，它只是一个临时表。源数据是一堆非标准的数据类型，我必须将它们转换为字符串。

剥离到相关细节，我的代码是：

SqlCommand comm = new SqlCommand;
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction insertTransaction = conn.BeginTransaction();
comm.Connection = conn;
comm.Transaction = insertTransaction;
while(buffer.ReadNext())  // fill a buffer that I use to make my query
{
    // form my insert statement and assign it
    // It looks like: INSERT INTO myTable VALUES (5,'2016-02-16',NULL,3)
    // A good fraction of the data is numeric with decimal points. A good
    // fraction is dates.  The parsing and string building,
// extravagantly inefficient as it is, is not the culprit.
    // The  INSERT INTO does not specify the column names
    comm.CommandText = myStatement;
    if (comm.ExecuteNonQuery() != 1) {throw...}
}
insertTransaction.commit;

我尝试指定不同的隔离级别；我无法指定.Snapshot（不想将目标数据库配置为允许它）。没有什么不同。

如果我注释掉comm.ExecuteNoQuery，让它在形成INSERT语句时旋转，那么它会以我认为的速度运行，如果它真的在做什么的话。如果我取消注释它，它所花费的时间大约是我认为应该花费的时间的8倍。"‘8倍长’是从哪里来的？"你问道。好吧，我用普适数据集成器（旧数据连接）做了一个类似的操作（端到端相同的操作）。从Pervasive崩溃时的诊断来看，我相信该程序正在使用连续的INSERT INTO进行插入语句。它比我的程序快8倍，只是比我不执行INSERT INTO语句时形成它们慢一小部分。

Pervasive Integrator通过ODBC进行添加，ODBC驱动程序设置为使用Sql Native客户端。Data Integrator不是.Net软件。我还没有真正尝试过ODBC，我想我现在会尝试一下，但我的目标是摆脱ODBC，所以它只是一个数据点，而不是一个解决方案，即使它更快。

我曾尝试过填充dataTable以进行大容量插入，但填充数据集也花费了太长时间。我认为使用批量插入的一些替代方法是使其工作的最快方法，但即使我最终停止使用INSERT INTO方法，我也很好奇为什么它需要更长的时间（比我认为的要长）

insert语句相当长，我的表中大约有350列。

您是否尝试过将插入值分组为简单的内容：

INSERT INTO myTables VALUES (5,'2016-02-16',NULL,3), (6,'2015-02-16',NULL,6), (7,'2012-02-16',NULL,6)...

这里有一个简单的实现，它并不优雅（而且它还没有经过测试，我在记事本上写了这个，所以很可能有拼写错误），但是。。。

SqlCommand comm = new SqlCommand;
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction insertTransaction = conn.BeginTransaction();
comm.Connection = conn;
comm.Transaction = insertTransaction;
String baseQuery = "INSERT INTO myTable VALUES ";
List<String> values = new List<String>();
Int32 i = 0;
while(buffer.ReadNext())  // fill a buffer that I use to make my query
{
    // Build your VALUES section here
    values.Add("(5,'2016-02-16',NULL,3)");
    if (i % 100 == 0)  // Chunk these every 100
    {
        myStatement = baseQuery + String.Join(", ", values.ToArray());
        comm.CommandText = myStatement;
        if (comm.ExecuteNonQuery() != 1) {throw...}
        insertTransaction.commit;
        values = new List<String>();  // Clear out our values and start a new
    }
    i++;
}
if (values.Count > 0)  // If any are left, INSERT them
{
    myStatement = baseQuery + String.Join(", ", values.ToArray());
    comm.CommandText = myStatement;
   if (comm.ExecuteNonQuery() != 1) {throw...}
   insertTransaction.commit;
}

数据是否一次出现在一行中？换句话说，您是否让用户将数据输入到应用程序中，然后按下按钮启动插入
或
是否所有数据都可以随时插入

如果（1.）只是按照你一直在做的方式插入。

我反对在应用程序/客户端一次累积一行的数据。这导致了很多问题。

如果（2.）执行批量导入。

有三种类型的批量导入和批量导出操作
http://msdn.microsoft.com/en-us/library/ms187042.aspx

这些文件是文本格式的吗
它们是柱状的吗
它们是否可以从文件系统（即C:\pathTo\file.ext）访问
如果是，生成一个bcp格式的文件（从程序中打印出来，注意包括C:\pathTo\file.ext）。
然后TRUNCATE暂存表（或上面生成的格式化文件中的TRUNCATE INTO），并通过给定格式化文件和数据库连接作为参数的system（）调用调用bcp实用程序
它超级快
直接bcp上传将击败任何可以通过ado.net上传的东西。

相关内容

最新更新

热门标签：