PHP w/ MS SQL slow on bulk insert -

i having issue bulk inserting staging table in mssqlsrv 2008 r2.

i inserting csv ~200,000 lines , taking 5 minutes complete.

i tried using both pdo , sqlsrv driver. both seem yield poor performance.

here code give idea of doing (i included both sqlsrv , pdo code):

...     try {         //create structure table record         foreach ($mapped_data $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 $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... ... 

i've been scouring internet looking possible solutions haven't been able find worked.

i've found this post says batch rows (which i've done).

and found post said pdo, set connectionpooling=0. tried , didn't see increase in performance.

has else run issue sqlsrv , php?

cheers,

i had similar issue. because problem lack of available memory, server kept having take time deal handling virtual memory. if that's not problem, answer not useful you.

you using string concatenation followed substr remove last comma. when use substr, makes copy of string, memory-intensive long strings. see this question example of happens when strings long. when switched array concatenation, speed increased due lower memory usage. however, if don't have memory issues, array concatenation slower you.

a couple of other things saw need collect $inserts variable once, , not unsetting big variables no longer need them. don't know if correcting kind of thing make appreciable difference or not. here basic kinds of changes try:

    if(!isset($insert)) {         $insert = array();         $collect = true;     }     $values = $array();     foreach ($mapped_data $k => $v) {         if(isset($collect))              $insert[] = $k;         $values[] = $v;     }     unset($collect);      .....     if(!isset($queryend))           $queryend = array();     $queryend[] = "(".implode(",",$values).")";      .....     $query = "insert $tablename ("              .implode(",",$insert)              .") values "              .implode(",", $queryend);     unset($queryend);  //always unset big things possible      .....  //after $res = $conn->prepare($query);     unset($query);