各位前輩們好

我要匯入一個大約有2萬筆的json
用原本CI內建的資料庫處理每秒大約處理29筆資料
大概要10分鐘左右完成超慢，

因為每筆資料的結構一樣，
想改用預備查詢prepare來看看是否能加速
但改預查詢後發現，
每秒只能處理5筆資料 @@

請問我哪邊寫法有問題? 或有什麼可以加速的建議?
另外想外update的預查詢好像不能使用update()方法?
使用update()會變成true...

$pQueryExist = null;
$pQueryUpdate = null;
$pQueryInsert = null;

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的預查詢
    if($pQueryExist == null) {
        $pQueryExist = $db->prepare(function ($db) {
            return $db->table('vsms_scan_copy')->where(['result_id' => '?'], false)->selectCount('result_id', 'cnt')->get();
        });
    }

    if($pQueryInsert == null) {
        $paramOrder = array_merge($vul, ['result_id' => '?', 'updated_at' => '?', 'created_at' => '?']);
        $pQueryInsert = $db->prepare(function ($db) use($paramOrder) {
            $result = $db->table('vsms_scan_copy')->insert($paramOrder);
            return $result;
        });
    }

    if($pQueryUpdate == null) {
        $paramOrder = array_merge($vul, ['updated_at' => '']);
        $pQueryUpdate = $db->prepare(function ($db) use($paramOrder) {

            $result = $db->table('vsms_scan_copy')->where('result_id' , '?', false);
            foreach ($paramOrder as $key => $value) {
                $result->set($key, '?', false);
            }
            $sql = $result->getCompiledUpdate(false);

            // return $result->update(); // 莫名其妙會變成true...有人知道原因嗎?
            return (new Query($db))->setQuery($sql);
        });
    }


    $resultId = $vul['id'];

    // 儲存紀錄
    $exist = $pQueryExist->execute($resultId)->getRow()->cnt;
    if (!empty($exist)){
		// 原本的方法
        // $db->table('vsms_scan')->update(array_merge($vul, ['updated_at' => $vulDate]), ['result_id' => $resultId]); // 'updated_at' => $now

		// 預查詢
        // 產生參數(key名稱要拿掉，只要值的順序一致就好)
        $param = array_values(array_merge($vul, ['updated_at' => $vulDate]));
        array_push($param, $resultId); // result_id
        $pQueryUpdate->execute(...$param);
    }
    else {
        // 原本的方法
        // $db->table('vsms_scan')->insert(array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate])); // , 'updated_at' => $now, 'created_at' => $now

        // 預查詢
        $param = array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]);
        $pQueryInsert->execute(...array_values($param));
    }
}
$pQueryExist->close();
$pQueryUpdate->close();
$pQueryInsert->close();

目前的想法是，
假設慢是慢在CI封裝處理這些SQL語法，
那我事先產生好值是問號的SQL語法，
後續帶參數的方式處理
像這樣

$db->query(
  "SELECT count(1) AS cnt FROM vsms_scan_copy WHERE result_id=? AND scan_no=? AND status=?",
  [$resultId, $vul['scan_no'], $vul['status']]
)

20210916
結合 海綿寶寶 的建議
因為大部分的資料是更新，
所以直接更新，沒更新再新增~~
並且只在第一次的時候產生SQL語法，
之後就重複使用SQL，
只替換掉值(好像有個專有名詞，好像不是叫封裝? 不重要XD)，
後來發現花費時間從原本的 10多分鐘 直接降到 3分半 !!!
果然主要慢在CI動態產生SQL語法~~

$pQueryUpdate = null;
$pQueryInsert = null;

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的SQL
    if (empty($pQueryUpdate)) {
        $param = array_merge($vul, ['updated_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryUpdate = $db->table('vsms_scan')
            ->where(['result_id' => ':result_id:'])
            ->set($param)
            ->getCompiledUpdate();
		// 去掉值的前後單引號
        $pQueryUpdate = str_replace("':", ':', str_replace(":'", ':', $pQueryUpdate));
    }

    if (empty($pQueryInsert)) {
        $param = array_merge($vul, ['result_id' => '', 'updated_at' => '', 'created_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryInsert = $db->table('vsms_scan')
            ->set($param)
            ->getCompiledInsert();
		// 去掉值的前後單引號
        $pQueryInsert = str_replace("':", ':', str_replace(":'", ':', $pQueryInsert));
    }


    $resultId = $vul['id'];

    // 直接更新，更新失敗就改新增
    if ( 
		!$db->query($pQueryUpdate, array_merge($vul, ['updated_at' => $vulDate, 'result_id' => $resultId]) ||
        $db->affectedRows() == 0
	){
         $db->query($pQueryInsert, array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]));
    }
}

換算下來CI動態產生一次SQL大約要7.5ms
這次的時間差大約7分半即450秒
一輪跑3次SQL (含沒列出的log)
(450 / 3) / 20000 = 0.0075秒 = 7.5ms
(大概是已關閉apache、php、mysql的快取，並開啟CI的開發模式的關係所以特別慢?)
果然方便就是有代價的XD

周末再來進行優化
依各位前輩的建議，
改用批次處理，
看速度還能提升多少

20210930

因為先判斷更新有無成功後才能知道要不要新增，
因此只有新增才有機會改批次處理

結果有點意外，反而變慢了40秒左右，總花費時間 04分15秒
實際新增資料大約1萬上下筆資料，
剩下的都是更新

$pQueryUpdate = null;
$insertParams = []; // 新增的參數

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的SQL
    if (empty($pQueryUpdate)) {
        $param = array_merge($vul, ['updated_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryUpdate = $db->table('vsms_scan')
            ->where(['result_id' => ':result_id:'])
            ->set($param)
            ->getCompiledUpdate();
		// 去掉值的前後單引號
        $pQueryUpdate = str_replace("':", ':', str_replace(":'", ':', $pQueryUpdate));
    }


    $resultId = $vul['id'];

    // 直接更新，更新失敗就改新增
    if ( 
		!$db->query($pQueryUpdate, array_merge($vul, ['updated_at' => $vulDate, 'result_id' => $resultId]) ||
        !$db->affectedRows()
	){
         $insertParams[] = array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]);
    }
}

$db->table('vsms_scan')->insertBatch($insertParams);

最後默默的回到上個版本...