Entries disappearing in mssql PDO transaction cycle

Question

I have the following code (more or less) for importing anywhere from 500,000 to 4,000,000 lines:

$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 commit every 100 lines, and I even added a bit of sleep. I used to execute commit only once every 25,000 lines, and I did not use sleep. However, at some point I found that I had no records. I started playing with these settings (sleep and row count). Thus, I reduced the number of missing entries from 50,000 to 100. But I still have not enough entries! Where are they going? I know that SQL is fine, because I get errors right away when something is wrong.

I thought I could lay out a lot of attachments during a transaction? Can a beginTransaction problem be a problem?

UPDATE:

The reward is over, and I had to reward it. Thank you all for your answers. Or advice in fact, since none of you answered my question. I did not ask for a workaround, although your suggestions are much appreciated. The answer to the award was awarded for receiving it, because it turned out to be closest to my answer. Unfortunately, this did not work.

Right now I'm using CSV import, which works fine, but if anyone has any other tips to fix this problem, let me know. Since I prefer to use my original method.

Answer 1

Do you consider using Sprocs instead of insert statements? recording ANY number of records sequentially - one at a time - is a waste of time / energy. It's just not as fast as it should be.

Are you sure you cannot use BULK INSERT or XML instead of inserting multiple rows at a time?

Answer 2

I had this problem before. For me, I had to do “SET NOCOUNT ON” before INSERTS, because SQL Server was trying to return “One Added Row” for each INSERT, and the message queue was full and it just stopped inserting data without returning any errors!

So, you should definitely try to do "SET NOCOUNT ON" before the INSERTS. I bet this will solve your problem.

Answer 3

You use sleep () 0.15 seconds to delay execution, however, the question is: What happens if INSERT takes more than 0.15 seconds? script to return and the table may be locked due to a previous commit.

Then try using multiple INSERTs in one 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 do 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!

Answer 4

@Saratis,

Have you considered creating a simple sproc that performs the desired action with MERGE? However, merging will require significant overhead, but I always knew that this is a very reliable way to synchronize records from the "main" data source with a dependent data source.

I adhere to the fact that the database must control the HOW data, and the code must control WHEN the database does what it does. I prefer to store everything related to data in a stored procedure, and call stored procs with code when certain conditions / events occur. However, your situation may be unique enough, which is not really best practice.

The following is a code snippet from Microsoft as an example of how to merge:

 MERGE Production.UnitMeasure AS target USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name) ON (target.UnitMeasureCode = source.UnitMeasureCode) WHEN MATCHED THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name) OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable; 

Here is a link to the entire article, which covers several different scenarios: http://technet.microsoft.com/en-us/library/bb510625.aspx

Now, to retrieve information in SQL Server from CSV, the following link explains how this can be achieved by using the file path as part of the FROM clause and specifying the delimiter in the WITH clause.

It also covers BULK INSERT if this might work best for you, however I am partly in MERGE because it handles both INSERT for new records and UPDATES for existing records. http://sqlserverpedia.com/blog/sql-server-bloggers/so-you-want-to-read-csv-files-huh/

FYI, BULK INSERT only works if the files are located on the same drives as the instance of SQL Server. My company, for obvious reasons, will not give me access to the local disks of SQL Server, so I will have to test this at home tonight to prepare a working example.