FireDAC: Bulk-Insert באמצעות Array DML וטיפול שגיאות נקי לכל שורה

Bulk-Insert עם BDE-Ablosung mit nativer Anbindung Array DML הוא לעתים הדרך המהירה ביותר להכניס כמות גדולה של רשומות למסד נתונים: במקום אלף INSERTים נפרדים מקשרים מערך פרמטרים ושולחים אותו בבת אחת לשרת. בפועל הבעייה מתגלה מהר: רשומה מפרה Unique-Index, שדה NOT NULL ריק, Foreign Key לא תואם — ופתאום לא ברור איזו שורה הרסה את ה-batch, האם חלק כבר נכתב ואיך להמשיך בצורה נקייה מבלי ליצור חוסר עקביות בנתונים.

זה בדיוק מה שמוסבר כאן: איך להשתמש ב-Array DML כך שתקבל לפי שורה מידע שגיאה מהימן, תשמור שליטה על הטרנזקציה ובתפעול תהיה יכולת לעקוב אחרי מה שקרה. המוקד אינו על קריאת תיעוד API אקדמי, אלא על מקרה קצה שמופיע באופן קבוע בייבוא אמיתי: אצווה גדולה, מעט שורות פגומות, אבל אתה רוצה לשמור על מהירות.

FireDAC Bulk-Insert mit Array DML: למה Array DML משתלם בכלל בביצוע Bulk-Insert

Array DML (Data Manipulation Language) פירושו ב-FireDAC: אתה קושר פרמטרים לא כערך יחיד אלא כמערך. FireDAC שולח אז (תלוי בדרייבר/DB) פחות Roundtrips, יכול לעבוד בצורה יעילה יותר בצד השרת ומצמצם באופן דרסטי את ה-overhead בצד הלקוח. זה רלוונטי במיוחד בשלוש סיטואציות:

• ETL- und Importstrecken: CSV/XML/JSON נכנסים, נירמול/מיפוי, ואז לטבלת Staging או לטבלת יעד.
• Schnittstellen-Puffer: REST- oder MQ-Payloads נאספים ונשמרים באופן תקופתי.
• Protokoll-/Event-Tabellen: הרבה INSERTים קטנים, שבהם השיהוי (latency) שולט.

הרווח אינו בחינם. עם Array DML אתה מעביר את המורכבות מ“הרבה פקודות בודדות“ אל „פקודה אחת עם שורות רבות“. זה טוב לביצועים, אך תובעני יותר בכל הנוגע לאבחון שגיאות, לוגיקת טרנזקציות והרצה מחדש.

מקרה קצה טיפוסי: אצווה אחת, שורה פגומה

התסריט הקלאסי בתפעול: אתה מייבא 50,000 שורות. אתה בוחר ArraySize של 1,000 כי אינך רוצה Roundtrip עבור כל שורה. אצווה 17 נכשלת. מסד הנתונים מדווח רק „duplicate key“ או „violates foreign key constraint“. בממשק המשתמש או ב- Service-Log לעתים נרשם רק: „ExecSQL failed“.

ללא ניהול שגיאות מסודר קורים בדרך כלל שני דברים רעים:

• אתה זורק את כל האצווה, למרות ש-999 מתוך 1,000 השורות היו תקינות.
• אתה חוזר ל-אינדיבידואל-INSERTים ומאבד את יתרון הביצועים לצמיתות.

המטרה היא דרך שלישית: לשמור על ביצועי ה‑Batch, אך לתעד את הכשל בדיוק (אינדקס השורה, ערכי מפתח, טקסט שגיאת DB) ולבצע אופציונלית commit ל־„שורות תקינות“ — תלוי עד כמה קריטית העקביות והאידמפוטנטיות (הרצה חוזרת ללא השפעה כפולה) בתהליך שלך.

FireDAC Array DML: הפרמטרים הרלוונטיים (ללא מיתוסים)

לעבודה עם Bulk-Insert ב־Array DML, בפרקטיקה אותם פרמטרים תמיד קובעים:

1) ArraySize und Batch-Größe

ArraySize (ב־TFDQuery/TFDCommand) קובע כמה „שורות“ FireDAC יעובדו בקריאה אחת. גדול לא אוטומטית טוב. גדול מדי אומר: יותר זיכרון בצד הלקוח, יותר payload ברשת, נעילות/עומס לוג גדולים יותר בשרת ובמקרה של שגיאה רדיוס הפגיעה („Blast Radius“) גדול יותר. עבור ייבואים עמידים תדירות, לעתים קרובות גודל ה‑Batch בין 200 ו־2.000 הוא נקודת התחלה טובה, בהתאם למספר העמודות, BLOBים והשהייה (latency).

2) Transaktionsgrenze

אתה צריך החלטה ברורה: Commit לכל Batch או Commit לכל הייבוא. זו לא העדפה, אלא החלטת תפעול:

• Commit לכל Batch: מגביל נעילות ו־transaction log, מקל על הרצה חוזרת, אך מצבי ביניים נראים (תלוי ברמת בידוד). שגיאה ב־Batch 17 תשאיר את Batch 1–16 במערכת.
• Commit בסיום: «הכל או כלום», עקבי יותר במובן הפונקציונלי, אבל בכמויות גדולות אתה מסתכן בנעילות ארוכות, Rollback גדול ובמקרה של שגיאה הכל הולך לאיבוד.

עבור רבים מתהליכי הממשק והייבוא, «Commit לכל Batch» היא אסטרטגיית התפעול הריאליסטית יותר — אבל רק אם טיפלת באידמפוטנטיות ובאסטרטגיית כפילויות באופן מסודר (למשל באמצעות מפתחות טבעיים, Upserts או Import‑ID).

3) UpdateOptions und Prepared Statements

כאשר מריצים Batches חוזרים, כדאי להשאיר את ה‑statement מוכן. „Prepare“ פירושו: FireDAC מאפשר ל־DB לפרסר/לקמפל את ה‑statement ולשימוש חוזר. בהתאם ל־DB זה יכול להשפיע במידה ניכרת, בעיקר בתדירות גבוהה. החשוב כאן הוא פחות „טריק 17“ ויותר: שימוש עקבי באותו אובייקט Query (או באותו TFDCommand) וסוגי פרמטרים יציבים.

טיפול שגיאות נקי לכל שורה: מה שבאמת צריך

אם אתה רוצה לטפל בשגיאות „לפי שורה“, אתה צריך שלושה דברים:

1. מיפוי: איזה אינדקס במערך (0..N-1) נכשל?
2. הקשר: אילו ערכי מפתח עסקיים יש לשורה זו (למשל ID חיצוני, מספר לקוח, חותמת זמן)?
3. החלטה: מה אתה עושה לאחר מכן? לבטל הכל, לדלג רק על שורות שגויות, או לפצל את ה‑Batch?

FireDAC יכול בהתאם לדרייבר להחזיר שגיאות לכל אלמנט במערך. בפועל זה לא תמיד „מובנה תמיד“. צריך לצפות שחלק מבסיסי הנתונים/הספקים מדווחים רק על השגיאה הראשונה, או שבמקרה של שגיאה בתוך ה‑Batch השאר לא יבוצעו. בדיוק לכן דפוס עמיד בדרך כלל הוא דו‑שלבי:

• שלב A: נסה להריץ את ה‑Batch כ‑Array DML.
• שלב B: אם ה‑Batch נכשל, חלק אותו (לחצי) או נפל מבוקר לשורות יחידות — אך רק עבור אותו Batch — ורשום לוג מסודר.

זה נשמע כמו עבודה נוספת, אך במסלולי ייבוא זה ההבדל בין «ב־02:00 בלילה הכל נעצר» לבין «הייבוא ממשיך, 7 שורות נחתו ברשימת השגיאות».

תבנית פרקטית: Batch קודם, ואז בידוד ממוקד

התבנית הבאה הוכחה כיעילה בפתרונות תוכנה הקרובים לתהליך, שבהם איכות הנתונים מעורבת:

שלב 1: לארוז נתונים במבנה אצווה (כולל הקשר שגיאה)

שמור את הנתונים שיש לייבא לא רק כערכים גולמיים, אלא עם הקשר מינימלי: ID חיצוני, מספר שורה מהמקור, ואולי Hash/Checksumme. זה לא „Nice to have“: במקרה של שגיאה אינך רוצה לפרסר מחדש את ה-CSV כדי לגלות מה התקלקל.

שלב 2: Array DML ausführen

אתה מגדיר את ArraySize לאורך האצווה, קושר פרמטרים כמערכים ומריץ ExecSQL. חשוב: שמור על יציבות סוגי הפרמטרים (למשל בשדות מספריים אל תקשר לפעמים כ-String ולפעמים כ-Integer), אחרת ה-DB ייצור המרות מרמזות או FireDAC יצטרך להמיר עבור כל פריט.

שלב 3: Fehlerfall – Batch eingrenzen statt blind wiederholen

אם ExecSQL נכשל, יש לך שתי אפשרויות מוצקות:

• Binary Split (לחלק לחצאים): חלק את האצווה לשתי חצאים, נסה כל חצי שוב כ-Array DML. חזור על כך עד שתגיע לכמות קטנה שניתן לבדוק פריט-אחר-פריט. יתרון: שומר על ביצועים גבוהים אם רק כמה שורות פגומות. חסרון: דורש לוגיקה נוספת, ובמקרים של שגיאות שיטתיות (למשל סוג נתונים שגוי) זה לא עוזר הרבה.
• Fallback auf Einzelzeilen עבור אצווה זו: הגדר ArraySize=1 (או קשר ערכי יחיד) והריץ שורה-אחר-שורה, רשם שגיאות והמשך. יתרון: פשוט, מובטח לכל שורה. חסרון: באצווה זו תאבד מהקצב.

בעבודה שגרתית אני משלב את שניהם: קודם מפצל 1–2 פעמים (כדי להעביר „בלוקים טובים“ במהירות), ואז כשהמיסת השאריות קטנה עוברים לשורות בודדות כדי לרשום מידע שגיאה ברור.

Fehlerobjekte und Meldungen: Was du aus FireDAC herausziehen solltest

FireDAC עוטף שגיאות DB בתוך Exceptions (טיפוסי EFDDBEngineException) עם פרטי מידע. לתפעול שלוש רמות חשובות:

• DB-Fehlercode (תלוי בבסיס הנתונים): למשל SQLSTATE ב-PostgreSQL, Error Number ב-SQL Server.
• Constraint-/Objektname: מופיע לעיתים קרובות בטקסט השגיאה (Unique-Index, FK-Constraint).
• Statement-Kontext: טבלה, פעולה, ובמידת הצורך ערכי פרמטרים (זהיר ביחס לנתונים אישיים).

אם אתה רוצה לרשום לוג לכל שורה, עליך במקרה שגיאה גם לזהות את השורה. FireDAC עשוי במקרים מסוימים לספק את ה-Array-Index. אל תסתמך רק על זה. תמיד בנה גם אינדקס משלך (מיקום באצווה) ורשום למיקום זה לפחות מפתח מקצועי יחיד.

Fallstricke, die in echten Imports Zeit kosten

1) „Es war doch nur eine Zeile“ – aber die Transaktion ist schon „dirty“

תלוי ב-DB ובדרייבר, שגיאה יכולה לגרום לכך שכל ביצוע ה-statement יסומם ככשל והטרנזקציה תימצא במצב שבו עליך לבצע במפורש rollback או שבו הצהרות נוספות ייכשלו. במיוחד אצל חלק מהדרייברים ההנחה „לאחר שגיאה פשוט להמשיך“ אינה בטוחה.

ההשלכה: אם אתה עובד בתוך טרנזקציה ואצווה נכשלת, הדרך הסטנדרטית היא: Rollback של הקונטקסט של האצווה הנוכחית (או של כל הטרנזקציה) ואז להתחיל מחדש. זה מתאים טוב לגישה של „Commit pro Batch“.

2) Autocommit vs. explizite Transaktion

אם אינך פותח טרנזקציה מפורשת, פעמים רבות הדרייבר/ה-Provider מחליט כיצד הוא עושה commit ל-statements. עבור ייבויים בכמויות גדולות זה לעיתים רחוק מהנדרש. טרנזקציות מפורשות נותנות לך שליטה על:

• משך נעילה
• התנהגות Rollback
• נקודות ריצוץ מחדש

וגם: מפורש לא אומר „עסקה ענקית“. זה אומר „מודע“.

3) טריגרים, אילוצים ותופעות לוואי

Array DML מאיץ את העברת הנתונים, אבל לא בהכרח את העבודה בצד השרת. אם בטבלת היעד יש לך טריגרים (למשל Audit-Logging, חישוב סטטוס אוטומטי), יתכן שהצוואר בקבוק בכלל לא הוא ה-INSERT, אלא קוד הטריגר. במקרה כזה יכול מגדל לצמצם Roundtrips, אך ה-CPU של שרת ה-DB נשאר הגורם המגבּיל.

למנהלי מערכת ואחראים טכניים: במקרה של בעיות ביצועים שווה לעיין ב-Wait Events/Locks וביומן הטרנזקציות. ה-Bulk-Insert אז רק מהווה את המניע, לא את הסיבה.

4) סוגי נתונים והמרות מרומזות

אחד הסיבות השכיחות ל“למה זה איטי?“: פרמטרים נקשרים כמחרוזת, וה-DB עושה cast לכל שורה ל-Integer/Date/Decimal. זה לא נראה לעין, אבל יקר. עבור ביצועים יציבים:

• הגדר את סוגי הנתונים של הפרמטרים כראוי (תאריך כתאריך, מספר כמספר).
• שימת לב לפחמי Locale עבור עשרונים (פסיק מול נקודה). FireDAC נכון כאן ברוב המקרים, אבל מקורות מעורבים עלולים לשבור את זה.
• ברר מראש אסטרטגיית אזורי זמן/UTC (טיימסטמפים הם קלאסיקה בייבוא).

5) טקסטי שגיאה מיועדים לאנשים, לא לאוטומציה

מפתה לפענח את טקסט השגיאה („duplicate key value violates unique constraint …“). עשה זאת רק כאופציה אחרונה. עדיף קודים מובנים (SQLSTATE, מספר שגיאה). לצערנו לא כל הדרייברים מספקים את הכול באותה איכות. תכנן לכן גם וגם: קוד וגם טקסט, בתוספת אופציונלית של „שם האילוץ מתוך הטקסט“, אבל בלי תלות קשיחה.

הערות לדיבוג: כך תמצא במהירות את השורה התקולה

להפוך את ה-Batch לשחזרי

אם ייבוא נכשל בצורה ספורדית, אתה צריך לשחזור. שמור עבור כל Batch קובץ אבחון קטן או רישום יומן שמכיל:

• מספר ה-Batch וזמן
• ArraySize ומצב הטרנזקציה
• רשימת המפתחות העסקיים (למשל מזהי חוץ) שב-Batch

זה לרוב מספיק כדי להריץ בדיעבד Mini-Import ממוקד רק לאותם מזהים.

להראות את ה-SQL הסופי (אבל בלי דליפות נתונים)

בדיבוג תרצה לדעת: האם ה-SQL תקין? האם הפרמטרים נכונים? FireDAC מספק ניטור/Tracing דרך רכיבי FDMoni ורישום דרייבר. בסביבות קרובות לפרודקשן חשוב:

• להפעיל Tracing באופן ממוקד וזמני בלבד (השפעה על ביצועים ופרטיות).
• לרשום ערכי פרמטרים רק בסביבה מאובטחת או בצורה מוסתרת/ממוּסכת.
• לגבי נתונים אישיים: ביומן רק מפתחות טכניים (IDs) ולא תוכן בטקסט גלוי.

אם אתה מבצע חלוקה לבדיקה: הגדירו קריטריוני עצירה

בחלוקה בינארית אתה לא רוצה לחלוק עד אין סוף. קבע סף תחתון, למשל „מתחת ל-20 שורות מעבר למצב יחיד“. וקבע גבול כמה שגיאות אתה סובל בסך הכל לפני שבוטל הייבוא (למשל במקרה של בעיות ממפות מערכתיות). אחרת תגיע לרשימות שגיאות אינסופיות ותחסום את העיבוד הבא.

מתי המאמץ באמת שווה את זה (ומתי לא)

Array DML עם טיפול בשגיאה פר שורה שווה את זה במיוחד כאשר:

• מעובדות שורות רבות (אלפים עד מיליונים).
• מספר קטן של שורות שגויות, אבל אתה רוצה שהעיבוד ימשיך בכל מקרה.
• הייבוא חייב לרוץ בצורה יציבה בתפעול (למשל עיבוד לילה, שירות ללא UI).
• אתה צריך להחזיר רשימת שגיאות למחלקת התחום/למקור (עם התייחסות לשורות).

זה פחות משתלם כאשר:

• כותבים רק כמה עשרות שורות (הכנסת רשומה בודדת מקובלת),
• איכות הנתונים כל כך גרועה ש-30–50% מהשורות נכשלות (אז אסטרטגיית Staging תהיה מועילה יותר),
• אתה בכל מקרה משתמש בתהליך Bulk-Load מקומי למסד הנתונים (למשל COPY ב-PostgreSQL, BCP/BULK INSERT ב-SQL Server) – אז Array DML אינו הכלי המתאים.

ארכיטקטורה חלופית: טבלת Staging במקום „ישירות ליעד“

אם אתה מתמודד באופן קבוע עם איכות נתונים מעורבת, הכנסת „Insert ישירות לטבלת היעד“ נקודתית היא לעתים ההחלטה השגויה. טבלת Staging (שלב מקדמי) היא טבלה שבה מאחסנים את הנתונים תחילה בצורה תקנית טכנית (במידת הצורך עם טיפוסים גמישים), ורק לאחר מכן מאמתים ומעבירים לטבלת היעד.

יתרונות בתפעול:

• רשומות שגויות נשמרות באופן שניתן לעקוב אחריו (כולל נתוני גלם).
• ניתן להריץ את תהליך האימות בנפרד ובאופן שניתן לחזור עליו.
• מופרדת קבלת הנתונים מהממשק מעיבוד התוכן העסקי.

Array DML מהווה לעיתים את הדרך המהירה אל טבלת ה-Staging, בעוד שהעברת הנתונים לטבלת היעד מתבצעת כ-SQL מבוסס-קבוצות (או Stored Procedure). זה מעביר את טיפול השגיאות לצד מסד הנתונים, מה שיכול להיות הגיוני או בלתי רצוי בהתאם לארגון (תפקידי DBA, Deployment).

תפעול וניהול: מה שמנהלי IT ומנהלי מערכת צריכים לדעת

ניטור: שיעור השגיאות והתפוקה הם המדדים המרכזיים

לתפעול יציב של Bulk-Import שני מדדים אלה משמעותיים יותר מאשר „משך ריצה“ בלבד:

• תפוקה: שורות לדקה (או לאצווה) כולל שיא/חציון.
• שיעור שגיאות: שורות שגויות לכל ריצה, באופן אידיאלי מקובצות לפי קטגוריות שגיאה (Unique, FK, NOT NULL, קונפליקט טיפוס).

אם תעקוב באופן קבוע אחרי שני המדדים האלה, תזהה מוקדם אם משהו השתנה במקור (למשל פורמט חדש) או אם מערכת היעד (למשל Constraints חדשים) הפכה לנוקשה יותר.

נעילות וחלונות עומס

Bulk-Inserts יכולים לגרום לנעילות ולעומס IO. אם משתמשים עובדים במקביל על אותן טבלאות, יש לשקול רמת בידוד (Isolation Level), אינדקסים ובמידת הצורך פרטישנינג. למעשה זה אומר: או להפעיל את היבוא בחלונות עומס, או לבנות את תזרים הנתונים כך שיקיים קיום לצד תפעול שוטף (למשל באמצעות Staging + העברה אסינכרונית).

רשימת בדיקה קונקרטית ל-Bulk-Insert עמיד באמצעות Array DML

• גודל אצווה: לקבוע ערך התחלתי (500–1,000) ולכיילו באופן מדיד.
• טרנזקציה מפורשת: Commit לאצווה כברירת מחדל; „Commit בסוף“ רק בהחלטה מודעת.
• סוגי פרמטרים יציבים: להגדיר סוגי פרמטרים יציבים, לא לאלץ המרות בלתי מפורשות.
• קונטקסט שגיאה לכל רשומה: לשמור מזהה חיצוני ושורת מקור.
• אסטרטגיית שגיאות: לטפל באצווה תחילה, ואז Split/Fallback; לרשום שגיאות פר שורה.
• רישום (Logging): קודים וטקסט, אך בהתאם לדרישות פרטיות; לתעד Batch-ID ו-Lauf-ID.
• הרצה חוזרת: להבטיח אידמפוטנטיות (מפתחות/Upsert/Import-ID).

סיכום: Array DML מהיר – העמידות מושגת באמצעות תהליך ואסטרטגיית שגיאות

Ein FireDAC Bulk-Insert mit Array DML ist ein starkes Werkzeug, solange du nicht so tust, als gäbe es keine Fehler. In echten Datenströmen gibt es immer Ausreißer: Dubletten, fehlende Referenzen, kaputte Datumswerte. Der saubere Ansatz ist deshalb: Array DML für die Performance, kombiniert mit einer kontrollierten Isolationsstrategie (Split oder Fallback) und einer pro Zeile nachvollziehbaren Fehlerliste. Damit bekommst du Tempo und Betriebssicherheit zusammen – und genau das zählt, wenn Imports nicht nur im Lab laufen, sondern jede Nacht zuverlässig durch müssen.

אם אתם רוצים לייצב תהליך ייבוא או ממשק קיים בDelphi/FireDAC (ביצועים, טרנזקציות, יכולת הרצה מחדש, Logging), נבהיר זאת בשמחה בצורה מובנית בשיחה טכנית:

לנושא זה חשובים גם Delphi Bulk Insert ו-Bulk Insert Delphi FireDAC. המאמר מסדר היבטים אלה באופן מובן ומראה מה חשוב בעבודה השוטפת.

לדון בפרויקט או במהלך מודרניזציה עם Net-Base.