c# - 在数据量大但更改集小的表上进行批量更新/合并的最佳方法

Question

我有一个包含 200K+ 行的表，我需要每天更新它。我的控制台应用程序在每天运行时生成所有这些数据，并且需要用结果更新表格。

情况是我在缓慢的网络上运行，每次运行时只有不到 0.1% 的行要更新/插入/删除，因此显然还有优化空间。该表很简单 - 键列，外加 2 个 nvarchar 列。

所以我的问题是 - 在这种特殊情况下最好的方法是什么？我总是可以把它翻过来并执行 SQLBulkCopy，但是 SqlDataAdapter 会更高效吗？

谢谢，

迈克

Answer 1

将包含所有更改的 XML 发送到存储过程。

访问数据库一次。

这里有一个老例子:

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

这是一个较小的示例，但显示了基础知识。

http://www.mindfiresolutions.com/Sending-Multiple-Records-As-XML-To-SQL-Server-Stored-Procedure-1861.php

将 xml 发送到存储过程。将 xml 切碎到 @variable 或 #temp 表中。使用@variable 或#temp 表执行更新/插入(或合并/UPSERT)。

编辑:

http://weblogs.asp.net/dwahlin/archive/2009/09/30/passing-multiple-records-to-a-stored-procedure-in-sql-server.aspx

另一个例子。

我喜欢做的是创建一个强大的数据集。将您的数据放入强大的数据集中。然后将 ds.GetXml() 发送到存储过程。

这样一来，您就可以获得强类型(使用强数据集)，并且您不必编写自己的 xml 生成器，您可以借助 .GetXml()。提示:创建强数据集后，删除命名空间(tempuri 或类似的东西)

附加(2019 年 Spring )

由于 xml“膨胀”，我不再将项目放在强数据集(或任何数据集)中。

我编写了一个自定义的 PocoObject-To-Xml 转换器(基于 xml 属性)并将其传递给存储过程。

下面是基于元素的...并显示了 xml-bloat

<Employees>
  <Employee>
      <EmployeeKey>123</EmployeeKey>
      <LastName>Smith</LastName>
      <FirstName>John</FirstName>
  </Employee>
  <Employee>
      <EmployeeKey>234</EmployeeKey>
      <LastName>Jones</LastName>
      <FirstName>Mary</FirstName>
  </Employee>
</Employees>

对比(更精简)

<Employees>
  <Employee EmployeeKey="123" LastName="Smith" FirstName="John" />
  <Employee EmployeeKey="234" LastName="Jones" FirstName="Mary" />
</Employees>

以及下面的自定义转换器代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace MyNamespace
{
    public class EmployeesToXmlConverter
    {
        public string ConvertToXmlString(ICollection<EmployeePoco> emps)
        {
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);

            writer.WriteStartElement("root");

            if (null != emps && emps.Any())
            {
                writer.WriteStartElement("Employees");
                foreach (EmployeePoco emp in emps)
                {
                    writer.WriteStartElement("Employee");
                    writer.WriteAttributeString("EmployeeKey", Convert.ToString(emp.EmployeeKey));
                    writer.WriteAttributeString("LastName", emp.LastName);
                    writer.WriteAttributeString("FirstName", emp.FirstName);
                    writer.WriteEndElement(); ////closing patient tag
                }

                writer.WriteEndElement(); ////closing emps tag

            }

            writer.WriteEndElement(); ////closing root tag
            writer.Close();
            string returnValue = sb.ToString();
            return returnValue;
        }
    }
}