שאלת SQL שאני מסתבך איתה

[SpecialNight]

שאלת SQL שאני מסתבך איתה

יש לי טבלה שמייצגת נגן מוזיקה, יש לי 2 שדות: "שם השיר (songName)" ו "מיקום ברשימה (position)". השדה של position מתחיל מאפס.
להלן דוגמה:

songName, position
----------------------------
Song1.mp3, 0
Song2.mp3, 1
Song3.mp3, 2
Song4.mp3, 3


אני מעוניין לעשות פעולה של הזזה של המיקום של שירים, נניח שהייתי רוצה להזיז את Song4 מעל Song2 (ככה שהוא יהיה בין Song1 לבין Song2) אז התוצאה הסופית תהיה ככה:

songName, position
----------------------------
Song1.mp3, 0
Song2.mp3, 2
Song3.mp3, 3
Song4.mp3, 1


כיצד הייתם עושים זאת עם שאילתות SQL? (אני צריך להיות מסוגל להזיז מיקומים גם מלמעלה ללמטה וגם מלמטה ללמעלה).

 

[מתן יונגמן]

שתי פקודות בתוך טרנזאקציה

היי,
בלי להיכנס ל-Syntax, מה שצריך להבין הוא שמדובר בשתי פעולות Update שצריכות להתבצע בצורה אטומית, כלומר או ששתיהן מתבצעות, או שאף אחת מהן לא מתבצעת.
ולכן, פסאודו קוד:

1. פתח טרנזאקציה
2. עדכן מיקום ראשון
3. עדכן מיקום שני
4. אם אין שגיאות, עשה Commit לטרנזאקציה. אם יש, עשה Rollback.

מקווה שעזרתי,
מתן

 

[pitoach]

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

תודה על הרצון לעזור אתמול

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

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

למשל אם רוצים להעלות את Song3 למיקום הראשון. האם נעלה אותו למיקום 1 ונעביר את Song2 למיקום 2 ואז נעביר את Song3 למיקום 0 ונעביר את Song1 למיקום 1?

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

אז מה כן? הדרך הנכונה היא לתכנן את מסד נתונים בצורה יותר נכונה

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

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

הביצוע: אם רוצים להעלות את Song4 להיות בין Song1 לבין Song2 אז צריך לעדכן רק רשומה אחת במסד הנתונים. את הרשומה של Song4. פשוט נותנים לה את המיקום של Song1 + המיקום של Song2 לחלק ל 2 (ז"א במקרה שלנו בפעם הראשנה פשוט ניתן ל Song4 את המיקום 0.5 וראו איזה פלא הרשומה קופצת למקום המתאים מייד

)

* הדבר היחיד שחשוב לוודא לפני הפעולה זה שהמיקום שאנו רוצים לקבוע (במקרה שלנו 0.5 ז"א התוצאה של חישוב המיקום החדש) לא שווה למיקום של הרשומה מעל או מתחת. זה מכסה את הבעיה שנוצרת בגלל עיגול ספרות.
למשל אם בחרנו לעבוד עם טור המוגדר כסוג decimal(38,17) ואנחנו רוצים להכניס רשומה בין מיקום
12345678901234567890.123456789012345677
למיקום
12345678901234567890.123456789012345678
אז לא נוכל מכיוון ששני נתונים אלו מעוגלים לאותו מספר בהגדרת הטור שלנו.
** יש הרבה נקודות חשובות לדון בהן ודרכים לפעול בכל מצב, ש חשיבות להגדרת הטור שלנו כמובן כפי שראינו זה משפיע על אופן העבודה אבל מצד שני זה משפיע על מיקום ועוד הרבה... אבל זו הדרך הבסיסית המתאימה יותר (ועדיין אינה המיטבית אם כי הכי פשוטה)
*** כל מקרה לגופו כמובן וישנם מצבים בו נבחר אפילו לעבוד עם ה DDL הקיים בעוד באחרים יתאים הדרך שתיארתי כן ובעוד מצבים תתאים דרך אחרת, כמו התבססות על טבלה מקושרת או שימוש בטור בינארי או אפילו טור גיאומטרי... לכתוב הכל ייקח לי כמה ימים אז נישאר עם הבסיס: תמיד תתכננו נכון את המערכת שלכם לפני שמתחילים לחפש דרכים לפתור בעיות שלא היו אמורות להיות בתכנון נכון

 

[SpecialNight]

אני מודה לך על התשובה המפורטת! שאלה בעניין

הצגת בעיה שבה הגענו כבר ל 17 ספרות אחרי הנקודה, האם כדאי לי
להריץ JOB פעם ביום שזז על כל הרשומות ומארגן מחדש את הכל? (מ 0.1 עד 0.x)? ככה אוכל למזער כל עדכון לשאילתה אחת בלבד

 

[pitoach]

אם אתה כבר מדבר על מעבר לשרת חי עם הרעיון

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

עכשיו אתה בדרך הנכונה של החשיבה

הנה עוד כמה טיפים:

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

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

* לא תמיד נכון לקבוע את המיספור בצורה רציפה. למה לקבוע הוספה של 1 לכל הודעה? 1 -> 2 -> 3
במערכת עם הרבה הקפצות של הודעות וכשיודעים שהמספר הכללי לא יגיע לגבולות המספר שלנו, אז לפעמים יותר נכון לקבוע שהודעה חדשה מקבלת למשל מיספור שעולה ב 100 בכל פעם 100->200->300.
בצורה זו נוכל להכניס הודעות בין ההודעות הקיימות תוך צורך בפחות מספרים אחרי הנקודה ואם אנחנו במערכת בלי כמעט הקפצות אולי אפילו מספיק להיסתדר עם טור מסוג INT בצורה כזו וב JOB התחזוקה מייצרים את המיספור מחדש פעם ביום/שבוע/חודש

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

אבל אנא תשקיעו עוד כמה "דקות" מחשבה על התכנון.

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

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

 

[גרי רשף]

זה קצת דומה ל-Fill Factor באינדקסים

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

 

[pitoach]

בדיוק

 

[גרי רשף]

הייתי עושה זאת כך..

הנתונים:
If Object_ID('tempdb..#T','U') Is Not Null Drop Table #T;
Go

Create Table #T(I Int Primary Key,
S VarChar(10));


Insert
Into #T
Values (0,'Song1.mp3'),
(1,'Song2.mp3'),
(2,'Song3.mp3'),
(3,'Song4.mp3');

Select * From #T Order By S;


ועדכון בפקודה אחת:
Declare @From Int=3,
@To Int=1;
Update #T
Set I=Case When I=@From Then @To Else I+1 End
Where I Between @To And @From;

Select * From #T Order By S;

זה יעבוד כשמזיזים שיר מלמעלה למטה.
אשאיר לך לטפל במקרה בו מזיזים מלמטה למעלה..

 

[pitoach]

עדיין אתה נאלץ לבצע שינויים על כל הרשומות

ולכן זה לא יעיל.

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

אלו יכולים להיות מליארדי הודעות...

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

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

 

[pitoach]

בהחלט יותר מתאים ל DDL של טור INT

 

[SpecialNight]

תודה לכולם!