title: PHP w/ MS SQL slow on bulk insert
body: <p>I am having an issue with bulk inserting into a staging table in MSSQLSRV 2008 R2.</p>
  
  <p>I am inserting a CSV with ~200,000 lines and it is taking roughly 5 minutes to complete.</p>
  
  <p>I tried using both PDO and the sqlsrv driver. They both seem to yield poor performance.</p>
  
  <p>Here is the code to give an idea of what I am doing (I included both SQLSRV and PDO code):</p>
  
  
  
  <pre class="lang-php prettyprint-override"><code>...
      try {
          //create structure table record
          foreach ($mapped_data as $k =&gt; $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-&gt;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-&gt;beginTransaction();
              $res = $conn-&gt;prepare($query);
              if($res-&gt;execute() === false) {
                  $errInfo = $res-&gt;errorInfo();
                  if ( $conn-&gt;inTransaction() ) {
                      $conn-&gt;rollback();
                  }
                  $log-&gt;logInfo('Data importing error on line: ' . $line . $errInfo[2]);
                  $errors[] = 'Data importing error on line: ' . $line . $errInfo[2];
              } else {
                  if ( $conn-&gt;inTransaction() ) {
                      $conn-&gt;commit();
                      $query = "";
                      $importedRows += ($line - 6) - $importedRows;
                  }
              }
          }
      }
      catch (PDOException $e) {
          if ( $conn-&gt;inTransaction() ) {
              $conn-&gt;rollBack();
          }
          $log-&gt;logInfo('PDO Exception: ' . $e-&gt;getMessage());
          $errors[] = 'PDO Exception: ' . $e-&gt;getMessage();
      }
      $line++;
  } // End of while loop through each CSV Line
  
  fclose($handle);
  $totalRows = $line - 6;
  $importedRows += $totalRows - $importedRows;
  
  // Insert remaing queries afterwards...
  ...
  </code></pre>
  
  <p>I've been scouring the internet looking for possible solutions but haven't been able to find anything that worked. </p>
  
  <p>I've found <a href="https://stackoverflow.com/questions/4659317/bulk-parameterized-inserts">this post</a> which basically says to batch rows together (which I've already done).</p>
  
  <p>And I found another post that said for PDO, to set connectionpooling=0. I tried that and didn't see any increase in performance.</p>
  
  <p>Has anyone else run into this issue with SQLSRV and PHP?</p>
  
  <p>Cheers,</p>