מניפולציות על קובץ XML בעת ייבוא לSQL

avr416

יש לי קבצי XML שאני צריך לייבא לדטהבייס, אולם אני רוצה לעשות בהם כמה שינויים בעת הייבוא.
לדוגמא, יש לי משתנה שהוא הID של העמוד והוא מחרוזת כזאת: "P5", אני רוצה לשמור רק את המספר 5 כי זה מיובא לעמודה בשם PAGE. וכן בכל שאר הID בקובץ הנ"ל שיש אותיות באנגלית בתחילת המספר כדי לסמן למה זה שייך דבר מיותר עבורי, שגם מקשה עלי בהמשך כאשר אני רוצה לעשות השוואות בין הערכים (כגון גדול מ, קטן מ).
לכן אני רוצה לעשות לזה Substring לגזור את האותיות ולהשאיר רק את המספרים ולאחסן אותם בשדה מסוג INT.
אלא שאני רוצה לעשות זאת בקוד הSQL בו אני מייבא את הXML דבר שאני לא מצליח, ולא כ"כ יודע איך לעשות זאת.

הנה הקוד שלי: (העתקתי חלק ממנו ממדריכים שמצאתי כיצד לייבא XML אז אני לא כ"כ יודע איפה אפשר לשנות..

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX), @Counter AS INT

SET @Counter = 1

WHILE(@Counter<94)
BEGIN

SELECT @XML = XMLData FROM dbo.XmlFiles
WHERE Id =@Counter

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO dbo.wordsVer2 (PageID, TextBlockID, CONTENT, WC, CC)
SELECT CAST(SUBSTRING(PageID,6,5) AS INT), TextBlockID, TextLineID, CONTENT, WC, CC
FROM OPENXML(@hDoc, 'alto/Layout/Page/PrintSpace/TextBlock/TextLine/String')
WITH 
(
PageID int'../../../../@ID',
TextBlockID int '../../@ID',
CONTENT [NVARCHAR](MAX) '@CONTENT',
WC [DECIMAL](3,2) '@WC',
CC int '@CC'
)
SET @Counter=@Counter+1
END

EXEC sp_xml_removedocument @hDoc
GO

בקוד הזה אני מקבל את השגיאה שא"א לעשות substring לערך מסוג INT, לכן בתוך הWITH שיניתי את הPageID לסוג varchar ואז אני מקבל את השגיאה: Conversion failed when converting the nvarchar value 'P1_TB00001' to data type int.
האם ישנה דרך לעשות זאת על ידי הSQL? או שמא צריך לעשות זאת על ידי קוד C# ורק אז לשמור לדטה בייס?
מקווה שהייתי מובן :shock:
תודה רבה מראש!!

פורסם במקור בפורום CODE613 ב22/08/2016 22:07 (+03:00)

ארכיטקט

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

אני אישית מייבא קבצי XML אך ורק דרך דוט נט, ומעבד אותם היטיב עם מחלקות מתמחות, ואז הקוד מקבל בהירות ויכולת תחזוקה גבוהה מאוד, אז מפעילים לולאה (אני אפילו מעדיף להשתמש ב EF למען הבהירות של הקוד) ולוקח רבע שעה מה קרה???

פורסם במקור בפורום CODE613 ב22/08/2016 22:41 (+03:00)

avr416

@ארכיטקט

אני אישית מייבא קבצי XML אך ורק דרך דוט נט, ומעבד אותם היטיב עם מחלקות מתמחות, ואז הקוד מקבל בהירות ויכולת תחזוקה גבוהה מאוד, אז מפעילים לולאה (אני אפילו מעדיף להשתמש ב EF למען הבהירות של הקוד) ולוקח רבע שעה מה קרה???

סתם מעניין, הקוד שהבאתי למעלה מייבא קובץ XML מסוים בכמה שניות, ואילו כשאני מייבא את אותו קובץ על ידי Linq to xml ולבסוף שומר את זה לדטהבייס (על ידי EF)- זה לוקח 7 דק' :shock:
מהי סיבת ההבדל?!

פורסם במקור בפורום CODE613 ב25/08/2016 23:46 (+03:00)

רחמים

EF עובד עם שאילתות דרך הרשת, ופונקציה אתה מריץ ישירות על השרת. הלא כן?

פורסם במקור בפורום CODE613 ב26/08/2016 16:22 (+03:00)

ארכיטקט

@רחמים

EF עובד עם שאילתות דרך הרשת, ופונקציה אתה מריץ ישירות על השרת. הלא כן?

למה החלטת שהוא עובד דרך הרשת?? הוא עובד על מחשב מקומי לפי מה שאני מבין.

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

פורסם במקור בפורום CODE613 ב27/08/2016 21:15 (+03:00)

dovid

בקוד הSQL SERVER אין ספק שעליך להצהיר על הPageID כvrachar. זה בכלל לא ספק.
הסיבה שגם אז הוא נכשל במהרה למספר היא כנראה בגלל שהSUBSTRING לא מחזיר מספר נקי, לפחות בחלק מהשורות. תבדוק זאת בצורה פשוטה, עשה עותק לפרוצדורה שפשוט מחזיר טבלה ולא מבצע מאומה וצפה בתוצאות. קוד כזה:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX), @Counter AS INT
 
SET @Counter = 1
 
WHILE(@Counter<94)
BEGIN
 
SELECT @XML = XMLData FROM dbo.XmlFiles
WHERE Id =@Counter
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
 
SELECT SUBSTRING(PageID,6,5), TextBlockID, TextLineID, CONTENT, WC, CC
FROM OPENXML(@hDoc, 'alto/Layout/Page/PrintSpace/TextBlock/TextLine/String')
WITH
(
PageID varchar(100) '../../../../@ID',
TextBlockID int '../../@ID',
CONTENT [NVARCHAR](MAX) '@CONTENT',
WC [DECIMAL](3,2) '@WC',
CC int '@CC'
)
SET @Counter=@Counter+1
END
 
EXEC sp_xml_removedocument @hDoc
GO

ההבדל הענק בין הLink2Sql לבין הSQL-SERVER טעון פירוט רקע של כמה דברים. ברור שהכנסה בEF איטית מSQL כי כל הבדיקות והאילוצים מבוצעים פעמיים, אבל עדיין זה לא הסבר לפער כה עצום.

פורסם במקור בפורום CODE613 ב27/08/2016 21:22 (+03:00)

avr416

@דוד ל.ט.

בקוד הSQL SERVER אין ספק שעליך להצהיר על הPageID כvrachar. זה בכלל לא ספק.
הסיבה שגם אז הוא נכשל במהרה למספר היא כנראה בגלל שהSUBSTRING לא מחזיר מספר נקי, לפחות בחלק מהשורות.

צודק לגמרי! הייתה לי שם טעות בקוד, ושכחתי לעשות SUBSTRING לעמודה אחרת..
תוך 3 שניות הוא המיר לי את כל הקבצים לתוך הטבלה!!
@דוד ל.ט.

ההבדל הענק בין הLink2Sql לבין הSQL-SERVER טעון פירוט רקע של כמה דברים. ברור שהכנסה בEF איטית מSQL כי כל הבדיקות והאילוצים מבוצעים פעמיים, אבל עדיין זה לא הסבר לפער כה עצום.

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

SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Users\Avraham\Downloads\השמטות הרמבם\äÖÄêàÜ äÿÄüì\003420123-0094.xml', SINGLE_BLOB) AS x;

ואח"כ אני רץ בלולאה על התאים הללו וממיר אותם לטבלה, דבר שלוקח 3 שניות ל94 קבצי XML.
מאידך, יש לי את קוד הC# הבא, שעושה את אותה עבודה אך לוקח לו 7 דקות!!

private async Task<bool> addXmlPageToDB(string folder, int bookID)
        {
            var files = Directory.GetFiles(folder, "*.xml");
            await Task.Run(() =>
            {
                foreach (var file in files)
                {
                    XElement xml = XElement.Load(new StreamReader(file));
                    var page = from pa in xml.Descendants("Page")
                               select pa.Attribute("ID").Value;
                   
                    var numPage = page.First().Substring(1);

                    var printSpace = from ps in xml.Descendants("PrintSpace")
                                     select ps;
                    var textBlocks = from tb in printSpace.Descendants("TextBlock")
                                     select tb;
                    foreach (var block in textBlocks)
                    {
                        int tbID = int.Parse(block.Attribute("ID").Value.Substring(6));
                        var textLines = from tl in block.Descendants("TextLine")
                                        select tl;
                        foreach (var line in textLines)
                        {
                            int tlID = int.Parse(line.Attribute("ID").Value.Substring(6));
                            var strings = from str in line.Descendants("String")
                                          select str;
                            foreach (var st in strings)
                            {
                                int strID = int.Parse(st.Attribute("ID").Value.Substring(6));
                                var word = new word()
                                {
                                    PageID = numPage,
                                    textBlockID1 = tbID,
                                    textLineID1 = tlID,
                                    stringID1 = strID,
                                    CONTENT = st.Attribute("CONTENT").Value.Trim(),
                                    HEIGHT = st.Attribute("HEIGHT").Value,
                                    WIDTH = st.Attribute("WIDTH").Value,
                                    HPOS = st.Attribute("HPOS").Value,
                                    VPOS = st.Attribute("VPOS").Value,
                                    CC = st.Attribute("CC").Value,
                                    WC = decimal.Parse(st.Attribute("WC").Value),
                                    BookID = bookID.ToString()
                                };
                                db.words.Add(word);
                            }
                        }
                    }
                }
            });           
            db.SaveChanges();
            return true;
        }

פורסם במקור בפורום CODE613 ב27/08/2016 22:26 (+03:00)

dovid

בEF ככל שכמות הרשומות פר קונטקסט ו/או פר קומיט (שמירה) עולה, אז המהירות יורדת.
אם מדובר ב200,000 שורות זה הסבר מניח את הדעת.

אני אכן הייתי דבק בC# מחמת שאינני מבין/שולט בSQL בכלל ובפרט בקוד שהבאת. אבל מצורפים פה שיקולי עצלות שגויים עם נכונים...
תבדוק בלי הEF, כלומר תבטל להערה את שורה 46. לדעתי זה ייקח זמן דומה לSQK SERVER. אם כן הפוקוס הוא הEF.
אתה יכול לעשות שתי דברים למהר את הEF:

לבטל את AutoDetectChangesEnabled + ValidateOnSaveEnabled.
לשמור + להחריב (Dispose) את הdb ולהציב בו מופע חדש כל X רשומות.
זה המקור שלי: http://stackoverflow.com/a/5942176/1271037

http://stackoverflow.com/a/15662102/1271037

אגב הTask מחזיר bool אבל הוא האותה מידה יכול לא להחזיר כלום (Task ללא פרמטר גנרי) ולא השורה האחרונה (53).

ובשולי הדברים כל הכבוד...

פורסם במקור בפורום CODE613 ב28/08/2016 01:10 (+03:00)

ארכיטקט

אוקי, אז בשורה 53 למעשה הוא נתקע (ולא בשורה 46 כמו שדוד אמר) מה שקורה ה EF מריץ שאילתה נפרדת עבור כל אינסרט שעשית, כלומר כמידת צעדי הלולאה, הוא עושה לולאה משלו כל רשומה רצה בשאילתה נפרדת. אתה יכול לראות את זה בפרופיילר.
השאילתות לא רצות במקביל, אלא כל אחת ממתינה לתשובה מהדטה בייס. ולא זו בלבד אלא שאחרי שהשאילתה בוצעה הוא עוד שואב משם מידע כדי לקבל את ה ID וכדומה.

פורסם במקור בפורום CODE613 ב28/08/2016 10:25 (+03:00)

avr416

תודה רבה דוד, אכן הקוד עצמו ללא האנטיטי רץ ב00.3 שניות על 94 קבצי XML.
לאחר ששמעתי בעצתך ועשיתי את 2 הדברים שהמלצת (גם שינוי הקונפיגורציה של EF וגם חיסול המופע של האנטיטי ויצירתו מחדש) המהירות שופרה פלאים!!
כאשר הגדרתי את המונה ל100 הקוד לקח 17 שניות, כשהגדרתי ל1000 לקח לו בין 1.2 ל 1.7 (ניסיתי כמה פעמים). וכשהגדרתי ל10000 וכן ל5000 לקח לו באזור ה1.6 שניות!! (קודם לקח לו 7 דקות!!).
כמו כן שמתי לב, שאם אני מריץ את הקוד הזה פעמיים, אז בפעם השניה זה לוקח שניה פחות! (כלומר 0.7 שניות סה"כ) כנראה מכיון שהוא כבר מחזיק את החיבור לדטה בייס פתוח או משהו כזה.
הנה התוספת לקוד (הבאתי רק את הלולאה הפנימית):

foreach (var st in strings)
                            {
                                int strID = int.Parse(st.Attribute("ID").Value.Substring(6));
                                var word = new word()
                                {
                                    PageID = numPage,
                                    textBlockID1 = tbID,
                                    textLineID1 = tlID,
                                    stringID1 = strID,
                                    CONTENT = st.Attribute("CONTENT").Value.Trim(),
                                    HEIGHT = st.Attribute("HEIGHT").Value,
                                    WIDTH = st.Attribute("WIDTH").Value,
                                    HPOS = st.Attribute("HPOS").Value,
                                    VPOS = st.Attribute("VPOS").Value,
                                    CC = st.Attribute("CC").Value,
                                    WC = decimal.Parse(st.Attribute("WC").Value),
                                    BookID = bookID.ToString()
                                };

                                counter++;
                                if (counter < 1000)
                                    db.words.Add(word);
                                else
                                {
                                    db.words.Add(word);
                                    db.SaveChanges();
                                    db.Dispose();
                                    db = new Model1();
                                    counter = 0;
                                }                               
                            }
                        }

הTASK מחזיר bool כיון שיש פונקציה אחרת שקוראת לו ומציגה למשתמש הודעה האם אכן המידע נוסף בהצלחה או לא.

ארכיטקט - תודה על ההסבר מה עומד מאחורי ההתנהגות של הEF.

פורסם במקור בפורום CODE613 ב28/08/2016 12:02 (+03:00)

avr416

מה שמוזר הוא שהדטה בייס עצמו נשאר ריק...
רק אם אני מוסיף בסוף הקוד פעם נוספת - db.SaveChanges() אז אכן הנתונים נשמרים בדטהבייס, ואז אכן לוקח לקוד לרוץ כ15 שניות.
זה ממש מוזר, הרי אני קורא לפונקציה הזו לאחר כל 1000 איטרציות, אז אני מבין שאם לבסוף הכנסתי פחות מ1000 אז כל האחרונים לא ישמרו, אבל מדוע כל ה33,000 האחרים לא נשמרים?! (כלומר בשורה 54 אני קורא לפונקציה הזו, אז למה אם שורה 64 תמחק לא יכנסו לדטהבייס שום נתונים????)
הנה הקוד במלואו:

private async Task<bool> addXmlPageToDB(string folder, int bookID)
        {
            var files = Directory.GetFiles(folder, "*.xml");
            await Task.Run(() =>
            {
                foreach (var file in files)
                {
                    XElement xml = XElement.Load(new StreamReader(file));
                    var page = from pa in xml.Descendants("Page")
                               select pa.Attribute("ID").Value;
                   
                    var numPage = page.First().Substring(1);

                    var printSpace = from ps in xml.Descendants("PrintSpace")
                                     select ps;
                    var textBlocks = from tb in printSpace.Descendants("TextBlock")
                                     select tb;
                    int counter = 0;
                    foreach (var block in textBlocks)
                    {
                        int tbID = int.Parse(block.Attribute("ID").Value.Substring(6));
                        var textLines = from tl in block.Descendants("TextLine")
                                        select tl;
                        foreach (var line in textLines)
                        {
                            int tlID = int.Parse(line.Attribute("ID").Value.Substring(6));
                            var strings = from str in line.Descendants("String")
                                          select str;
                            foreach (var st in strings)
                            {
                                int strID = int.Parse(st.Attribute("ID").Value.Substring(6));
                                var word = new word()
                                {
                                    PageID = numPage,
                                    textBlockID1 = tbID,
                                    textLineID1 = tlID,
                                    stringID1 = strID,
                                    CONTENT = st.Attribute("CONTENT").Value.Trim(),
                                    HEIGHT = st.Attribute("HEIGHT").Value,
                                    WIDTH = st.Attribute("WIDTH").Value,
                                    HPOS = st.Attribute("HPOS").Value,
                                    VPOS = st.Attribute("VPOS").Value,
                                    CC = st.Attribute("CC").Value,
                                    WC = decimal.Parse(st.Attribute("WC").Value),
                                    BookID = bookID.ToString()
                                };

                                counter++;
                                if (counter < 1000)
                                    db.words.Add(word);
                                else
                                {
                                    db.words.Add(word);                                    
                                    db.SaveChanges();
                                    db.Dispose();
                                    db = new Model1();
                                    counter = 0;
                                }                               
                            }
                        }
                    }
                }
            });           
            db.SaveChanges();
            return true;
        }

פורסם במקור בפורום CODE613 ב28/08/2016 12:34 (+03:00)

dovid

ממש מופלא. שמת נקודת עצירה על הSaveChanges?

אגב לקוד פשוט יותר תוכל לכתוב ככה:

db.words.Add(word);

if (++counter % 1000 == 0)
{
    db.SaveChanges();
    db.Dispose();
    db = new Model1();
}

זה מחליף את 48 ואילך.

פורסם במקור בפורום CODE613 ב28/08/2016 18:35 (+03:00)

ארכיטקט

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

פורסם במקור בפורום CODE613 ב28/08/2016 19:15 (+03:00)

avr416

טוב, אז זו הייתה טעות טיפשית שלי :?
איתחלתי את המונה בתוך הלולאה הראשונה, כך שהוא התאפס כל הזמן לפני שהגיע לאלף איטרציות..
אגב, עכשיו אני רואה שאם אני לא משתמש בכלל במונה ולא מאפס את הEF אז זה לוקח לו הכי מהר (אחרי ששיניתי את ההגדרות של הקונפיגורציה כמובן..) (ל33,000 שורות 16 שניות).
אם המונה פועל על מאה אז זה כמעט אותו זמן, אם הוא על 1000 לוקח לו 32 שניות, אם הוא על 10000 לוקח לו יותר מדקה..
אז נראה שבכלל זה מיותר לאפס אותו ולאתחל אותו מחדש.

פורסם במקור בפורום CODE613 ב28/08/2016 22:43 (+03:00)

dovid

@avr416

אגב, עכשיו אני רואה שאם אני לא משתמש בכלל במונה ולא מאפס את הEF אז זה לוקח לו הכי מהר (אחרי ששיניתי את ההגדרות של הקונפיגורציה כמובן..) (ל33,000 שורות 16 שניות).
אם המונה פועל על מאה אז זה כמעט אותו זמן, אם הוא על 1000 לוקח לו 32 שניות, אם הוא על 10000 לוקח לו יותר מדקה..
אז נראה שבכלל זה מיותר לאפס אותו ולאתחל אותו מחדש.

תודה רבה רבה על הפרטים החשובים!
מאוד מעניין, זה לבטח שינוי בEF כי זה בבירור לא היה ככה בעבר.

פורסם במקור בפורום CODE613 ב31/08/2016 17:44 (+03:00)

avr416

עכשיו ראיתי פוסט יפה בעברית בנושא:

יש לו שם עוד כמה פוסטים בנושא sql server.

פורסם במקור בפורום CODE613 ב13/12/2016 21:22 (+02:00)

תחומים - פורום חרדי מקצועי

מניפולציות על קובץ XML בעת ייבוא לSQL