PHP w / MS SQL批量插入速度慢

发布时间：2020-12-13 22:50:15 所属栏目：PHP教程来源：网络整理

导读：我在批量插入MSSQLSRV 2008 R2中的临时表时遇到问题. 我正在插入一个包含约200,000行的CSV,大约需要5分钟才能完成. 我尝试使用PDO和sqlsrv驱动程序.它们似乎都表现不佳. 这是代码,以了解我在做什么(我包括SQLSRV和PDO代码)： ... try { //create structure t

我在批量插入MSSQLSRV 2008 R2中的临时表时遇到问题.

我正在插入一个包含约200,000行的CSV,大约需要5分钟才能完成.

我尝试使用PDO和sqlsrv驱动程序.它们似乎都表现不佳.

这是代码,以了解我在做什么(我包括SQLSRV和PDO代码)：

...
    try {
        //create structure table record
        foreach ($mapped_data as $k => $v) {
            $insert .= $k . ",";
            $values .= $v . ",";
        }
        $insert = substr($insert,-1); //remove last,$values = substr($values,-1);
        $tableName = $table;
        if ($stageData) {
            $tableName = "stage_$table";
        }
        if ( $query == "" )
            $query = "INSERT INTO $tableName ($insert) VALUES ";
        $query .= "($values),";

        // Insert in blocks of 1000 lines
        if ($line % 1000 == 0) {
            $log->logInfo("Executing @ line: $line");
            $query = substr($query,$query .= ";";

            // ======================
            // = SQLSRV DRIVER CODE =
            // ======================
            sqlsrv_begin_transaction($sqlsrvConn);
            $queryResult = sqlsrv_query($sqlsrvConn,$query);
            if ($queryResult) {
                sqlsrv_commit($sqlsrvConn);
            } else {
                sqlsrv_rollback($sqlsrvConn);
            }

            // ===================
            // = PDO DRIVER CODE =
            // ===================
            $conn->beginTransaction();
            $res = $conn->prepare($query);
            if($res->execute() === false) {
                $errInfo = $res->errorInfo();
                if ( $conn->inTransaction() ) {
                    $conn->rollback();
                }
                $log->logInfo('Data importing error on line: ' . $line . $errInfo[2]);
                $errors[] = 'Data importing error on line: ' . $line . $errInfo[2];
            } else {
                if ( $conn->inTransaction() ) {
                    $conn->commit();
                    $query = "";
                    $importedRows += ($line - 6) - $importedRows;
                }
            }
        }
    }
    catch (PDOException $e) {
        if ( $conn->inTransaction() ) {
            $conn->rollBack();
        }
        $log->logInfo('PDO Exception: ' . $e->getMessage());
        $errors[] = 'PDO Exception: ' . $e->getMessage();
    }
    $line++;
} // End of while loop through each CSV Line

fclose($handle);
$totalRows = $line - 6;
$importedRows += $totalRows - $importedRows;

// Insert remaing queries afterwards...
...

我一直在寻找可能的解决方案,但一直没有找到任何有效的解决方案.

我找到了this post,它基本上就是说要一起批处理行(我已经完成了).

我发现另一个帖子说PDO,设置connectionpooling = 0.我尝试过,并没有看到性能有任何提高.

有没有其他人使用SQLSRV和PHP遇到这个问题？

干杯,

解决方法

我有一个类似的问题.因为我的问题是缺少可用内存,所以我的服务器不得不花费额外的时间来处理虚拟内存.如果这不是你的问题,我的回答对你没用.

您正在使用字符串连接,后跟substr删除最后一个逗号.当您使用substr时,它会生成该字符串的另一个副本,这对于长字符串来说是内存密集型的.有关字符串很长时会发生什么的示例,请参见this question.当我切换到阵列连接时,由于内存使用率较低,我的速度大大提高.但是,如果您没有内存问题,阵列连接实际上可能是slower.

我看到的其他一些事情是你只需要收集一次$inserts变量,并且一旦你不再需要它们就不会取消大变量.我不知道纠正这种事情是否会对你产生明显的影响.以下是您可以尝试的基本类型的更改：

if(!isset($insert)) {
        $insert = array();
        $collect = true;
    }
    $values = $array();
    foreach ($mapped_data as $k => $v) {
        if(isset($collect)) 
            $insert[] = $k;
        $values[] = $v;
    }
    unset($collect);

    .....
    if(!isset($queryend)) 
         $queryend = array();
    $queryend[] = "(".implode(",",$values).")";

    .....
    $query = "INSERT INTO $tableName ("
             .implode(",$insert)
             .") VALUES "
             .implode(",$queryend);
    unset($queryend);  //always unset big things as soon as possible

    .....  //after $res = $conn->prepare($query);
    unset($query);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!