推荐用于多个查找的编程模式

本文关键字：编程 模式 查找 用于 | 更新日期: 2023-09-27 18:08:58

我的任务是在另一个供应商生成的CSV文件和300多个独立但结构相同的CRM数据库之间创建一个数据同步过程。所有CRM数据库都定义在同一个SQL Server实例中。以下是具体内容:

源数据将是一个CSV，其中包含客户选择加入营销通信的所有电子邮件地址的列表。这个CSV文件将在每晚全部发送，但将包含记录级别的日期/时间戳，这将允许我仅选择自上次处理周期以来已修改的记录。CSV文件可能有数十万行，尽管每天的预期更改将大大低于此值。

我将从CSV中选择数据，并将每行转换为自定义List<T>对象。

一旦查询了CSV并转换了数据，我将需要将此List<T>的内容与CRM数据库进行比较。这是因为CSV文件中包含的任何给定的电子邮件地址都可能:

  • 不存在于任何 300个数据库
  • 存在于300个数据库中的一个
  • 存在于多个数据库

在主CSV列表中的电子邮件地址与任何CRM数据库之间存在匹配的任何情况下，匹配的CRM记录将使用CSV文件中包含的值进行更新。

一般来说，我认为我应该这样做:

foreach(string dbName in masterDatabaseList)
{
    //open db connection
    foreach(string emailAddress in masterEmailList)
    {
        //some helper method that would execute a SQL statement like
        //"IF EXISTS ... WHERE EMAIL_ADDRESS = <emailAddress>" return true;
        bool matchFound = EmailExistsInDb(emailAddress)
        if (matchFound )
        {
            //the current email from the master list does exist in this database
            //do necessary updates and stuff
        }
    }
}

这是最有效的方法吗?我不愿意为了查看主CSV列表中的每一封电子邮件是否存在而访问300个数据库，可能需要数千次。理想情况下，我希望生成如下SQL语句:

"SELECT * FROM EMAIL_TABLE WHERE EMAIL_ADDRESS IN(email1,email2, email3,...)"

这将允许对数据库执行单个查询，但我不知道这种方法是否会更好/更有效，特别是因为我必须动态生成SQL，并且可能会将其开放给注入。

这个场景中的最佳实践是什么?因为每次都需要比较300个数据库，所以我正在寻找一种能够以最少的处理时间产生最佳结果的方法。在我的生产代码中，我将实现一种多线程方法，以便可以同时处理多个数据库，因此任何方法都需要是线程安全的。

推荐用于多个查找的编程模式

你的基本想法似乎是对的。对CSV中的每一行访问数据库一次将会太慢。你可以通过LINQ创建一个"where in"语句，如下所示:

var addresses = GetEmailAddresses();
var entries = ctx.Entries.Where(e => addresses.Contains(e.EmailAddress));

然而，如果你的列表中有太多的地址，它将花费很长很长的时间来生成和评估你的查询。我建议将输入列表分成合理大小的批次(200个条目?)，然后使用上面的技巧通过单个数据库检查来处理每个批次。

一旦你让这个工作，你可以尝试一些其他的事情，看看它们是否会产生可衡量的性能差异:

  1. 调整批处理大小
  2. 以不同程度的并行度独立运行批次。
  3. 使用数据库表上的索引，特别是电子邮件地址字段。
  4. 在将电子邮件地址批量分解之前，先订购电子邮件地址。db查询可能会更好地利用硬盘缓存策略。

您可以将csv列表对象的内容放入表值参数中。然后调用存储过程，传入该TVP。然后，存储过程可以在300个数据库中运行游标，并连接到表值参数(使用ad-hoc sql)。它基本上是一个循环，迭代300次，这还不算太坏。像这样的

CREATE PROCEDURE yourNewProcedure
(
    @TableValueParameter dbo.udtTVP READONLY
)
AS
DECLARE @dbName varchar(255)
DECLARE @SQL nvarchar(3000)
DECLARE DB_Cursor CURSOR LOCAL FOR
    SELECT DISTINCT name
    FROM sys.databases
    WHERE Name like '%yourdbs%'
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @dbName
WHILE @@FETCH_STATUS  = 0
BEGIN
    SET @SQL = 'UPDATE t
                SET t2.Field = t.Field              
                FROM @TableValueParameter t
                JOIN [' + @dbName + ']..TableYouCareAbout t2 ON t.Field = t2.Field '
    EXEC sp_executesql @SQL, N'@TableValueParameter dbo.udtTVP', @TableValueParamete
    FETCH NEXT FROM DB_Cursor INTO @dbName
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
相关文章：