PHP с MS SQL медленный при массовой вставке

У меня проблема с массовой вставкой в ​​промежуточную таблицу в MSSQLSRV 2008 R2.

Я вставляю CSV с ~ 200 000 строк, и это занимает примерно 5 минут.

Я пытался использовать как PDO, так и драйвер sqlsrv. Они оба, кажется, дают плохую производительность.

Вот код, дающий представление о том, что я делаю (я включил код SQLSRV и PDO):

...
    try {
        //create structure table record
        foreach ($mapped_data as $k => $v) {
            $insert .= $k . ",";
            $values .= $v . ",";
        }
        $insert = substr($insert, 0, -1); //remove last ,
        $values = substr($values, 0, -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, 0, -1); //remove last ,
            $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...
...

Я искал в Интернете возможные решения, но не смог найти ничего, что сработало.

Я нашел это сообщение, в котором в основном говорится о группировании строк (что я уже сделал).

И я нашел еще один пост, в котором говорилось, что для PDO нужно установить connectionpooling=0. Я попробовал это и не увидел никакого увеличения производительности.

Кто-нибудь еще сталкивался с этой проблемой с SQLSRV и PHP?

Ваше здоровье,


php sql sql-server sqlsrv pdo
person Gimli    schedule 07.10.2013    source источник
comment
+1 за использование PDO, но вы должны использовать подготовленные операторы, чтобы быть более защищенными.   -  person bksi    schedule 07.10.2013
comment
Сервер базы данных работает в той же сети, что и ваш PHP-сервер? Возможно, у вас проблемы с пропускной способностью?   -  person MonkeyZeus    schedule 07.10.2013
comment
Пробовали ли вы тестировать свой сценарий, чтобы увидеть, действительно ли замедление связано с INSERT?   -  person MonkeyZeus    schedule 07.10.2013
comment
БД и PHP работают на моей локальной машине разработки (которая является довольно мощной машиной). Я не пробовал тестировать скрипт. Попробую это   -  person Gimli    schedule 07.10.2013
comment
Насколько велика каждая запись? Какая еще нагрузка на ваш сервер? Насколько хороша пропускная способность вашего диска при записи? Есть ли много больших полей для индексации? Вы привязаны к процессору? Думали ли вы, что вставка 200 000 записей с помощью метода любой может занять 5 минут?   -  person Sammitch    schedule 07.10.2013
comment
@Gimli Пожалуйста, опубликуйте свои результаты, когда у вас будет время. Возможно, мы сможем внести предложения, если увидим, какие разделы/блоки кода работают медленно. Спасибо   -  person MonkeyZeus    schedule 08.10.2013

Ответы (1)


arrow_upward
1
arrow_downward

У меня была несколько похожая проблема. Поскольку моей проблемой была нехватка доступной памяти, моему серверу приходилось тратить дополнительное время на обработку виртуальной памяти. Если это не ваша проблема, то мой ответ вам не пригодится.

Вы используете конкатенацию строк, за которой следует substr, чтобы удалить последнюю запятую. Когда вы используете substr, он создает еще одну копию строки, которая интенсивно использует память для длинных строк. См. этот вопрос для примера того, что происходит, когда строки длинные. Когда я переключился на конкатенацию массивов, моя скорость значительно увеличилась из-за меньшего использования памяти. Однако, если у вас нет проблем с памятью, объединение массивов на самом деле может быть медленнее для вас.

Еще пару вещей, которые я увидел, заключались в том, что вам нужно собрать переменную $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);
person miyasudokoro    schedule 07.10.2013
comment
Память не является проблемой на моей машине, но также не помешает сделать ее более эффективной. Я попробую это и посмотрю, поможет ли это. Спасибо! - person Gimli; 08.10.2013