c# - 如何高效避免重复键异常

Question

我遇到了以下问题:

我正在尝试使 sql-server 中的表与多个外部数据库保持同步。这些外部数据库没有共享的唯一主键，因此本地表有一个简单的整数 PK。

现在要使本地表保持最新，请执行以下操作:

1. 查询外部数据库。
2. 数据转换为本地表的有效数据。
3. insert 用于尝试将数据写入本地表。
4. 如果insert返回duplicate entry异常，将通过select查询查找PK，并通过update查询将数据写入表。
5. 使用插入或更新行的主键修改另一个表。

现在这工作正常，但对我来说似乎效率很低。大多数情况下，数据已经在本地表中，并导致插入时出现重复键异常。这意味着需要处理很多异常，这是昂贵的。此外，由于 PK 由数据库管理，因此必须使用选择查询来查找要更新的行。

如何避免这种影响？我不想使用存储过程，因为我喜欢通过代码来管理查询并包含在版本控制中。

我看过 merge，但我看到太多人报告了它的问题。

我想我需要使用一种 upsert 形式，但我不确定如何使用由数据库管理的 PK。

tl;dr:我需要的是一个允许我插入或更新行(取决于重复键与否)的查询，该查询将始终返回该行的 PK。

Answer 1

我有一个我喜欢的过去完成的实现。您可能会发现它有用，也可能不会。

这就是它的工作原理...我使用适用于两者的模型对象将外部和本地数据加载到内存中。例如……

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
    public string Address { get; set; }

    // This comparer will be used to find records that exist or don't exist.
    public class KeyFieldComparer : IEqualityComparer<Person>
    {
        public bool Equals(Person p1, Person p2)
        {
            return p1.FirstName == p2.FirstName && p1.LastName == p2.LastName;
        }

        public int GetHashCode(Person p)
        {
            return p.FirstName.GetHashCode() ^ p.LastName.GetHashCode();
        }
    }

    // This comparer will be used to find records that are outdated and need to be updated.
    public class OutdatedComparer : IEqualityComparer<Person>
    {
        public bool Equals(Person p1, Person p2)
        {
            return p1.FirstName == p2.FirstName && p1.LastName == p2.LastName && (p1.PhoneNumber != p2.PhoneNumber || p1.Address != p2.Address);
        }

        public int GetHashCode(Person p)
        {
            return p.FirstName.GetHashCode() ^ p.LastName.GetHashCode();
        }
    }
}

我们需要一些方法来唯一标识我假设您拥有的记录。在这个例子中它是 FirstName和 LastName (我知道这不是很独特，但为简单起见，我们假设它运行良好)。 IEqualityComparer<>当列表加载到内存中时，将执行查找过时和新记录的工作。

现在我们简单的把现有的过期记录和全新的记录像这样分开...

List<Person> local = loadLocalRecords();
List<Person> external = loadExternalRecords();

var newRecordsToInsert = external.Except(local, new Person.KeyFieldComparer());

var outdatedRecordsToUpdate = local.Intersect(external, new Person.OutdatedComparer());

我希望这是有道理的。如果你有问题我可以回答。这种方法的好处是它以最少的数据库命中率完成工作(我认为)。坏处是它必须将所有内容加载到内存中，这对您来说可能不切实际。但是您的 table 尺寸必须很大才能成为问题。超过几百万条记录，具体取决于列数。