我有一个SQL查询完全如this post所述.总而言之，它将读取所有带有偏移量指定的Carboards.此查询在MariaDB数据库上执行.
现在，我在我的C#ASP.NET程序中有我的Cardboards(ID，Cardboard_number，DateTime，ProductionLineNode).我必须阅读每个Carboard的所有生产过程(基本上production. start = cardboard. datetime = production. end).<<
Oracle数据库中Productions的表如下所示(我没有自己创建该表，也无法更改任何内容，因为它也用于生产程序):
- 生产编号(编号)
- POSNR(数字)
- DateTime(时间戳(6))
- PROJECT_ACTIVE(1)
- 生产线(编号)
PROCESS_ACTIVE列用作标志，当启动生产流程时，一行插入DATETIME = sysdate，PROCESS_ACTIVE = 1，停止的行用sysdate，PROCESS_ACTIVE = 0表示.
我已经创建了一个查询来总结我的过程，所以我得到了每个Productivity_NUMBER和POSNR的开始和结束:
SELECT *
FROM
(
SELECT PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME as START, LEAD(DATETIME, 1, SYSDATE) OVER (ORDER BY DATETIME ASC) AS END
FROM ssq_lauftr
GROUP BY PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME
ORDER BY DATETIME
)
WHERE PROCESS_ACTIVE = 1
我在C#代码中遍历从MariaDB检索到的所有Cardboards，并执行这个查询为every cardboard(其中cardboard是从C#循环注入的对象):
SELECT *
FROM
(
SELECT PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME as START, LEAD(DATETIME, 1, SYSDATE) OVER (ORDER BY DATETIME ASC) AS END
FROM ssq_lauftr
WHERE PRODUCTION_LINE = cardboard.PRODUCTION_LINENUMBER and DATETIME <= cardboard.DATETIME
GROUP BY PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME
ORDER BY DATETIME
)
WHERE PROCESS_ACTIVE = 1 AND cardboard.DATETIME <= END
它在少量的纸板上工作得很好.
这个解决方案的问题是，如果我有很多Cardboard，阅读所有Productions的整个功能将花费太长的时间.有没有一种方法(例如使用PLSQL)使这个过程更有效？上面的SQL语句相当快，但是在C#中迭代列表并将结果添加到ProductionSet中会大大降低应用程序的速度.
Edit:
纸板存储在MariaDB中，而Productions存储在Oracle DB中.
我当前的C#代码描述的功能看起来像这样:
Cardboards = [.. _mariaDB.Cardboards.FromSql($@"
SET @cb_num = {request.Cardboard_Number};
select *
from (
SELECT *,
sum(Cardboard_Number LIKE CONCAT(@cb_num, '%')) over (
partition by ProductionLine_Number
order by timestamp, id
rows BETWEEN {CardboardOffset} preceding AND {CardboardOffset} following
) matches
from cardboards
) cardboards_with_matches
where matches
")];
HashSet<Production> productionSet = [];
for(int i = 0; i < Cardboards.Count(); i++)
{
productionSet.UnionWith(_oracleDB.Production.FromSqlRaw($@"
SELECT *
FROM
(
SELECT PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME as START, LEAD(DATETIME, 1, SYSDATE) OVER (ORDER BY DATETIME ASC) AS END
FROM Productions
WHERE PRODUCTION_LINE = {Cardboards.ElementAt(i).ProductionLine_Number} and {Cardboards.ElementAt(i).DateTime} <= cardboard.DATETIME
GROUP BY PRODUCTION_NUMBER, POSNR, PROCESS_ACTIVE, PRODUCTION_LINE, DATETIME
ORDER BY DATETIME
)
WHERE PROCESS_ACTIVE = 1 AND {Cardboards.ElementAt(i).DateTime} <= END
"));
}
MariaDB fiddle - Fiddle for Cardboards
OracleDB fiddle - Fiddle for Productions (not running in fiddle, but running in Oracle SQL developer, I honestly don't know the problem there)
为了详细分析，基本上当用户搜索Cardboard 'WDL—005943998—1'时，预期输出将是整个Cardboard的数据(在MariaDB中搜索)和Productivity_NUMBER = 461618的生产数据，因为Cardboard的日期时间介于生产开始和结束之间，并且生产与Cardboard扫描的同一行上.
请注意，可以有相同的生产多次，但具有不同的时间戳(例如，生产暂停).