יצוא מידע מ sql server ב push במקום ב pull.

[זהר פלד]

יצוא מידע מ sql server ב push במקום ב pull.

יש לי לקוח שעובד עם sql server 2008 r2, ובתוכו יש מספר מסדי נתונים, כאשר אחד מהם משמש מקור מידע למסדי נתונים אחרים, חלקם על אותו שרת וחלקם בכלל אצל הלקוחות של אותו לקוח.
במספר מוגבל של טבלאות יש צורך להוציא את המידע החוצה כמעט ברגע שמשהו משתנה בטבלה (עד 2 דקות איחור זה נסבל) - כלומר - נוספת שורה, מתעדכנת שורה או נמחקת שורה (שזה קצת בעייתי, כי בחלק מהמקרים צריך להוציא את כל המידע שנמחק).

עד היום עבדנו איתו בשיטה כזו:
על כל טבלה שנדרש בה עדכון מיידי יש טריגרים ל insert, update ו delete, שמכניסים את ה id של השורה המדוברת (ב delete גם את התוכן שלה) לתוך טבלה יעודית, ויש תהליך שרץ כל דקה, שולף נתונים מהטבלה הזו, רץ על השורות שהוא שלף, שולף את הנתונים הרלוונטיים מתוך הטבלה שהשתנתה, מרכיב XML, ושולח ללקוחות.
אני יודע שזה נשמע סופר מסורבל, ולצערי זה באמת מסורבל, אבל לא אני כתבתי את זה, אז לפחות אני לא מרגיש אשם

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

התחלתי לקרוא קצת על change tracking, אבל נראה לי שזה פתרון מסורבל כמעט באותה המידה.
השאלה היא האם יש איזה שהיא דרך פשוטה, זריזה ובעיקר זולה מבחינת משאבים לדחוף מידע החוצה מה SQL?
(את כל התהליך של יצירת ה XML ושליחה ללקוחות השונים אני מתכוון להשאיר באפליקציה, אני לא רואה טעם להעמיס על ה database את ה overhead הזה).
תודה מראש,
זהר.

 

[כלליים]

אולי

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

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

 

[pitoach]

בלי להיכנס לאפיון שלכם כרגע אלא רק לשאלה

SQL מאפשר עבודה בשיטת push במקום ב pull ולפעמים (הרבה פעמים) אכן זו הדרך הנכונה.

כדי לעבוד ב push עובדים עם service broker שזו אחת מ 2 המטרות הכי חשובות שלו אני מעריך

הערה: אני לא רואה למה דווקא זו הדרך שמתאימה לכם ויש לשבת על האפיון מעט יותר. בעקרון אני לא מבין מדוע את מחפש גישה של push ולהיכן אצתה רוצה לבצע את ה push . גישת של push או של PULL הן גישות למשל כשעובדים ב REST (קשר לקוח-שרת) או בקשר דרת-שרת. במקרים אלו עדיף מבצעים push מהשרת שמתעדכן לשרת או הלקוח שצריך לקבל את העדכון או PULL ממי שצריך להתעדכן כל X זמן

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

 

[pitoach]

*ב REST (קשר לקוח-שרת) או בקשר דרת-שרת ->

ב REST (קשר לקוח-שרת) או בקשר שרת-שרת

 

[זהר פלד]

הדרישה היא להוריד כמה שיותר עומס מה sql.

במקום המצב היום שבו התהליך של הודעה על שינוי דורש גם טריגר שמזין שורה בטבלה נוספת, גם קריאה מהטבלה הנוספת, גם קריאה של כל שורה (בנפרד!) שבה היה שינוי מהטבלה המקורית, שלפעמים גם דורשת קריאה מטבלאות אחרות (שזה טמטום כשלעצמו, בעיקר כאשר מדובר על טבלאות תרגום מפגרות ברמת id / text שמתעדכנות בתדירות הרבה יותר נמוכה אם בכלל) אני מעוניין ליצור מצב שבו השרת "יודיע" לאפליקציה שהיה שינוי, אם אפשר כבר עם הנתונים שהשתנו, והאפליקציה עצמה תדע כבר להרכיב את ה message שנדרש להעביר ללקוח.
אני רוצה לתת דוגמא קטנה לאיך התהליך מתרחש:

יש לנו שלוש טבלאות שקשורות בקשרי גומלין בניהן - למשל Category, SubCategory ו Product.
על כל שינוי ב Product אני אמור לשלוח ללקוח הודעה שכוללת את כל הפרטים של ה product, כולל השם של הקטגוריה ושל תת הקטגוריה שאליהן הוא שייך.

בתהליך הנוכחי, אני צריך לבצע שליפה של join משולש גם אם התבצע שינוי ברמת שדה בודד ב product.

אם ה sql ידחוף את המידע לאפליקציה, אני אוכל להחזיק ב ram את המידע מטבלאות התרגום ולחסוך את ה joins האלו, ובנוסף, אני לא אצטרך אפילו לעשות Select על טבלת ה products כי השינוי כבר ישלח לאפליקציה עצמה. צריך לקחת בחשבון שמדובר על שרת אפליקציות שיש לו ram גדול פי כמה וכמה ממה שצריך לטובת שמירת טבלאות התרגום, ככה שמהבחינה הזו אין בעיה.

בעיניים שלי כמתכנת נראה מאוד ברור שהאופציה של push תהיה יעילה יותר ותכביד פחות על השרת ועל התעבורה ברשת הפנימית, אבל יכול להיות שיש הרבה דברים ברמת ה sql server שאני לא מודע אליהם ושצריך להיות DBA בשביל לדעת.
לכן אני שואל כאן.

 

[pitoach]

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

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

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

שינוי הנתונים מבוצע על ידי מי? אתה רושם "על כל שינוי ב Product אני אמור לשלוח.." אבל מי מבצע את השינוי? האפליקציה או ה DBA ישירות ב SQL? אם האפליקציה היא המקור לשינוי אז מה הבעיה לבצע את הפעולה ברמת האפליקציה ?

טריגרים לא נועדו ליעילות אלא משמשים כקופסאות שחורות (למשל כשיש לך מנכ"ל חברה שמוחק נתונים בלי להבין הרבה ישירות בשרת ואז מפריע לך באמצע הלילה כל כמה ימים ומודיע לך שמישהו מחק בטעות רשומה אז אתה יכול להוסיף טריגר שישלח לך מייל מי ביצע את המחיקה ובמקביל שיחזיר את הנתון אחרי כמה שניות או לא יאפשר את המחיקה בכלל...). הטריגר יושב ברמת השרת ולכן הוא לא נראה לאליקציה שלך וגם לא ל SSMS. כאמור הוא מעין קופסא שחורה למשתמש במסד הנתונים וכאן הכוח החזק שלו.
(*אולי הפיסקה לא מתאימה... כאמור לא קראתי את ההודעה שלך לעומק ורק ראיתי את העניין של גישה כמפתח... בריפרוף מהיר)

 

[זהר פלד]

אוקי, אני אנסה ולסכם לתמצת כמה שיותר:

נתונים:מסד נתונים שמקבל עדכונים אוטומטיים מארבעה מקומות שונים ובנוסף עדכונים ידניים מתוכנת ניהול יעודית ומדי פעם גם ישירות מה SSMS. לכן השיטה של טריגרים נראתה לנו הכי הגיונית - בדיוק בגלל שלא משנה מאיפה השינוי יגיע, אנחנו נוכל לנטר עליו.

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

הגישה הנוכחית - טריגר על כל טבלה שמשתנה שדוחף את שם הטבלה וה id של השורה שהשתנתה לתוך טבלה יעודית במסד הראשי,
ממנה קוראת אפליקציה כל X זמן על מנת לאתר את השינויים שנעשו, והיא זו שאחראית לייצר XML Messages ולשלוח אותן ללקוחות השונים.
(במאמר מוסגר - ה XML מורכב משליפות נתונים מהטבלה שעליה נעשה השינוי ומטבלאות תרגום שונות - ע"ע קטגוריה ותת קטגוריה)

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

הדרישות העיקריות - אמינות ויעילות - אסור שיהיו שינויים שהלקוחות לא מקבלים, ואסור שיהיה יותר מדי זמן המתנה בין השינוי בפועל לבין שליחת ההודעה ללקוח.

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

 

[pitoach]

בהחלט יש בעיה באפיון על פי מה שנראה

אבל הפתרון כל כך מהיר ופשוט שאתה לא תאמין

הנה קובץ וורד עם כמה הערות/הארות על האפיון שאתה מציג לנו וכמובן רעיון לאפיון שונה (כמו שאמרתי כל כך מהיר לביצוע ופשוט שלא יאמן

)
http://www.adrive.com/public/WmYUH7/לזהר_פלד.zip

אני מקווה שזה מעט יעזור
מילת המפתח היא timestemps

 

[pitoach]

הקישור לא יצא טוב הנה נסיון נוסף

אני אעלה את הקובץ כאן. תחליף לו את הסיומת ל ZIP

 

[זהר פלד]

תודה רבה, יש לי רק שאלה אחת:

ב trigger אני יכול לבדוק את ההבדל בין inerted ו deleted על מנת לדעת איזה שדות השתנו (אם בכלל). האם יש לי אופציה דומה בשימוש ב timestamp ?

 

[pitoach]

אתה מתכוון לזהות שינוי בזמן השאילתה?

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

 

[pitoach]

אתה יכול לשמור את הנתון של ה TIMESTEMPS

גם במסד הנתונים ולא רק האפליקציה
למשל לבנות טבלה עם 2 טורים:
סוג DATETIME עם נתון של זמן אחרון שבו בוצע הפעולה של פעם בדקה
סוג טקסט עם נתון של ה TIMESTEMPS הכי גדול מבין הרשומות לפני תחילת הםעולה של פעם בדקה

ככה אתה יכול לפנות לטבלה זו לאתר את הרשומות המתאימות לפי ה TIMESTEMPS המתאים
+
זה נותן זכרון אחרוה של פעולות וזה יכול לשמש לצרכים נוספים

 

[זהר פלד]

אני חושב שמשהו כאן מתפספס.

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

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

 

[pitoach]

אם יש לך TIMESTEMP של העדכון הקודם

אז אתה יודע שכל TIMESTEMP גדול יותר הוא של העדכון הנוכחי

אתה יכול לייצא מה שאתה רוצה בשאילתה אחרי שמצאת את הרשומות. אין קשר. אתה יכול לייצא טור אחד או את הכל או איזה טורים שאתה רוצה

ה TIMESTEMP נועד לעזור לאיתור הרשמות שעודכנו במקום לעשות שימוש בטבלה נוספת שבה רושמים את ה ID של הרשומות שעודכנו

 

[pitoach]

תיקון הקלדה:

אם יש לך TIMESTEMP של העדכון הקודם אז אתה יודע שכל TIMESTEMP גדול יותר הוא של העדכון הנוכחי

אתה יכול לייצא מה שאתה רוצה בשאילתה אחרי שמצאת את הרשומות שעברו את העדכון האחרון. אין קשר לאיזה רשומות אתה מייצא. אתה יכול לייצא טור אחד או את הכל או איזה טורים שאתה רוצה

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

 

[pitoach]

אל תשכח שהכל מתבסס על כך שאתה מבצע

את השמוש בטריגר רק בשביל להכניס את הנתון של ה ID לטבלה נוספת לשימוש עתידי

את הפעולה עצמה אתה מבצע פעם בדקה לפי ה ID שיש בטבלה הנוספת

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

 

[pitoach]

בשיטה הנוכחית אתה לא בודק אם שדה מסויים השתנה

זה כבר אפיון שונה
אתה בודק אם רשומה השתנתה

 

[זהר פלד]

טעות שלי, לא רשמתי את זה, אבל זה כן חלק מהמצב

הנוכחי - כן יש משמעות לאיזה שדה משתנה בשורות שמתעדכנות.

 

[pitoach]

זה אפיון שונה לחלוטין

אני לא יודע אם אוכל להספיק לעבור על זה היום אבל בוא ננסה (מקסימום יש סוף שבוע הבא גם כן)

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

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

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

אפיון לדוגמה שיכול לעזור לך אולי ונכון גם כשרוצים שמירה של היסטוריה, הוא לא לבצע אף פעם עדכון או מחיקה אלא רק הוספה. זה יכול להיות מאוד יעיל. אינדקסים יהיו מיטביים כשהאינדקס הראשי יהיה שדה כמו IDENTITY ולכן לא יהיה צורך לבצע ניקוי אינדקס או פעולות כמו PAGE SPLIT מפני שכל נתון מתווסף בסיום הרשומות הקיימות (אני מדבר על סידור פיזי של הנתונים בדיסק לפי PAGE-ים). במקרה כזה קל מאוד לדעת מה הרשומה ה"פעילה". זו הרשומה האחרונה שיש לנו עם נתון מסויים (וככה גם נשמרת היסטוריה וניתן לדעת בדיוק איזה שדה או מה השתנה בכל רגע ומתי... מומלץ במקרים כאלה גם להחזיק עמודה של זמן והאינדקס צריך לכלול גם אותה למשל)

** ניתן פעם בכמה זמן (יום למשל) להעביר רשומות ישנות לטבלה אחרת של ארכיון או למחיצות אחרות בטבלה.

*** להמשך עבודה אם הרעיונות עד כה עדיין לא מכסים הכל אנא צריף DDL+DML מדוייקים + כיצד אתה פועל כרג כדי שלא נבזבז זמן מיותר.

 

[זהר פלד]

אז ככה:

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