Records disappearing in PDO mssql transaction loop

Question

I have the following code (more or less) to import anywhere from 500.000 to 4.000.000 rows:

$sSql = "Insert into table (a,b,c) VALUES(?,?,?)"
$oSQLStmnt = $pdo->prepare($sSql);
$oSQLStmnt->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);
if (!$oSQLStmnt) {
    echo $pdo->errorInfo(); // Handle errors
}
$pdo->beginTransaction();
$iLineCounter = 1;
while (($sLine = fgets ($oCSV, 8000)) !== FALSE) {
      $aLine = explode('|', $sLine); //Fgetscsv did not work properly 
       if ($iLineCounter % 100 == 0) {
            lo("Inserting row " . $iLineCounter);
            $pdo->commit();
            sleep(0.15);
            $pdo->beginTransaction();
       }
       try {
            $oSQLStmnt->execute($aLine);
            $iSuccesulInserts++;
       }
       catch (exception $e) {
            print_r($e);
            $iFailedInserts++;
       }

       $iLineCounter++;
}
$pdo->commit();

As you can see, I perform a commit every 100 lines, and I even added some sleep. I used to run the commit only once every 25.000 lines, and I did not use any sleep. However, at one point, I discovered I was missing records. I started playing with these settings (sleep and number of rows). This way I reduced the number of missing records from 50.000 to about a 100. But I'm still missing records! Where are they going? I know the SQL is ok, because I immediately receive errors when somethings wrong there.

I thought I could stack a lot of inserts during a transaction? Could calling beginTransaction be a problem?

UPDATE:

The bounty ended and I had to award it. Thank you all for your answers. Or tips actually, as none of you actually answered my question. I was not asking for a workaround, although you suggestions are much appreciated. The answer the bounty was awarded to received it because it came closest to actually answering my question. Unfortunately it did not work.

For now I'm using CSV bulk import, that works fine, but if anyone has any other tips for fixing this issue, please let me know. As I prefer using my original method.

Answer 1

I had this problem before. For me, I had to do a "SET NOCOUNT ON" before the INSERTS because SQL Server was trying to return me "One row added" for each INSERT and it message queue was full and it just stoped inserting data, without returning any errors!

So you should definitely try to do a "SET NOCOUNT ON" before the INSERTS. I bet it's gonna fix your problem.

Answer 2

You use sleep () 0.15 seconds to delay the execution, however, question: What happens if the INSERT take longer than 0.15 seconds? The script to run back and the table may be blocked because of previous commit.

Then try an approach of multiple INSERT's in a single run in the database. Try something like this:

INSERT INTO example (example_id, name, value, other_value)VALUES
(100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4');

To achieve this, do:

$sql = ' INSERT INTO example (example_id, name, value, other_value)VALUES';
while (($sLine = fgets ($oCSV, 8000)) !== FALSE) {
    // generate VALUES to INSERT in a $sql .= '(..., ..., ...),'
}

And then run!