SQL Server למפתחים: Bulk Insert ו- SqlBulkCopy

הקדמה

כל מפתח שעובד/עבד מול דטאבייס באשר הוא יודע איך מכניסים שורות לטבלה. מדובר באחת מהפעולות הבסיסיות, INSERT,. למשל, אם יש לי טבלה שמכילה 3 עמודות: עמודת ID עולה (IDE

CREATE TABLE [dbo].[BulkInsertDemo](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [NumCol] [int] NOT NULL,

    [LongTextCol] [varchar](2000) NOT NULL,

 CONSTRAINT [PK_BulkInsertDemo] PRIMARY KEY CLUSTERED

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

NTITY), עמודה שמכילה ערך מספרי ועמודה של טקסט:

אזי ה- SQL Statement שבו נשתמש כדי להכניס שורות לטבלה הזאת הוא:

INSERT INTO BulkInsertDemo(NumCol, LongTextCol) VALUES (1, 'BlahBlahBlah')

שום דבר מתוחכם עד עכשיו.

כיום, בהרבה מאד פרוייקטים נעשה שימוש ב ORM כלשהו, כדוגמת Entity Framework, או NHibernate, ובפרוייקטים כאלה אנחנו לא נייצר את ה- INSERT Statement בעצמנו, אלא הוא יורץ עבורנו באמצעות שכבת ה- ORM. זה לא באמת משנה בפועל. מבחינת SQL Server, מורץ INSERT Statement כדוגמת זה שראינו עד עכשיו.

הבעייה

כשעושים INSERT לשורה אחת, ל-10 שורות ואפילו ל-1,000 שורות – הזמן סביר לחלוטין. אבל כאשר עושים INSERT ל-100,000 שורות או ל-200,000 שורות – מגיעים כבר לזמנים פחות סימפטיים.
בגרף הבא אפשר לראות את זמני ההכנסה במילי שניות (ציר ה- y) כתלות בכמות השורות (ציר ה- x). כאשר אנחנו מכניסים נתונים לאותה טבלה שיצרתי קודם, כלומר כל שורה כוללת שתי עמודות מספריות, ועמודה טקסטואלית באורך של 480 תווים.

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

latency רשת: כל בקשה, קטנה ככל שתהיה, שעוברת מעל הרשת גוררת איתה את ה-“קנס” של latency רשתי. בדוגמא המסויימת שהבאתי הקנס הזה למעשה “מוזנח” בגלל שהאפליקציה שמכניסה את המידע וה-DB יושבים מעל אותו מחשב. בפועל, בתרחיש real-world, בד”כ יהיה לנו שרת DB שעל כל גישה אליו אנחנו משלמים קנס latency קטן (כמה מילי-שניות). אם אנחנו מכניסים רק שורה אחת בכל גישה, אז אנחנו משלמים את הקנס הזה במכפלות גדולות.
המתנה לנעילות: כאשר אנחנו כותבים לטבלה, אנחנו צריכים לקחת נעילות מסויימות כדי לשמור על הנכונות הלוגית (והפיזית) של המידע. במצבים מסויימים, אם מתבצעות פעולות במקביל, אנחנו עלולים להמתין עד שאנחנו מקבלים את הנעילות הללו. בדוגמא המסויימת שלנו, לא באמת היה צורך להמתין לסיום טרנזקציות אחרות, כי לא רצו פעולות אחרות במקביל. כמובן שבכל אופן היו locks ו- latches בשימוש, אבל לא נדרשנו להמתין להם.
עלות CPU: מין הסתם, יש לנו עלות מסויימת של זמן CPU בביצוע ה- insert. במקרה שלנו, העלות הזאת יחסית מינורית, אבל כמובן בכל זאת קיימת. מדובר בעלות שכרוכה למשל בייצור ה- execution plan של ביצוע הפעולה, והעלות שדרושה להפעלת פעולת ה- IO (בצורה אסינכרונית, כמובן) וניהול התהליך. פעולות ה- CPU מתבצעות ב-SQL Server ע”י ה- schedulers, כאשר עבור כל ליבה לוגית שחשופה ל- SQL Server קיים scheduler שלוקח משימות מתור המשימות, ומתחיל את הביצוע שלהם עד שהוא מגיע לאיזושהי פעולה שדורשת ממנו המתנה, ואז הוא עובר לבצע פעולה אחרת (מנגנון שקיים במטרה להקטין context switch).
עלות כתיבה ל- transaction log: כל פעולה שמתבצעת מול ה-DB וכוללת שינוי של data files, לא מבתצעת ישירות על ה- data files, אלא ראשית נכתבת “רשומת לוג” המתעדת את הפעולה ב- transaction log [קובץ ייעודי המשמש למטרה הזאת, ופתוח ע”י SQL Server בתור memory mapped file]. המטרה של התהליך הזה היא לאפשר rollback של הטרנזקציה במידת הצורך, ולהבטיח את שלמות הנתונים בכל מצב (גם במצב של הפסקת חשמל באמצע תהליך הכתיבה, למשל). כמובן, יש עוד שימושים ל- transaction log, אבל הם מחוץ ל- scope של הפוסט הזה.
בפועל, זה אומר שהמידע שאנחנו רוצים לכתוב לטבלאות נכתב קודם ל- transaction log (בצירוף כמובן רשומות נוספות המבטאות פעולות כמו הקצאת page-ים וכו’). במקרה שלנו, כל פעולת INSERT גררה כתיבה של כל המידע שהוספנו לטבלה גם ל- transaction log עצמו. כתיבה לקובץ מגלמת בתוכה עלות של I/O. קצב הביצוע של פעולות ה-IO מהווה גם איזשהו חסם עליון על מהירות ביצוע פעולת כתיבה. כלומר, אם יש לנו throughput נמוך במיוחד לדיסק של 1MB/sec (לטובת ההקצנה), אז זה אומר שלא-משנה-מה לא נוכל לכתוב מידע יותר מהקצב הזה. בנוסף למגבלת ה- throughput קיימת מגבלת IOPS: כמה פעולות IO שונות הדיסק יכול לבצע בשנייה.

אז מה נרצה בפועל? לייעל את הזמנים הללו עבור הכנסות גדולות. כלומר, להקטין את העלות השולית שמתווספת להכנסה של כמות גדולה של שורות.

הפיתרון: BULK INSERT

SQL Server כולל תמיכה מובנית לתרחיש שנדרשת טעינה של כמות גדולה של נתונים, שהוא בפועל תרחיש די נפוץ. התמיכה הזאת מתבצעת באמצעות statement שניתן להריץ דרך SQL Server שנקרא BULK INSERT שמאפשר לקבל קובץ (ותיאור של הפורמט שלו) ולטעון את הערכים ממנו לטבלה בצורה יעילה.

בנוסף, קיים command line utility בשם bcp.exe שמאפשר ביצוע של הפעולה הזאת דרך command line, בלי SQL. עבור כאלה שעובדים עם SSIS, אז יש יכולת להשתמש ב- bulk insert באופן מובנה. בנוסף, כאשר עושים שימוש ב Import and Export wizzard, עבור import של נתונים לתוך SQL Server – זה נעשה תוך שימוש ב- BULK INSERT.

כל זה נחמד, אבל כמפתחים אנחנו רוצים לעשות דרך לבצע את הפעולה בצורה אפליקטיבית. לשם כך קיים אובייקט מובנה ב- .NET Framework בשם System.Data.SqlClient.SqlBulkCopy., שבו אני אתמקד בהמשך ההסבר.

נדגים רגע את השימוש ב- SqlBulkCopy:

//Prep data
DataTable dt = new DataTable();
dt.Columns.Add("NumCol", typeof(int));
dt.Columns.Add("LongTextCol", typeof(string));
for (int i = 0; i < rows; i++)
{
    var row = dt.NewRow();
    row["NumCol"] = i;
    row["LongTextCol"] = _longString;
    dt.Rows.Add(row);
}
  
       
//Load data
using (var bulkCopy = new SqlBulkCopy(_connectionString) { DestinationTableName = "BulkInsertDemo" })
{
    bulkCopy.ColumnMappings.Add("NumCol", "NumCol");
    bulkCopy.ColumnMappings.Add("LongTextCol", "LongTextCol");
    bulkCopy.WriteToServer(dt);
}

לפני שנפענח מה קורה פה, נראה רגע איך נראה הגרף שמשווה בין INSERT ל-BULK INSERT עם SqlBulkCopy (הכחול זה INSERT-ים והכתום זה SqlBulkCopy) [הבהרה: ה- benchmark הזה מיועד כדי לתת תחושה בסיסית לגבי השוואת המהירות, ולא בודק קריטריונים נוספים שונים שיכולים להשפיע על המהירות. אם אתם מעוניינים, יש הרבה benchmarks מפורטים הרבה יותר ברשת]

כמו שאפשר לשים לב, ההבדל דרמטי, במיוחד בקצב הגידול. עבור הכנסה של 200,000 שורות: הכנסה באמצעות INSERT-ים בודדים לוקחת 39.5 שניות, בעוד ששימוש ב- SqlBulkCopy מוריד את הזמן ל- 3.1 שניות (הזמן כולל את הזמן של יצירת ה- data table והכנסת המידע אליו. הזמן של ביצוע פעולת הכתיבה עצמה, הוא 1.8 שניות). גם עבור כמות שורות קטנה יותר יש פער משמעותי לטובת bulk insert. למשל, עבור 1,000 שורות מדובר בפער בין 205ms ל- 17ms.

אז אחרי שהבנו שאנחנו מקבלים מזה תמורה אמיתית, בואו נבין ביחד את הקוד שמופיע קודם שעושה שימוש ב- SqlBulkCopy. תחילה, כפי שאפשר לראות, אנחנו מייצרים DataTable וממלאים אותו בערכים. הסיבה היא של- WriteToServer, המתודה של SqlBulkCopy שמשמשת לכתיבת המידע בפועל, יש overload שמקבל DataTable. בהמשך נראה דרכים נוספות.
אחרי שמילאנו את ה- DataTable, אנחנו מייצרים instance של SqlBulkCopy, נותנים לו את ה- connection string ב- constructor (הוא תומך בקבלת פרמטרים נוספים, אבל אנחנו לא מעבירים כאלה, כך שהוא עובד לפי ה- defaults שלו). בנוסף, אנחנו מגדירים את ה- DestinationTableName להיות שם הטבלה שאליה אנחנו מכניסים את המידע.

בחלק הבא אנחנו מבצעים mapping בין שמות העמודות כפי שהן מופיעות ב- DataTable לשמות העמודות בטבלה. השמות במקרה הזה זהים, כך שאפשר היה לעבור בלולאה על העמודות ולהוסיף את ה- mapping תוך הסתמכות על השמות הזהים. בסוף אנחנו קוראים ל- WriteToServer שמבצע את הפעולה בפועל תוך שימוש ב- BULK INSERT. קיימת גם מקבילה async-ית שנקראת WriteToServerAsync שניתן להשתמש בה בשילוב עם async-await.

לעטוף את זה יפה

עבודה עם DataTable ישירות רק לטובת ביצוע ה- bulk insert זה די מגעיל, ותכלס – אין סיבה טובה לעשות את זה אם יש לנו איזושהי שכבה כבר באפליקציה שעושה אבסטרקציה של המידע לאובייקטים (ORM כלשהו).

מי שעובד עם Entity Framework יכול להשתמש ב- EntityFramework.BulkInsert. מדובר בפרוייקט שזמין כ- NuGet package שמוסיף את הפונקציונאליות של BulkInsert ל- context ומאפשר לקרוא ל- context.BulkInsert ולהעביר IEnumerable של entities של entity framework (בין אם database first ובין אם code first) ולהנות מהביצועים של bulk insert.

מי שעובד עם ORM אחר, או משהו משלו , ורוצה להשתמש ב- SqlBulkCopy בלי להוסיף את ה- overhead של לייצר DataTable ולהעתיק אליו את המידע, יכול לראות את הפוסט הזה ב- stackoverflow שמכיל בסיס של מימוש ל- ObjectDataReader, ש- WriteToServer תומך גם בלקבל אותו. בצורה הזאת ניתן יחסית בקלות לשלב את זה עם אובייקטים משלכם (בין אם במיפוי של 1:1 כאשר השמות באובייקטים זהים לשמות של השדות בטבלאות, ובין אם לעשות עוד קצת שינויים ולהוסיף attributes שמתארים את הקשר בין האובייקט לטבלה ולהתאים את הקוד לשימוש בו).

כמובן, שבסופו של דבר תמיד אפשר כן לעשות שימוש ב- DataTable, רק צריך להיות מודעים לכך שאם אתם לא עובדים איתו ישירות, אלא למעשה מעתיקים את המידע מאובייקטים אחרים אתם משלמים עלות של זיכרון נוסף , זמן העתקה, ועלות נוספת ב-GC.

למה זה יותר מהיר?

יש כמה גורמים שהופכים את פעולת ה- BULK INSERT להיות יותר מהירה:

הקטנת ה- overheadהתקשורתי

כאשר עובדים מול שרת מרוחק, אז כאמור יש לנו “קנס” של latency לכל בקשה שאנחנו מבצעים. כלומר, נרצה לייצר בקשות גדולות כמה שיותר כדי להקטין את ה-overhead התקשורתי. למשל, אם נגדיר שה- latency הרשתי שלנו פר-בקשה הוא 1ms. כלומר, אם נעשה 200,000 הכנסות של שורה אחת כל פעם – יוצא ששילמנו 200,000ms (כלומר, קצת יותר מ-3 דקות) עוד לפני שעשינו ולא פעולה אחת – רק עלות גישה רשתית.
כאשר אנחנו משתמשים ב- BULK INSERT אנחנו מאפשרים “לאגד” הכנסה של כמה שורות באותה פאקטה שיוצאת מהמחשב שלנו, וכך מקטינים את הקנס הרשתי.
כמובן שאנחנו לא יכולים להקטין אותו ל-0, כי יש מגבלה של packet size (גם ברמת ה- SQL Server, גם ברמת המערכת הפעלה, גם ברמת הראוטרים שבדרך, גם ברמת הכרטיס רשת…). אבל, אם למשל גודל שורה שאנחנו מכניסים הוא 300 בתים, ואנחנו עובדים בסביבה שתומכת ב- jumbo frames, כלומר MTU של 9000 בתים, אנחנו יכולים להכניס כ-22 שורות לפאקטה אחת שעוברת פיזית בכרטיס רשת (כמובן שכתלות ב- packet size שמוגדר ב- connection string יכול להיות שבפועל נשלחות יותר שורות, והפיצול מתבצע ברמת הכרטיס רשת). כלומר, אנחנו כבר מקטינים באופן משמעותי את ה- overhead התקשורתי.

הקטנת ה- overhead הכללי בטיפול בקשה-בקשה

חוץ מה- overhead התקשורתי, טיפול במרוכז ב-batch של שורות שיש להכניס ולא שורה-שורה מאפשר ל- SQL Server לייעל את התהליך: לא נדרש ייצור execution plan(גם אם טריוויאלי) מחדש עבור כל שורה, מציאת page-ים לאכסון המידע מתבצעת בבת אחת לכמות גדולה יותר ולכן מפחית את ההשפעה, אם מוגדרת דחיסה ברמת ה- page, אז היא יכולה להתבצע פעם אחת ולא הכנסה של כל שורה גוררת “פתיחה ודחיסה” מחדש וכו’. בקיצור, יש יתרון גדול לאגד מספר בקשות יחד אם אפשר.

למעשה, זאת מהות שיפור הביצועים שאנחנו מקבלים מפעולת ה- Bulk Insert: העובדה שאנחנו מאגדים ביחד דברים וכך מקטינים את ה- overhead שכרוך בטיפול בקשה-בקשה.

Minimal-logging

בעבודה עם ריבוי INSERT-ים, כפי שנאמר קודם, המידע נכתב ל- transaction log ולאחר מכן נכתב ל- data files. כלומר, בפועל, כמות ה- IO המצטבר על השרת שלנו גדולה יותר: אם סופרים את כל פעולות ה- IO המעורבות בכתיבת הנתונים החדשים, אז אנחנו גם כתבנו את כל השורות החדשות למעשה ל- transaction log (כי תיעדנו כל שינוי שנעשה ב- page-ים ב- buffer pool בזיכרון) וגם לאחר מכן כל המידע הזה נכתב ל- data files ע”י ה- lazy writer או בעקבות פעולת CHECKPOINT.

כאשר ה-DB מוגדר ב- recovery model של Simple או Bulk-Logged (בתמצית, אפשר להתייחס ל- recovery model כמדיניות שמירת רשומות ה- transaction log שכבר “אין בהן צורך” מבחינת התקינות של ה-DB. הגדרות ה- recovery mode השונות משפיעות על סוגי הגיבויים והשחזורים שאפשר לעשות, ויש להן חשיבות רבה – שהיא מחוץ ל- scope בפוסט הזה), אז מתאפשר פיצ’ר של SQL Server שנקרא minimal logging (יש עוד תנאים שנדרשים להתקיים, למשל שהטבלה לא מסומנת ל-replication ושמגדירים table lock) שבמסגרתו נכתב משמעותית פחות מידע ל- transaction log (נכתב למעשה רק מידע על page-ים חדשים שהוקצו לטובת אכסון המידע) והמידע נכתב למעשה ישירות ל- data files. בפועל, זה אומר משמעותית פחות I/O ולכן משפר את ביצועי טעינת המידע ונותן למעשה שיפור נוסף מעבר להקטנת ה- overhead

נקודות שצריך להיות מודעים אליהם בעבודה עם BULK INSERT

אחרי שראינו את הבסיס של איך להשתמש ב-BULK INSERT אפליקטיבית באמצעות SqlBulkCopy, יש כמה נקודות שכדאי להכיר לגבי BULK INSERT (שניתן לשלוט עליהם דרך ה- instance שאנחנו יוצרים של SqlBulkCopy).

הגדרות שאפשר להגדיר לפעולת ה- BULK INSERT

ב- constructor של SqlBulkCopy ניתן להעביר לו הגדרות שונות באמצעות SqlBulkCopyOptions. למשל:

   1: using (var bulkCopy = new SqlBulkCopy(_connectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers) { DestinationTableName = "BulkInsertDemo" })

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

CheckConstraints: מאפשר להגדיר שתהיה בדיקה של constraints שמוגדרים על הטבלה בזמן ההכנסה. כברירת מחדל, לא מתבצעת בדיקה של constraints.
FireTriggers: כאשר מוגדר, טריגרים של AFTER INSERT יופעלו.
KeepIdentity: כברירת מחדל, עמודו שמוגדרות בתור IDENTITY – גם אם מועבר אליהן ערך הוא יידרס ע”י הערך שנבחר ע”י SQL SERVER. אם מוגדר, אז יישמר הערך של עמודות ה- IDENTITY.
TableLock: מגדיר שתילקח נעילה מלאה על הטבלה. מאפשר לנו (בכפוף לקיום התנאים האחרים) להנות מהיתרונות של minimal logging

הגדרה חשובה נוספת, שלא מועברת באמצעות SqlBulkCopyOptions אלא באמצעות property אחר של SqlBulkCopy, בשם BatchSize היא הגודל של ה- batch. כברירת מחדל, כשאנחנו קוראים ל- WriteToServer כל המידע נכתב ל- SQL Server בתור batch בודד. המשמעות של batch היא כמות השורות שנשלחות כל פעם ל-SQL Server. אם מוגדר UseInternalTransaction (אופציה של SqlBulkCopyOptions), אז כל batch נחשב כטרנזקציה בפני עצמו, כך ש-rollback יתבצע בגרנולריות של batch.

עוד הגדרה חשובה שכדאי להגדיר, בכלל לא מוגדרת במסגרת ה- SqlBulkCopy היא ה- packet size. את ההגדרה הזאת מוסיפים ל- connection string, והיא מאפשרת לנו למעשה להגדיל עוד יותר את ה- throughput של ההכנסות. ברירת המחדל היא 4096, כאשר את ההגדלה צריך לעשות בזהירות (זה משפיע גם על צריכת הזיכרון בשרת), אבל במקרה של פעולת bulk insert בסביבה שעושה שימוש ב- jumbo frames, אז בוודאי שנרצה להגדיל לפחות פי 2.

BULK INSERT ופעולה מקבילית

כמו כל פעולה ב- SQL Server, גם BULK INSERT לוקח נעילות. אולם, כל עוד לא מוגדר TableLock, אין שום מניעה להריץ ממספר קליינטים שונים Bulk Insert ושהפעולות יתבצעו במקביל. צריך להבדיל פה בין מקביליות שמתבצעת ברמת ה- clients, שקורית כאשר יש מספר thread-ים או פרוססים שעושים Bulk insert לאותה הטבלה במקביל לבין parallel execution plan שאומר שהפעולה מתמקבלת ברמת SQL Server.

כדי לבצע טעינות מקביליות ממספר קליינטים, אופציית ה- TableLock חייבת להיות לא מסומנת.

כדי לאפשר ל- SQL Server לבצע את פעולת ה- bulk insert כ- parallel operation, אז אופציית ה- TableLock חייב כן להיות מסומנת, ובנוסף חייב לא להיות מוגדר אף אינדקס על הטבלה שאליה טוענים, כך שזה תרחיש שמתאים בעיקר אם עובדים עם טבלאות staging.

BULK INSERT וקשרים בין טבלאות

כאשר אנחנו עובדים עם Entity Framework, אחד הפיצ’רים החזקים הוא היכולת לייצר קשרים בין אובייקטים (associations) שלאחר מכן כאשר אנחנו עושים SaveChanges, אז EntityFramework יודע לייצר מהם את ה- insert statements שיבצעו את ההכנסה כך שהקשרים הללו יישמרו גם ברמת הטבלאות ב-DB. הצורה שבה זה מתבצע היא שלאחר כל insert, מתבצעת שליפה ע”י Entity Framework של הערך שחוזר מ- SCOPE_IDENTITY, כלומר של ה- “id” של השורה שהוכנסה, מה שמשמש לטובת יצירת ה-relationship באמצעות שימוש בערך הזה ב- insert-ים העתידיים.

כאשר עובדים עם BULK INSERT, אז אנחנו מכניסים את המידע כ- bulk ובפרט לא מקבלים את ה-IDENTITY עבור כל ערך. כלומר, צורת העבודה הזאת לא מתאימה להכנסה “בבת אחת” של object graph שמתאר קשרים שמתמפים ל- relationship ברמת ה-DB.

מה אפשר לעשות?

דה-נורמליזציה לטבלאות שמבצעים אליהם טעינות מאסיביות: אם למשל אנחנו רוצים לטעון מידע בכמויות מאסיביות לטבלת LogEvents, שלה למשל יש relationship של 1:1 מול טבלת LogEventsExtendedDetails כך שחלק מהמידע נשמר פה וחלק נשמר שם . במקרה זה ייתכן שביצוע דה-נורמליזציה ואיחוד לטבלה אחת יהיה פיתרון פשוט ויעיל – שיאפשר לנו להנות מהיתרונות של bulk insert בלי להתקשות עם החיסרון של relationships.
שימוש בערכים שממולאים כבר ע”י ה- client: אם למשל יש לנו טבלת LogEvents וטבלת LogEventsParameters שמכילה עבור כל שורה בטבלת LogEvents מספר פרמטרים שאנחנו רוצים לשמור. במקרה כזה, אם נרצה גם להשתמש ב- bulk insert לא נוכל במידה שהקישור בין LogEventsParameters ל- LogEvents מתבצע באמצעות ערך של עמודת IDENTITY, או כל ערך אחר שמיוצר ברמת ה-DB. אם, לעומת זאת, נייצר את הערך בצד המכניס, למשל נייצר GUID ונשתמש בו לטובת הקישור, נוכל לייצר את המידע כך שנשמר הקישור על בסיס ה-GUID הזה שנקבע ב- client ולבצע שתי פעולות bulk insert עם שני instance-ים בלתי תלויים של SqlBulkCopy לטובת הכנסה לשתי הטבלאות. ניתן לעשות עוד כל מיני מניפולציות המתבססות על “בישול” המידע ב- client שמבצע את ההכנסה.

BULK INSERT וכשלונות

ההתנהגות הכללית של BULK INSERT היא של רצף INSERT-ים בלתי תלויים (מבחינה לוגית), ולכן כישלון שקורה לא גורר rollback של כל ה- INSERT-ים . אם מוגדר UseInternalTransaction, אז כן יתבצע rollback של ה- batch הנוכחי (שגודלו כגודל ה- batch size, או בדיפולט כל השורות).

אם פעולת ה- SqlBulkCopy הייתה כחלק מטרנזקציה שהייתה פעילה ברמת ה- connection או שהועברה לו ספיציפית, אז מתבצע rollback לטרנזקציה (וכמובן לכל הפעולות שבוצעו במסגרתה).

מעקב אחרי קצב ה- BULK INSERT בסביבת ה- production

ניתן לעקוב אחר קצב פעולות ה- bulk insert דרך ה- performance monitor (נכנסים ל- run וכותבים permon.msc). ה-counter הרלוונטי נמצא ב- SQL Server : Databases –>Bulk Copy rows/sec, Bulk Copy throughput/sec:

שיפור מהירות ההכנסה של פעולות INSERT

על אף שזה לא הנושא העיקרי של הפוסט, אני רוצה להתייחס לעוד מס’ אפשרויות (ממש לא הכל) שיכולות לשפר את ביצועי ההכנסה גם ללא שימוש ב SqlBulkCopy.

INSERT של מספר שורות מרובה

פיצ’ר יחסית חדש ב- SQL Server מאפשר להעביר מספר values ב- INSERT רגיל:

INSERT INTO BulkInsertDemo(NumCol, LongTextCol) 
VALUES 
(1, 'BlahBlahBlah'),
(1, 'BlahBlahBlah'),
(2,'a')

כמות השורות שניתן להעביר בצורה הזאת מוגבלת ל-1000 שורות. עבור 1000 שורות, הכנסה באמצעות רצף פעולות INSERT לקחה 205ms, באמצעות SqlBulkCopy לקחה 17ms ובאמצעות INSERT עם ערכים מרובים, כמו בשיטה הזאת, 143ms. עדיין נהנים מחלק מה- benefits (למשל, חיסכון מסויים ב- overhead של בקשה אחר בקשה), אבל בכל אופן מדובר בתהליך שהוא פחות optimized מ- BULK INSERT (על אף שעבור מי שכותב SQL הוא יותר פשוט, כי הוא לא דורש טעינה מקובץ חיצוני, הגדרת פורמטים וכו’).

מדובר למעשה בפיצ’ר שהמטרה העיקרית שלו היא לפשט כתיבת SQL ופחות נושא הביצועים, אבל הוא בכל זאת נותן שיפור קל. עם זאת, הוא מוגבל בכמות השורות, וכן חושף אותנו לקושי שלעיתים צץ בעבודה עם BULK-ים (באפליקציות שתוכננו לעבוד שורה-שורה מסיבות כאלה ואחרות), כך שאין לו יתרון אמיתי על פני שימוש ב- SQL Bulk Copy.

שימוש ב- Delayed Durability

פיצ’ר נוסף שחדש יחסית ב- SQL Server הוא delayed durability שמאפשר לנו לשפר את ביצועי ההכנסה באמצעות ניתוק הצורך להמתין לכתיבה ל- transaction log (וכשיש כתיבה, היא יעילה יותר, כי מתבצעת כתיבה של buffer גדול יותר בד”כ), זאת במחיר הסיכון שקיים לאיבוד מידע (שלא קיים בעת עבודה “רגילה”, כלומר ללא איפשור delayed durability.

ניתן לאפשר Delayed Durability ברמת ה-DB כך שהוא לא מופעל בדיפולט, ונדרש איפשור שלו גם ברמת הטרנזקציה, או לאפשר אותו באופן רחב לכל הפעולות.

שימוש ב- In-Memory OLTP

עוד פיצ’ר חדש ב- SQL Server, הוא In-Memory OLTP שמאפשר למעשה שינוי צורת העבודה מול טבלאות מסויימות כך שכל נתוני הטבלה נשמרים בזיכרון. לא מדובר רק בפיצ’ר שמשנה את המיקום שבו הנתונים נשמרים, אלא הוא כולל גם מנגנון locking (או ליתר דיוק, מנגנון העדר locking) שונה, סוגי אינדקסים שונים וכו’. אולם, הוא מאפשר גם שיפור משמעותי בפעילויות הכנסת המידע, במיוחד כאשר נעשה שילוב של זה עם delayed durability או עם טבלאות שהן ללא data durability (כלומר, קיימות בזיכרון בלבד).

יש עוד לא מעט דברים שיכולים להשפיע על ביצועי INSERT, כולל נושאים שלא נגעתי אליהם בכלל (כי הם פחות נוגעים לזווית של מפתחים) כמו חומרה, והגדרות ברמת ה- DB וכו’, ומי שמתעניין יכול למצוא כמובן שפע של מידע בנושא ברחבי הרשת.

שחר.