PHP Oracle 查询 select 语句内循环慢 [英] PHP Oracle query select statement inside loop slow
问题描述
我有这个php函数来检查文本文件中的数据并将其插入到数据库中.
I have this php function to check and insert data from text file to database.
//Get All Model
$qModel = oci_parse($c1, "SELECT MODELID, MODEL_NAME FROM MEP_TBL_MODEL WHERE ACTIVE = 'Y' AND LOCATION = 'PCBA' ORDER BY MODELID ASC");
oci_execute($qModel);
while($dModel = oci_fetch_array($qModel))
{
//Configuration
$qDtl = oci_parse($c1, "SELECT * FROM MEP_TBL_MODEL_CONFIGURATION WHERE MODELID_FK = '" . $dModel['MODELID'] . "'");
oci_execute($qDtl);
while($dDtl = oci_fetch_array($qDtl))
{
$modelIDAccept[] = $dDtl['CONFIGURATIONID'];
$dateCode = date($dDtl['DATE_CODE']);
$readRowAfter = date($dDtl['READ_ROW_AFTER']);
$createFromFormat = $dDtl['CREATE_FROM_FORMAT'];
$ipAddress = $dDtl['IP_ADDRESS'];
$status = $dDtl['STATUS'];
if($dDtl['SOURCE'] != "")
{
$source = "\\".$dDtl['SOURCE'];
}
else
{
$source = "";
}
if(empty($ipAddress))
{
$fileAccept = file_get_contents("\\\\192.168.184.13\\Reports\\".$dModel['MODEL_NAME'].$source."\\Accept\\Accept_".$dDtl['MODEL_CODE']."_".$dateCode."_".$dDtl['TS_CODE'].".txt");
$linesAccept = explode("\n",$fileAccept);
$rowsintimespanAccept = 0;
for($i = $readRowAfter; $i < count($linesAccept); $i++)
{
$dateobjAccept = DateTime::createFromFormat($createFromFormat, $linesAccept[$i]);
if($dateobjAccept < $toDateTime && $dateobjAccept > $fromDateTime)
{
$rowsintimespanAccept++;
$logDate = $dateobjAccept->format('Y-m-d H:i:s');
//I put select query and insert here but it so slow.
$qChk = oci_parse($c1, "SELECT * FROM MEP_TBL_OUTPUT_DETAILS WHERE MODELID_FK = '" . $dModel['MODELID'] . "' AND RUNNING_DATE = TO_DATE('$logDate', 'YYYY-MM-DD hh24:mi:ss') AND TS_CODE = '" . $dDtl['TS_CODE'] . "' AND SHIFT = 'Morning' AND QUANTITY_STATUS = 'OK' AND CONFIGURATIONID_FK = '" . $dDtl['CONFIGURATIONID'] . "'");
oci_execute($qChk);
if(oci_fetch($qChk) > 0)
{
}
else
{
$qInsert = oci_parse($c1, "INSERT INTO MEP_TBL_OUTPUT_DETAILS(MODELID_FK, RUNNING_DATE, QUANTITY_STATUS, TS_CODE, SHIFT, CONFIGURATIONID_FK) VALUES('" . $dModel['MODELID'] . "', TO_DATE('$logDate', 'YYYY-MM-DD hh24:mi:ss'), 'OK', '" . $dDtl['TS_CODE'] . "', 'Morning', '" . $dDtl['CONFIGURATIONID'] . "')");
oci_execute($qInsert);
}
}
}
$totalAccept[] = $rowsintimespanAccept;
}
}
}
当我尝试运行代码时，我加载页面的速度很慢，有时它会显示执行超时.
When I tried to run the code, I got very slow loading the page and sometimes it show me time out execution.
我的问题是，有什么方法可以使查询在循环内或循环外快速进行?我知道它很慢，因为当我删除选择和插入查询时，加载页面只有 3-4 秒.
My question, is there any way to make the query fast maybe inside or outside the loop? I knew it slow because when I remove the select and insert query, the load page is only 3-4 seconds.
推荐答案
如果我正确阅读了您的代码，那么您所追求的是可以在数据库上运行的单个 MERGE 语句.我不懂 PHP，所以我不能告诉你它应该如何调用，但我可以给你运行的 SQL 语句:
If I've read your code correctly, what you're after is a single MERGE statement that you can run on the database. I don't know PHP, so I can't give you how it should be called, but I can give you the SQL statement to run:
MERGE INTO mep_tbl_output_details tgt
USING (SELECT mtm.modelid,
mtm.model_name,
mtmc.configurationid,
mtmc.date_code,
mtmc.read_row_after,
mtmc.create_from_format,
mtmc.ip_address,
mtmc.status,
mtmc.ts_code
FROM mep_tbl_model mtm
INNER JOIN mep_tbl_model_configuration mtmc ON mtm.modelid = mtmc.modelid_fk
WHERE mtm.active = 'Y'
AND mtm.location = 'PCBA') src
ON (tgt.modelid_fk = src.modelid
AND tgt.ts_code = src.ts_code
AND tgt.configurationid_fk = src.configurationid
AND tgt.runningdate = :log_date
AND tgt.shift = 'Morning'
AND tgt.quantity_status = 'OK')
WHEN NOT MATCHED THEN
INSERT (tgt.modelid_fk, tgt.running_date, tgt.quantity_status, tgt.ts_code, tgt.shift, tgt.configuration_fk)
VALUES (src.modelid, :log_date, 'OK', src.ts_code, 'Morning', src.configurationid);
这会执行您在循环中重新创建的连接，将其链接回您尝试插入的表，并且仅在表中不存在时才插入一行.
This does the join you were reinventing with your loops, links it back to the table you're trying to insert into, and only inserts a row if it doesn't already exist in the table.
您需要编写 PHP 代码来执行此操作，并将 log_date 作为绑定变量传入.
You would need to write the PHP code to execute this, having passed the log_date in as a bind variable.
通过绑定变量，您可以让数据库跳过硬解析(即找出执行查询的最佳方式)，从而节省时间.
By binding the variable, you allow the database to skip the hard parse (i.e. finding out the best way to execute the query), which saves time.
通过在选择更多数据之前不获取数据并手动循环并确定是否需要进行插入，您可以跳过大量上下文切换和通过网络拉/推数据.让数据库做繁重的工作；这就是它的设计目的！
By not fetching data and manually looping round before selecting more data and working out if you need to do the insert, you skip a whole lot of context switching and pulling/pushing data across the network. Let the database do the heavy lifting; it's what it's designed to do!
这篇关于PHP Oracle 查询 select 语句内循环慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
