הבלוג של גרי רשף

28/06/2010

Execution Plan: עדכון טבלה אחת על פי טבלה אחרת.

Filed under: Uncategorized — תגיות: , , , , , , — גרי רשף @ 14:37

לפני כשנתיים התחלתי לעבוד במקום עבודה שהשתמשו בו באוראקל ולא ב-SQL Server. קורה..
די מהר ראיתי שיש דמיון רב בין המערכות, והמעבר מהאחת לשניה לא כל כך מסובך, אם כי יש לא מעט מה ללמוד ולהשלים: אולי בגלל תקני ANSI ששתי הסביבות מציתים להן, ואולי בגלל שהמערכת החדשה יותר – SQL Server – השתדלה להציג חלופות משלה לכלים הקיימים וחיקתה אותם בצורה זו או אחרת; ואין לי כוונה לפתוח בדיון סביב השאלה "למי יש גדול יותר?"..
בכל מקרה- התברר לי שיש פקודות SQL שמקובל לכתוב אותן באופן שונה בשתי המערכות. למשל- באוראקל נהוג לכתוב את הטבלאות בשליפת SQL כשמפרידים בינהם פסיקים ואת היחסים בינהם לפרט ב-Where; בניגוד ל-SQL Server שם נהוג להשתמש ב-Join וב-On (מבחינת הביצועים לא אמור להיות הבדל אך מבחינת הבנת הקוד – השימוש ב-Join עדיף לדעתי).
בפקודות Update של טבלה אחת על פי אחרת– כך התברר לי – מקובל באוראקל לכתוב בערך כך:

Update MyTbl1
Set MyFld=(Select .. From .. Where ..)

כאשר שאילתת המשנה מתייחסת בתנאי ה-Where לטבלה החיצונית,
בעוד שב-SQL Server אני הייתי רגיל לעדכן בערך כך (דרך שאינה קיימת באוראקל):

Update MyTbl1
Set MyTbl1.MyFld=MyTbl2.MyFld
From …
Inner Join ..
On…

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

Update (Select … .. From NMyTbl1, MyTbl2 Where ..)
Set MyFld1=MyFld2

SQL Server מאוד סובלני ושלוש הדרכים עובדות אצלו במידה כזו או אחרת של יעילות, כפי שנראה להלן.
נעביר מ-AdventureWorks טבלה גדולה פעמיים- פעם אחת בתור New, פעם בתור Old:

Use tempdb;

Go


If Object_ID('TransactionHistoryArchiveOld') Is Not Null Drop Table TransactionHistoryArchiveOld;

Go


Select    *

Into    TransactionHistoryArchiveOld

From    AdventureWorks.Production.TransactionHistoryArchive;

Go


If Object_ID('TransactionHistoryArchiveNew') Is Not Null Drop Table TransactionHistoryArchiveNew;

Go


Select    *

Into    TransactionHistoryArchiveNew

From    AdventureWorks.Production.TransactionHistoryArchive;

Go

כעת נבחן את שלוש אופציות העדכון של New על פי Old:

Update New

Set        ProductID=Old.ProductID

From    TransactionHistoryArchiveNew New

Inner Join TransactionHistoryArchiveOld Old

        On New.TransactionID=Old.TransactionID;

Go


Update TransactionHistoryArchiveNew

Set        ProductID=(Select    ProductID

                    From    TransactionHistoryArchiveOld Old

                    Where    Old.TransactionID=TransactionHistoryArchiveNew.TransactionID);

Go


Update    T

Set        NewPID=OldPID

From    (Select    New.ProductID NewPID,

                Old.ProductID OldPID

        From    TransactionHistoryArchiveNew New

        Inner Join TransactionHistoryArchiveOld Old

                On New.TransactionID=Old.TransactionID) T;

Go

ונקבל את ה-EP הבא:

image

ניתן לראות שלאפשרות הראשונה והשלישית אותו EP, ובאמת הן די דומות ומדובר בעדכון של Join: בהעדר אינדקסים על הטבלאות – המערכת מבצעת Hash Match לשתיהן (לא יעילה אך היחידה הבאה בחשבון במצב זה),

לערכים המותאמים מתבצע Aggregate כדי להסיר כפילויות (המערכת אינה "יודעת" ש-TransactionID הוא מפתח יחודי),

מכיוון שיש סיכוי שיהיו שורות שיתעדכנו מספר פעמים (כי ה-Join מצא להן מספר התאמות)- נבחר לכל אחת הראשון (Top),

ואז מתבצע העדכון.

בשיטה "האוראקלית" שעולה לפחות פי שלושה משתי האחרות- נתוני ה-Old מועברים לטבלה זמנית בזמרון שמאונדקסת ומתבצע עליה Aggregate לנטרול כפילויות,

בין טבלת New לטבלה הזמנית מתבצע Nested Loops – לכל שורה מ-New מתבצע חיפוש אחר המאימה לה בטבלה הזמנית,

ואז מתבצע העדכון.

השיטה הזו "מאלצת" את המערכת לבצע Nested Loops שכן לכל ערך מ-New יש לחפש את המתאים לו ב-Old,

זה היה יכול להיות יעיל יותר מ-Hash Match לו טבלת ה-Old הייתה מאונדקסת מראש,

אך כעת יש לבצע זאת תוך כדי וזה מה שמייקר אותה (81%).

מה יקרה אם לשתי הטבלאות יהיה Unique Clustered Index על עמודת TransactionID?

ניצור את האינדקסים:

Alter Table TransactionHistoryArchiveOld Add Constraint PK_TransactionHistoryArchiveOld_TransactionID Primary Key Clustered (TransactionID Asc);

GO


Alter Table TransactionHistoryArchiveNew Add Constraint PK_TransactionHistoryArchiveNew_TransactionID Primary Key Clustered (TransactionID Asc);

GO

וכעת ה-EP יראה כך:

image

גם כאן- האפשרות הראשונה והשלישית מתבצעות באופן זהה: מתבצע Clustered Index Scan לשתי הטבלאות (כך מתבצע Scan כשיש Clustered Index),

מכיוון ששתיהן ממויינות (Clustered!)- מתבצע Merge Join יעיל,

והטבלה (=Clustered Index) מתעדכנת.

בשיטה "האוראקלית" העדכון די דומה עם כמה הבדלים קוסמטיים הנובעים מהאופי השונה של משפט ה-SQL,

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

והמחיר של שלוש האפשרויות זהה.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: