บันทึกหายไปในวงธุรกรรม PDO mssql

ฉันมีรหัสต่อไปนี้ (มากหรือน้อย) ที่จะนำเข้าจาก 500,000 ถึง 4,000,000 แถว:

$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();

อย่างที่คุณเห็น ฉันคอมมิตทุกๆ 100 บรรทัด และฉันยังเพิ่มโหมดสลีปอีกด้วย ฉันเคยรันการคอมมิตเพียงครั้งเดียวทุกๆ 25,000 บรรทัด และฉันไม่ได้ใช้สลีปเลย อย่างไรก็ตาม เมื่อถึงจุดหนึ่ง ฉันค้นพบว่าฉันไม่มีบันทึก ฉันเริ่มเล่นกับการตั้งค่าเหล่านี้ (สลีปและจำนวนแถว) ด้วยวิธีนี้ ฉันจึงลดจำนวนบันทึกที่หายไปจาก 50,000 รายการเหลือประมาณ 100 รายการ แต่ฉันยังคงขาดบันทึกอยู่! พวกเขากำลังจะไปไหน? ฉันรู้ว่า SQL นั้นใช้ได้ เพราะฉันได้รับข้อผิดพลาดทันทีเมื่อมีบางอย่างผิดปกติ

ฉันคิดว่าฉันสามารถซ้อนเม็ดมีดได้จำนวนมากระหว่างการทำธุรกรรม? การเรียก startTransaction อาจเป็นปัญหาหรือไม่?

อัปเดต:

เงินรางวัลสิ้นสุดลงและฉันต้องให้รางวัล ขอขอบคุณทุกท่านสำหรับคำตอบของคุณ หรือเคล็ดลับจริงๆ เนื่องจากไม่มีใครตอบคำถามของฉันจริงๆ ฉันไม่ได้ขอวิธีแก้ปัญหา แม้ว่าข้อเสนอแนะของคุณจะได้รับการชื่นชมมากก็ตาม คำตอบที่ได้รับค่าหัวเพราะมันใกล้เคียงกับการตอบคำถามของฉันมากที่สุด น่าเสียดายที่มันไม่ได้ผล

ขณะนี้ฉันใช้การนำเข้า CSV จำนวนมาก ซึ่งใช้งานได้ดี แต่ถ้าใครมีเคล็ดลับอื่นๆ ในการแก้ไขปัญหานี้ โปรดแจ้งให้เราทราบ เนื่องจากฉันชอบใช้วิธีเดิมมากกว่า


php sql sql-server transactions pdo
person Derk Arts    schedule 02.07.2012    source แหล่งที่มา
comment
การเรียกใช้โค้ดโดยไม่เริ่มต้นธุรกรรมและการซ้อนแบบสอบถามที่แทรกทั้งหมดในธุรกรรมเดียวส่งผลให้เรกคอร์ดประมาณ 40,000 รายการหายไป...   -  person Derk Arts    schedule 02.07.2012
comment
ถ้าฉันทำซ้ำลูปนี้โดยไม่มีธุรกรรม มันก็ใช้ได้ดี ไม่มีบันทึกสูญหาย...   -  person Derk Arts    schedule 02.07.2012
comment
ปัญหาไม่ได้เกิดจาก PDO นั่นก็แน่นอน   -  person Derk Arts    schedule 02.07.2012
comment
ฉันลอง msdn.microsoft.com/en-us/library/ms188365.aspx เนื่องจากมีข้อมูลจำนวนมากอยู่ที่นั่น   -  person allen213    schedule 02.07.2012
comment
คุณหมายถึงการนำเข้าไฟล์ CSV โดยตรงใน SQL หรือไม่ นั่นหมายความว่าฉันจะต้องอ่านไฟล์ CSV ทำความสะอาด เขียนกลับไปยัง CSV อื่น และแทรกไฟล์นั้นลงใน de DB ฉันสามารถทำเช่นนั้นได้ แต่รู้สึกว่าไม่มีประสิทธิภาพมากนัก   -  person Derk Arts    schedule 02.07.2012
comment
คุณแน่ใจหรือไม่ว่าตัวแยกวิเคราะห์ CSV ที่ผลิตเองที่บ้านของคุณไม่ใช่ปัญหา หากคุณทำให้ปัญหาง่ายขึ้นด้วยการแทรกอาร์เรย์ (a$i, b$i, c$i) สำหรับทุก ๆ $i ใน range(0, 50000) และลบโค้ดที่เป็นปัญหาทั้งหมด (sleeps, try .. catch และคอมมิตระดับกลาง) คุณยังคงสามารถสร้างปัญหาซ้ำได้หรือไม่ ? หากใช่ คุณสามารถลิงก์ไปยังสคริปต์ตัวอย่างสมบูรณ์ ได้หรือไม่   -  person phihag    schedule 04.07.2012
comment
ไม่พบข้อยกเว้นที่เป็นไปได้จาก $pdo->commit(); และ $pdo->beginTransaction(); ด้วยโค้ดดังกล่าว หากฉันอ่านอย่างถูกต้อง   -  person vyegorov    schedule 04.07.2012
comment
Phi:ตามที่ระบุไว้ เมื่อฉันละการทำธุรกรรม ทุกอย่างช้าแต่ก็โอเค ไม่มีบันทึกที่ขาดหายไป ดังนั้นจึงไม่มีปัญหากับตัวแยกวิเคราะห์ CSV ของฉัน VYE: มีข้อยกเว้นอะไรบ้าง? ฉันคิดว่าข้อยกเว้นเหล่านั้นจะเพิ่มขึ้นเมื่อฉันดำเนินการค้นหา นอกจากนี้ เมื่อฉันทำงานช้าลง ฉันจะสูญเสียบันทึกน้อยลง ดังนั้นข้อผิดพลาดที่ตรวจไม่พบใน SQL จึงไม่ใช่ปัญหา   -  person Derk Arts    schedule 04.07.2012
comment
สคริปต์ใน $sSql ไม่มีคำหลัก VALUES คำนั้นหายไปจากสคริปต์ในรหัสการทำงานของคุณหรือไม่?   -  person Andriy M    schedule 05.07.2012
comment
¿กระทำและ startansactioan ผลตอบแทนจริงเสมอหรือไม่? ¿บันทึกที่หายไปมีความสัมพันธ์กัน?   -  person    schedule 07.07.2012

คำตอบ (4)


arrow_upward
1
arrow_downward

คุณเคยพิจารณาใช้ Sprocs แทนการแทรกคำสั่งหรือไม่? การเขียนบันทึกจำนวนเท่าใดก็ได้ตามลำดับ- ทีละรายการ- ถือเป็นการเสียเวลา/พลังงาน.. มันไม่เร็วเท่าที่ควร

คุณแน่ใจหรือว่าไม่สามารถใช้ BULK INSERT หรือ XML แทนเพื่อแทรกหลายแถวพร้อมกันได้

person Aaron Kempf    schedule 05.07.2012
comment
นั่นคือสิ่งที่ฉันกำลังทำอยู่ตอนนี้เพื่อเป็นวิธีแก้ปัญหา แต่ฉันคิดว่ามันแย่มากที่บันทึกหายไปโดยไม่แจ้งให้ทราบล่วงหน้า... - person Derk Arts; 05.07.2012

arrow_upward
3
arrow_downward

ผมมีปัญหานี้มาก่อน. สำหรับฉันฉันต้อง "SET NOCOUNT ON" ก่อน INSERTS เนื่องจาก SQL Server พยายามส่งคืน "เพิ่มหนึ่งแถว" ให้ฉันสำหรับแต่ละ INSERT และคิวข้อความเต็มและเพิ่งหยุดการแทรกข้อมูลโดยไม่ส่งคืนข้อผิดพลาดใด ๆ !

ดังนั้นคุณควรลองทำ "SET NOCOUNT ON" ก่อนใส่ INSERTS ฉันพนันได้เลยว่ามันจะแก้ไขปัญหาของคุณได้

person Danielle Paquette-Harvey    schedule 04.07.2012
comment
นั่นฟังดูเป็นไปได้อย่างยิ่ง! วันนี้จะลองดู! - person Derk Arts; 05.07.2012
comment
ก่อนทุกคำสั่ง insert หรือเพียงครั้งเดียว? - person Derk Arts; 05.07.2012
comment
แก้ไม่ได้อย่างน่าเสียดาย '14:57:10[119] | ผลลัพธ์สำหรับตาราง: บรรทัดทั้งหมด: 466792สำเร็จ: 466789 ล้มเหลว: 2 ' -› select count(*) from table = 441925 - person Derk Arts; 05.07.2012

arrow_upward
3
arrow_downward

คุณใช้โหมดสลีป () 0.15 วินาทีเพื่อชะลอการดำเนินการ อย่างไรก็ตาม คำถาม: จะเกิดอะไรขึ้นหาก INSERT ใช้เวลานานกว่า 0.15 วินาที สคริปต์ที่จะรันกลับและตารางอาจถูกบล็อกเนื่องจากการคอมมิตครั้งก่อน

จากนั้นลองใช้วิธี INSERT หลายรายการในการรันครั้งเดียวในฐานข้อมูล ลองสิ่งนี้:

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');

เพื่อให้บรรลุเป้าหมายนี้ ให้ทำดังนี้

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

แล้ววิ่ง!

person Maykonn    schedule 06.07.2012

arrow_upward
2
arrow_downward

@ซาราติส

คุณได้พิจารณาสร้าง sproc ง่าย ๆ ซึ่งดำเนินการตามที่ต้องการโดยใช้ MERGE หรือไม่? การผสานจะใช้ค่าใช้จ่ายจำนวนมาก อย่างไรก็ตาม ฉันรู้มาโดยตลอดว่ามันเป็นวิธีที่เชื่อถือได้มากในการซิงโครไนซ์บันทึกจากแหล่งข้อมูล 'หลัก' ไปยังแหล่งข้อมูลที่ขึ้นต่อกัน

ฉันมีปรัชญาที่ว่าฐานข้อมูลควรควบคุมวิธีการใช้ข้อมูล และโค้ดควรควบคุมว่าฐานข้อมูลจะดำเนินการเมื่อใด สิ่งที่ฉันชอบทำคือเก็บทุกสิ่งที่แตะข้อมูลไว้ใน proc ที่เก็บไว้ และเรียก proc ที่เก็บไว้ด้วยโค้ดเมื่อมีเงื่อนไข/เหตุการณ์บางอย่างเกิดขึ้น อย่างไรก็ตาม สถานการณ์ของคุณอาจไม่ซ้ำกันมากพอจนไม่ใช่แนวทางปฏิบัติที่ดีที่สุดอย่างแน่นอน

ข้อมูลโค้ดด้านล่างนี้มาจาก Microsoft เพื่อเป็นตัวอย่างของวิธีการผสานให้สำเร็จ:

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;

นี่คือลิงก์ไปยังบทความทั้งหมด ซึ่งครอบคลุมสถานการณ์ต่างๆ เล็กน้อย: http://technet.microsoft.com/en-us/library/bb510625.aspx

ในตอนนี้ หากต้องการรับข้อมูลลงใน SQL Server จาก CSV ลิงก์ต่อไปนี้จะอธิบายวิธีการดำเนินการดังกล่าวโดยใช้พาธของไฟล์ซึ่งเป็นส่วนหนึ่งของคำสั่งย่อย FROM และการระบุตัวคั่นในส่วนคำสั่ง WITH

มันครอบคลุมถึง BULK INSERT ด้วย หากนั่นอาจทำงานได้ดีที่สุดสำหรับคุณ ฉันเป็นส่วนหนึ่งของ MERGE เพราะมันจัดการทั้ง INSERT สำหรับบันทึกใหม่และ UPDATES บันทึกที่มีอยู่ http://sqlserverpedia.com/blog/sql-server-bloggers/so-you-want-to-read-csv-files-huh/

โปรดทราบว่า BULK INSERT ใช้งานได้เฉพาะในกรณีที่ไฟล์อยู่บนดิสก์เดียวกันกับอินสแตนซ์ SQL Server บริษัทของฉันเข้าใจดีว่าไม่อนุญาตให้ฉันเข้าถึงไดรฟ์ในเครื่องของ SQL Server ดังนั้นฉันจะต้องทดสอบสิ่งนี้ที่บ้านคืนนี้เพื่อให้คุณมีตัวอย่างการทำงานเพื่อใช้งาน

person EastOfJupiter    schedule 05.07.2012
comment
ถือว่าดี แต่ฉันไม่คิดว่าจะมีผลกับการนำเข้าไฟล์ CSV หรือฉันเข้าใจผิด - person Derk Arts; 10.07.2012
comment
ฉันขอโทษ ฉันไม่เห็นในโพสต์ต้นฉบับของคุณว่าคุณนำเข้าจาก CSV ลิงค์นี้อาจเสนอวิธีแก้ปัญหา sqlserverpedia.com/blog /sql-server-bloggers/ เลือก CSV ลงใน Common Table Expression จากนั้นทำการผสาน ฉันจะอัปเดตคำตอบเพื่อรวมลิงก์นี้ด้วย - person EastOfJupiter; 10.07.2012