遍历C# List批量执行数据库查询的高效优化方案咨询

针对你这种跨MariaDB和Oracle的批量关联查询场景，以下几种方案能大幅提升性能，替代循环单查的低效方式：

方案1：批量导入临时表后Oracle端关联查询 #

核心思路是把MariaDB中获取的Cardboards数据批量导入Oracle临时表，再在Oracle端一次性关联生产流程数据，避免多次网络请求。

步骤： #

1. 在Oracle创建临时表

CREATE GLOBAL TEMPORARY TABLE TEMP_CARDBOARDS (
    ID NUMBER,
    CARDBOARD_NUMBER VARCHAR2(100),
    DATETIME TIMESTAMP,
    PRODUCTION_LINENUMBER VARCHAR2(50)
) ON COMMIT DELETE ROWS; -- 会话级临时表，提交后自动清空

2. C#批量插入Cardboards到Oracle临时表
   使用OracleBulkCopy实现高效批量插入，比循环插入快得多：

// 假设cardboards是从MariaDB获取的List<Cardboard>
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(long));
dt.Columns.Add("CARDBOARD_NUMBER", typeof(string));
dt.Columns.Add("DATETIME", typeof(DateTime));
dt.Columns.Add("PRODUCTION_LINENUMBER", typeof(string));

foreach (var item in cardboards)
{
    dt.Rows.Add(item.ID, item.Cardboard_number, item.DateTime, item.Production_LineNumber);
}

using (var oracleConn = new OracleConnection("你的Oracle连接字符串"))
{
    oracleConn.Open();
    using (var bulkCopy = new OracleBulkCopy(oracleConn))
    {
        bulkCopy.DestinationTableName = "TEMP_CARDBOARDS";
        bulkCopy.ColumnMappings.Add("ID", "ID");
        bulkCopy.ColumnMappings.Add("CARDBOARD_NUMBER", "CARDBOARD_NUMBER");
        bulkCopy.ColumnMappings.Add("DATETIME", "DATETIME");
        bulkCopy.ColumnMappings.Add("PRODUCTION_LINENUMBER", "PRODUCTION_LINENUMBER");
        bulkCopy.WriteToServer(dt);
    }

    // 执行关联查询，获取所有结果
    var query = @"
        SELECT 
            tc.ID,
            tc.CARDBOARD_NUMBER,
            p.PRODUCTION_NUMBER,
            p.POSNR,
            p.PRODUCTION_LINE
        FROM TEMP_CARDBOARDS tc
        JOIN (
            -- 这里替换成你已有的生产流程起止时间查询逻辑
            SELECT 
                PRODUCTION_NUMBER,
                POSNR,
                PRODUCTION_LINE,
                MIN(CASE WHEN PROCESS_ACTIVE = 1 THEN DATETIME END) AS START_TIME,
                MAX(CASE WHEN PROCESS_ACTIVE = 0 THEN DATETIME END) AS END_TIME
            FROM PRODUCTIONS
            GROUP BY PRODUCTION_NUMBER, POSNR, PRODUCTION_LINE
        ) p ON tc.PRODUCTION_LINENUMBER = p.PRODUCTION_LINE
            AND tc.DATETIME BETWEEN p.START_TIME AND p.END_TIME";

    // 使用EF Core或OracleCommand执行查询，一次性获取所有匹配结果
}

方案2：利用Oracle数据库链接（DB Link）直接跨库关联 #

如果你的Oracle数据库有访问MariaDB的权限，可以创建DB Link直接关联两边的表，无需在C#端中转数据。

步骤： #

1. 创建指向MariaDB的DB Link

CREATE DATABASE LINK MARIADB_LINK
CONNECT TO 你的MariaDB用户名 IDENTIFIED BY 你的MariaDB密码
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MariaDB服务器IP)(PORT = 3306))
    (CONNECT_DATA = (SID = 你的MariaDB实例名))
    (HS = OK) -- 用于异构数据库访问
)';

注：需要确保Oracle服务器安装了ODBC驱动并配置了MariaDB的数据源，具体配置可参考Oracle异构连接文档

2. 执行跨库关联查询
   直接在Oracle中写关联SQL，C#只需执行一次查询即可获取所有结果：

SELECT 
    tc.ID,
    tc.CARDBOARD_NUMBER,
    p.PRODUCTION_NUMBER,
    p.POSNR,
    p.PRODUCTION_LINE
FROM MARIADB_DB.CARDBOARDS@MARIADB_LINK tc
JOIN (
    -- 你的生产流程起止时间查询逻辑
    SELECT 
        PRODUCTION_NUMBER,
        POSNR,
        PRODUCTION_LINE,
        MIN(CASE WHEN PROCESS_ACTIVE = 1 THEN DATETIME END) AS START_TIME,
        MAX(CASE WHEN PROCESS_ACTIVE = 0 THEN DATETIME END) AS END_TIME
    FROM PRODUCTIONS
    GROUP BY PRODUCTION_NUMBER, POSNR, PRODUCTION_LINE
) p ON tc.PRODUCTION_LINENUMBER = p.PRODUCTION_LINE
    AND tc.DATETIME BETWEEN p.START_TIME AND p.END_TIME;

方案3：预缓存生产流程数据 #

如果生产流程数据不会频繁变动，可以预先生成并缓存生产流程的起止时间快照：

1. 在Oracle创建一个普通表PRODUCTION_SNAPSHOT，定期执行你的生产流程起止时间查询来刷新数据（比如用定时任务或触发器）。
2. C#批量获取Cardboards后，用方案1的方式导入Oracle临时表，关联PRODUCTION_SNAPSHOT查询，减少实时计算生产流程的开销。

性能优化注意点： #

• 确保MariaDB的Cardboards表中DateTime和Production_LineNumber字段有索引。
• Oracle的Productions表中PRODUCTION_LINE、PROCESS_ACTIVE、DATETIME字段需建立联合索引，加快生产流程起止时间的计算。
• 批量操作时尽量控制单次数据量，避免内存溢出。

内容的提问来源于stack exchange，提问作者philipp8230