גרי רשף

סוג נתון: Decimal

2013-04-30T18:37:18Z

כשמטפלים במספרים גדולים מאוד מבחינת ערכם המוחלט (כלומר- מספר הספרות משמאל לנקודה) או קטנים מאוד מבחינת ערכם המוחלט (כלומר- מספר הספרות מימין לנקודה) – Decimal הוא אפשרות שתספק את רוב צרכינו; כולל מקרים בהם יש לטפל במספרים שלמים גדולים מאוד שאפילו BigInt מרים ידיים לעומתם..
סוג הנתון הזה מקבל שני פרמטרים המציינים כמה ספרות בסה"כ וכמה מתוכן מימין לנקודה. למשל (Decimal(38,12 מציין שניתן לדייק עד 12 ספרות מימין לנקודה (שברים עשרוניים) ועד 38-12=26 ספרות משמאל לנקודה:

     Declare @I Decimal(38,12)=12345678901234567890123456.123456789012;

    Select  @I;

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

    Declare @I Decimal(38,12)=12345678901234567890123456.123456789012;

    Select  @I/1000000;

    Select  @I*1000000;

כשחילקנו במיליון – הדיוק של 12 ספרות מימין נותר בעינו, וכתוצאה מכך 6 ספרות "הלכו לאיבוד";
וכשכפלנו במיליון – המערכת הקצתה לנו עוד 6 ספרות משמאל אך "קנסה" אותנו ב-6 ספרות מימין.
במקרה זה – המידע שאבד זניח: אם אנחנו מודדים את המרחק מכדור הארץ לשמש לא נתעקש על דיוק של אלפיות המילימטר, ואם עסקינן בחוב החיצוני של ארגנטינה בדולרים – לא נתעקש לציין את מספר הסנטים..
(אם נבצע את התרגיל הנ"ל בעזרת עשרה מיליון – פעולת הכפל תיכשל: זה לא תמיד עובד!)
יחד עם זאת, ל-Decimal יש כמה פינות אפלות. שורו נא:

    Declare @A Decimal(38,10)=22,

            @B Decimal(38,10)=7;

    Declare @A Decimal(38,10)=1000,

            @B Decimal(38,10)=1024;

    Select  @A [A],

            @B [B],

            @A+@B [A+B],

            @A-@B [A-B],

            @A*@B [A*B],

            @A/@B [A/B],

            @A/@B*@B [A/B*B];

בשתי העמודות הראשונות אנחנו רואים ששני המשתנים קיבלו את מספר הספרות המבוקש מימין לנקודה (10).
כשמחברים או מחסירים אותם (העמודות השלישית והרביעית) – מתקבלת התוצאה המבוקשת בדיוק המבוקש,
אך כשכופלים או מחלקים (העמודות החמישית והשישית) מקבלים משום מה דיוק של 6 ספרות מימין לנקודה, כשבמקרה של החילוק זה קריטי כי מידע הולך לאיבוד! ניתן לראות זאת בעמודה השביעית: את A חילקנו ב-B וכפלנו ב-B, לכאורה היינו אמורים לקבל חזרה את A=1000, אלא שבחלוקה סיפרה אחת מימין הלכה לאיבוד (צריך להיות 0.9765625 ולא 0.976562) והמכפלה לא החזירה אותנו לנקודת המוצא.
מה עושים? פתרון אפשרי במקרה זה – קודם לכפול ולאחר מכן לחלק (יש מספיק מקום משמאל לנקודה):

    Declare @A Decimal(38,10)=1000,

            @B Decimal(38,10)=1024;

    Select  @A [A],

            @B [B],

            @A*@B/@B [A*B/B];

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

    Declare @A Decimal(38,20)=1000,

            @B Decimal(38,20)=1024;

    Select @A [A],

            @B [B],

            @A/@B*@B [A/B*B];

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

    Declare @A Decimal(38,20)=1000,

            @B Decimal(38,10)=1024;

    Select  @A [A],

            @B [B],

            @A/@B [A/B],

            @A/@B*@B [A/B*B];

מה קורה כאן? נכון שדומה שהבעייה נפתרה, אבל מה החוקיות כאן? כיצד המערכת קובעת את רמת הדיוק במקרה של חלוקה של Decimals?
לא מדובר כאן בסתם סקרנות אינטלקטואלית: לעיתים יש להפיק דוחות שמתבצעים בהם חישובי ריבית או שיעורי רווח שמצריכים רמת דיוק גבוהה, במקרים אחרים מתעסקים בסכומים הנקובים במיליונים (נניח – 100 מציין מאה מיליון) ואז הספרה השביעית מימין אולי אינה משמעותית אך הדוחות עלולים לא להתאזן; ומה נסביר למנכ"ל? שה-Decimal גנב לנו כמה שקלים?..
ניסיתי לחקור את הסוגייה עד כמה שאפשר, והנוסחה הכי מדוייקת אליה הצלחתי להגיע היא זו:

    MIN(1+X2+Y1,MAX(X2-Y2+Y1-X1,6,(38-MAX(X1,X2))+Y1-Y2))

כאשר המונה הוא (Decimal(X₁,Y₁ והמכנה הוא (Decimal(X₂,Y₂.
למשל:

    Declare @A Decimal(26,5)=1000,

            @B Decimal(38,7)=1024;

    Select  @A [A],

            @B [B],

            @A/@B [A/B];

    X1=26

    Y1= 5

    X2=38

    Y2= 7

    MIN(1+38+5,MAX(38-7+5-26,6,(38-MAX(26,38))+5-7)) = 10

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

הערך Microsoft SQL Server בויקיפדיה: בקשת עזרה מהקוראים

2013-03-23T09:21:39Z

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

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

כתבתי את הערך על פי שיקול דעתי ולא כתרגום של הערך האנגלי, כשהכוונה הייתה ליצור ערך אנציקלופדי עם הסברים מובנים לתם ששואל "מה זאת?", ולא תיעוד טכני או Tutorial (מלכודת שקל ליפול לתוכה).
כלומר- מי ששמע את המושג "SQL Server" ולא יודע אם זה שם של אתר פורנו באינטרנט או שם של ציפור יפנית שניזונה מסושי, או אולי מי שאינו מבין מה ההבדל בין דטבייס וטבלה ובין SQL Server ו-SSMS או סתם רוצה לדעת מה זה Agent (לא איך מקנפגים או מפעילים אלא רק מה זה) – יבוא על סיפוקו..
לא רשימות של features אלא יכולות ומגמות; לא "איך עושים מה" אלא "מה זה מה".

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

תודה רבה מראש!

ניהול שולחנות במסעדה

2013-03-18T19:02:08Z

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

     Use tempdb;

    Go

    If Object_ID('T_Hazmanot','U') Is Not Null Drop Table T_Hazmanot;

    Go

    Create Table T_Hazmanot(ID Int Identity Primary Key,

                            Shulhan Int Not Null,

                            Taarih Date Not Null

                            MiShaa Time Not Null,

                            AdShaa Time Not Null);

    Go

    Insert Into T_Hazmanot Select 1,'20130101','17:00','19:30';

    Insert Into T_Hazmanot Select 1,'20130101','20:00','23:00';

    Insert Into T_Hazmanot Select 2,'20130101','00:00','02:30';

    Insert Into T_Hazmanot Select 2,'20130101','22:00','23:59:59.999';

    Insert Into T_Hazmanot Select 1,'20130102','00:00','23:59:59.999';

    Go

    Select *

    From T_Hazmanot;

יש להוסיף מנגנוני בקרה שיוודאו שאף הזמנה אינה מתחילה לאחר שהסתיימה (כלומר- לוודא ש-MiShaa<AdShaa), ולמצוא פתרון להזמנות שמתחילות ביום אחד לפני חצות ומסתיימות למחרת לאחר חצות (למשל- לפצל את ההזמנה לשתיים).
ההזמנות ליום 01/01/2013 שולחן 1 הם נתונים "רגילים" (שתי הזמנות באמצע הערב),
נתוני שולחן 2 לאותו היום נצמדים לקצוות של תחילת היום וסוף היום,

וליום 02/01/2013 יש הזמנה של שולחן 1 לכל היום, ואין הזמנה של שולחן 2.

     
    With    T As

    (Select Row_Number() Over(partition By Shulhan, Taarih Order By MiShaa) N,

            *

    From    T_Hazmanot)

    Select  IsNull(T1.Shulhan,T2.Shulhan) Shulhan,

            IsNull(T1.Taarih,T2.Taarih) Taarih,

            IsNull(T1.AdShaa,'00:00') MiShaa,

            IsNull(T2.MiShaa,'23:59:59.999') MiShaa

    From    T T1

    Full Join T T2

            On T1.Shulhan=T2.Shulhan

            And T1.Taarih=T2.Taarih

            And T1.N=T2.N-1

    Where   IsNull(T1.AdShaa,'00:00')<>IsNull(T2.MiShaa,'23:59:59.999')

    Order By 1,2,3

קודם כל- יש למספר את ההזמנות לכל יום לכל שולחן בסדר עולה כדי שנוכל להשוות כל הזמנה לזו שאחריה וכך למצוא את החלון בינהן.
לאחר מכן מבצעים Full Outer Join כדי לקבל גם את ההזמנות הראשונות והאחרונות, מחשבים להן את החלונות מתחילת היום או עד סוף היום, ומנפים חלונות באורך 0.
כדאי לשים לב שאין תוצאות ליום 02/01/2013: מבחינת שולחן 1 אין חלונות כלל כי הוא מוזמן לכל היום (הגיוני), ומבחינת שולחן 2 הוא אינו מופיע כי אין אף הזמנה לאותו יום שניתן לחשב חלונות ביחס אליה (קצת פחות הגיוני).. כדי לפתור את הבעייה יש ליצור טבלת תאריכים ולעשות Left Join ממנה ל-Full Join הנ"ל (את זה אשאיר לשף המתלמד הנ"ל לעשות בעצמו..).

Union & Union All

2013-02-18T16:05:18Z

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

     Create Table #T(N Int);

    Go

    Insert Into #T Select 1;

    Insert Into #T Select 2;

    Insert Into #T Select 2;

    Insert Into #T Select 3;

    Insert Into #T Select 3;

    Insert Into #T Select 3;

    Go

    Select * From #T;

והשליפות לבדיקה:

    Select * From #T

    Union  All

    Select * From #T

    Union

    Select * From #T;

    Select * From #T

    Union

    Select * From #T

    Union  All

    Select * From #T;

כלומר- במקרה הראשון התבצע בשלב הראשון Union All ללא Distinct, אך לאחר מכן Union כולל Distinct והפלט הצטמצם לשלוש שורות;
ובמקרה השני התבצע בשלב הראשון Union הכולל Distinct, ולשלוש השורות שהתקבלו התווספו כל השש בשל ה-Union All בהמשך.

כעת אתפנה לטפל באיום האיראני ואשוב לדווח כשאשלים את משימתי (נתנז ובושהר- אני בדרך!).

שליפת "דפים" מטבלה תוך שימוש באינדקס רגיל

2013-02-16T12:51:54Z

בגרסאות 2005 – 2008R2 שליפה של דפים, למשל כמקובל בדפי אינטרנט שמחזירים חלק מהתוצאות, הייתה יכולה להיעשות באמצעות Row_Number: ממספרים את השורות בסט, ובכל פעם מחזירים "מֵאִייָה" אחרת.
אם יש אינדקס על העמודה לפיה התוצאות ממויינות – אזי מה טוב, ניתן לעשות שימוש בו:

     Use tempdb;

    Go

    If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

    Select * Into T_Messages From sys.messages;

    Create Index Idx_T_Messages On T_Messages(Message_ID, Language_ID);

    Select * From T_Messages;

    With T As

    (Select Row_Number() Over(Order By Message_ID, Language_ID) N,

            *

    From    T_Messages)

    Select  *

    From    T

    Where   N Between 201 And 300;

כפי שניתן לראות – המערכת מבצעת Index Scan כדי למצוא באמצעותו את מאה השורות שבדף השלישי (300-201), דא עקא שזה כרוך בביצוע Lookup יקר מאוד לטבלה מכיוון שבאינדקס נמצאים רק הערכים של שתי העמודות ולא של השאר (בניגוד ל-Clustered Index או לשימוש ב-Include), והגרוע מכל- המערכת מבצעת את ה-Lookup על כל 300 השורות הראשונות בטרם תשלוף את המאייה השלישית..
היה הרבה יותר יעיל לו הייתה שולפת מהאינדקס את המאייה הרלוונטית, ורק אז מבצעת Lookup, למשל כך:

    With T As

    (Select Row_Number() Over(Order By Message_ID, Language_ID) N,

            Message_ID,

            Language_ID

    From    T_Messages)

    Select  Tm.*

    From    T

    Inner Join T_Messages Tm

            On T.Message_ID=Tm.Message_ID

            And T.Language_ID=Tm.Language_ID

    Where   T.N Between 201 And 300;

ואם נשווה את הביצועים:

ההבדל בהחלט משמעותי.
מסקנות:
1. עדיף Clustered Index במידת האפשר.
2. אם כבר Index רגיל – לכלול את העמודות האחרות ב-Include.
3. יש מקרים בהם יש צורך "לתחמן" את האופטימייזר כדי לשפר את הביצועים, וזו דוגמה לכך.

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

2013-01-31T07:32:54Z

פנה אלי פלוני בשאלה כדלקמן: יש לו טבלה אליה הוא מכניס שורות בעזרת פרוצדורה בכל פרק זמן נתון, והוא רוצה למספר אותן. נניח שהוא הכניס שורות ביום ובשעת כתיבת שורות, אזי בעמודת התאריך יהיה כתוב 29/01/2013 07:29:15, והוא רוצה בנוסף שהשורות של תאריך זה יהיו ממוספרות. הוא מכיר את אופציית ה-Identity ליצירת מספור אוטומטי, אלא שאופציה זו ממספרת בסדר עולה את כל השורות ולא כל תאריך לחוד.. מה עושים?
בניגוד לפוליטיקאים שעונים על השאלות המופנות אליהן ב-"לא זו השאלה אלא.." ומנסחים שאלה חלופית עליה יש להם תשובה מן המוכן, אני משתדל לענות קודם כל לגופו של עניין גם אם השאלה והתשובה מופרכות; ורק לאחר מכן מנסה לברר למה בעצם השואל שואף להגיע, ולהציע פתרון לבעייה ולא תשובה לשאלה.
ניתן איפוא לאפס את ה-Identity בכל ריצה, וכך בכל פעם השורות החדשות ימוספרו מחדש מ-1 ואילך; ולהקפיד כמובן שעמודה זו לא תהיה עמודת מפתח כמקובל, מכיוון שיש בה כפילויות (אא"כ מדובר בשילוב שלה עם עמודת התאריך).
לדוגמה- טבלה בה נשמור את שמות ומספרי האובייקטים שבטבלת המערכת sys.objects:

     Use tempdb;

    Go

    If Object_ID('tempdb..T1','U') Is Not Null Drop Table T1;

    Create Table T1(ID Int Identity Not Null,

                    [Date] DateTime Default GetDate() Not Null,

                    [Object_ID] Int,

                    Name Sysname);

    Alter Table T1 Add Constraint[PK_T1] Primary Key Clustered([Date],

                                                               ID);

    Insert  Into T1([Object_ID],Name)

    Select  Object_ID,

            Name

    From    sys.objects;

    Select  * From T1;

אם נרצה להריץ שוב- נאפס קודם את ה-Identity:

    DBCC CheckIdent(T1,Reseed,0);

    Insert  Into T1([Object_ID],Name)

    Select  Object_ID,

            Name

    From    sys.objects;

    Select  * From T1;

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

    If Object_ID('tempdb..T2','U') Is Not Null Drop Table T2;

    Create Table T2([Date] DateTime Default GetDate() Not Null,

                    [Object_ID] Int Not Null,

                    Name Sysname);

    Alter Table T2 Add Constraint[PK_T2] Primary Key Clustered([Date],

                                                               [Object_ID]);

    If Object_ID('V2','V') Is Not Null Drop View V2;

    Go

    Create  View V2 As

    Select  Row_Number() Over(Partition By [Date] Order By [Object_ID]) ID,

            *

    From    T2;

    Go

    Insert  Into T2([Object_ID],Name)

    Select  Object_ID,

            Name

    From    sys.objects;

    Select  * From V2;

הפתרון שהוחלט עליו לבסוף היה שילוב של שני הנ"ל: טבלה עם עמודת תאריך ועמודת מונה, אך אכלוס עמודת המונה לא תהיה באמצעות מנגנון ה-Identity אלא Row_Number:

    If Object_ID('tempdb..T3','U') Is Not Null Drop Table T3;

    Create Table T3(ID Int Not Null,

                    [Date] DateTime Default GetDate() Not Null,

                    [Object_ID] Int,

                    Name Sysname);

    Alter Table T3 Add Constraint[PK_T3] Primary Key Clustered([Date],

                                                               ID);

    Insert Into T3(ID,[Object_ID],Name)

    Select  Row_Number() Over(Order By [Object_ID]) ID,

            Object_ID,

            Name

    From    sys.objects;

    Select  * From T3;

כדאי לשים לב שהפרמטר Partition By אינו נדרש בפונקצייה Row_Number במקרה זה מכיוון שבכל פעולת Insert – לכל השורות אותו תאריך. כלומר- גם אם לא נכניס לטבלה כמה מאות שורות אלא כמה מאות מליוני שורות, פעולה שעלולה להימשך לפחות מספר דקות, לכל השורות הנכנסות יהיה אותו תאריך ושעה.

יעילות תנאי In

2012-12-28T19:45:03Z

האופרטור In (כחלק מפסוקית ה-Where) הוא מאוד פופלרי כשרוצים להשוות בין ערך לרשימת ערכים ללא שימוש ב-Or מסובך. למשל- במקום Where @X=1 Or @X=2 Or @X=3 אפשר בקיצור (Where @X In (1,2,3.
יתרוהו הגדול של האופרטור In שהוא פשוט להבנה ונוח לשימוש, ולא פעם משמיצים את ביצועיו שלא בצדק.
כל עוד מדובר ברשימה קצרה של מספר ערכים – אין טעם להתעמק בביצועים, אך מה קורה אם מדובר ברשימה ארוכה?
אני אבדוק שלוש אפשרויות:
1. In עם רשימת ערכים מפורשת.
2. In הפונה לטבלה (ממופתחת ומאונדקסת לעילא ולעילא).
3. חלופה ל-In כאשר מדובר ברשימת ערכים המתקבלת כמחרוזת, וכשידוע שלא ניתן להריץ שאילתות בסגנון של:

     Declare @S Varchar(Max)='1,2,3';

    Select  *

    From    MyTable

    Where   ID In (@S);

נפתח כמקובל עם רשימת המצרכים- טבלת נתונים (T_Messages), וטבלה לחיפוש עבור אפשרות 2 (T_Where):

    Use tempdb;

    Go

    If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

    Go

    Select  *

    Into    T_Messages

    From    sys.messages;

    Alter   Table T_Messages Add ID Int Identity Primary Key;

    Select  * From T_Messages;

    If Object_ID('T_Where','U') Is Not Null Drop Table T_Where;

    Create Table T_Where(ID Int Primary Key);

לחימום הקנה- נשווה בין שתי האפשרויות הראשונות עם ערך אחד:

    Insert Into T_Where Select 1;

    Select * From T_Messages Where ID In (1);

    Select * From T_Messages Where ID In (Select ID From T_Where);

את פעולת ה-Insert נבצע, ובין שתי האחרות נשווה מבחינת ה-Execution Plan:

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

    Truncate Table T_Where;

    Insert Into T_Where Select ID From T_Messages Where ID<=128 And ID Not In (Select ID From T_Where);

    Declare @S Varchar(Max);

    Select @S=IsNull(@S+','+Cast(ID As Varchar),Cast(ID As Varchar)) From T_Where;

    Print @S;

    Select * From T_Messages Where ID In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128);

    Select * From T_Messages Where ID In (Select ID From T_Where);

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

במקרה זה יש כבר יתרון מסויים לעבודה עם טבלה, כך לפחות בשרת עליו בדקתי את הקוד; אך בשרתים אחרים התוצעות יכולות להיות שונות.
עד כמה התוכנית הזו אמינה? למערכת יש נטייה ליצור Execution Plans מופרכים כשאין מספיק נתונים להעריך את עלות הרצת הקוד (למשל- לקוד דינמי), אלא שבמקרה זה המערכת מנתחת את תכולת הסוגריים ויודעת שמדובר ב-128 ערכים.
מה קורה כשמוסיפים גם את האפשרות השלישית ומחפשים את ערכי ID בתוך מחרוזת הכוללת את כל מה שבתוך הסוגריים?
במקרה זה לא ניתן להיעזר ב-Execution Plan ולפיכך נשווה זמני ריצות (שימו לב שבאופציה השלישית המחרוזת אמורה להגיע כפרמטר טקסטואלי):

     
    Set Statistics Time On;

    Select * From T_Messages Where ID In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128);

    Select * From T_Messages Where ID In (Select ID From T_Where);

    Select * From T_Messages Where ',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,' 

           Like '%,'+Cast(ID As Varchar)+',%';

    Set Statistics Time Off;

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

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

מספרים משוכללים ומספרים ידידים

2012-12-12T18:21:55Z

מספרים משוכללים הם מספרים ששווים לסכום המחלקים הקטנים מהם. למשל- 6 מתחלק ב-1,2,3; וסכום הוא 6. 28 מתחלק ב-14,7,4,2,1; וסכומם הוא 28.
מספרים ידידים הם צמדי מספרים שסכום מחלקי האחד שווים למשנהו (ולהיפך). למשל- 220 ו-284 הם מספרים ידידים מכיוון שסכום המחלקים של 220 הוא 284 וסכום המחלקים של 284 הוא 220.
כדי למצוא יצורים משונים שכאלו ניתן להיעזר בלולאות, אך לא נידרדר לשפל כזה כשניתן להשתמש בטבלת מספרים ולהציג פתרון Set based..
נתחיל מהמשוכללים, כשהפתרון כולו "באוויר"; כלומר- בלי ליצור טבלאות ואובייקטים אחרים, ואפילו טבלת המספרים נוצרת תוך כדי מטבלת sys.messages:

     With Nm As

    (Select Row_Number() Over(Order By (Select 1)) N

    From    sys.messages)

    Select  *

    From    Nm Nm1

    Where   N<=1000

            And N=(Select Sum(N) From Nm Nm2 Where Nm2.N<Nm1.N And Nm1.N%Nm2.N=0);

הריצה נמשכה אצלי 7 שניות, לא הרבה; אבל אם רוצים לחפש מספרים משוכללים גדולים מ-1000 כדאי לשקול מעבר לטבלת מספרים עם Primary Key כדי ליעל את שליפת המשנה של סכום המחלקים שבתנאי.

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

    With Nm As

    (Select Row_Number() Over(Order By (Select 1)) N

    From    sys.messages),

    T As

    (Select *,

            (Select Sum(N) From Nm Nm2 Where Nm2.N<Nm1.N And Nm1.N%Nm2.N=0) N0

    From    Nm Nm1

    Where   N<=1000)

    Select  T1.*

    From    T T1

    Inner Join T T2

            On T1.N<T2.N

            And T1.N=T2.N0

            And T1.N0=T2.N;

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

    If Object_ID('P_MisparimYedidim','P') Is Not Null Drop Proc P_MisparimYedidim;

    Go

    Create Proc P_MisparimYedidim @N Int As

    --יצירת טבלת מספרים

    If Object_ID('tempdb..#Nm','U') Is Not Null Drop Table #Nm;

    With Nm As

    (Select Row_Number() Over(Order By (Select 1)) N

    From    sys.messages)

    Select  N,

            Cast(Null As Int) N0

    Into    #Nm

    From    Nm

    Where   N<=@N;

    Alter   Table #Nm Alter Column N Int Not Null;

    Alter   Table #Nm Add Constraint PK_#Nm Primary Key Clustered (N);

    --עדכון סכומי המחלקים

    Update  Nm1

    Set     N0=(Select Sum(N) From #Nm Nm2 Where Nm2.N<Nm1.N And Nm1.N%Nm2.N=0)

    From    #Nm Nm1;

    --שליפת המספרים הידידים

    Select  T1.*

    From    #Nm T1

    Inner Join #Nm T2

            On T1.N<T2.N

            And T1.N=T2.N0

            And T1.N0=T2.N;

    Go

וכעת נחפש זוגות עד 10,000:

Exec P_MisparimYedidim 10000;

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

ריבוי תנאים על אותה עמודה

2012-11-29T17:17:15Z

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

     Select  *

    From    MyTable

    Where   Safa='עברית'

            And Safa='אנגלית'

            And Safa='ספרדית';

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

    Select  *

    From    MyTable

    Where   Safa='עברית'

            Or Safa='אנגלית'

            Or Safa='ספרדית';

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

    Use tempdb;

    If Object_ID('T_Ovdim','U') Is Not Null Drop Table T_Ovdim;

    Create Table T_Ovdim(ID_Oved Int Primary Key,

                        Oved Varchar(10));

    If Object_ID('T_Safot','U') Is Not Null Drop Table T_Safot;

    Create Table T_Safot(ID_Safa Int Primary Key,

                        Safa Varchar(10));

    If Object_ID('T_OvdimSafot','U') Is Not Null Drop Table T_OvdimSafot;

    Create Table T_OvdimSafot(ID_Oved Int Not Null,

                            ID_Safa Int Not Null);

    Alter Table T_OvdimSafot Add Constraint PK_T_OvdimSafot Primary Key Clustered (ID_Oved,ID_Safa);

    Go

    Insert Into T_Ovdim Select 1,'Ana';

    Insert Into T_Ovdim Select 2,'Beni';

    Insert Into T_Ovdim Select 3,'Carmel';

    Insert Into T_Safot Select 1,'Hebrew';

    Insert Into T_Safot Select 2,'Spanish';

    Insert Into T_Safot Select 3,'English';

    Insert Into T_OvdimSafot Select 1,1;

    Insert Into T_OvdimSafot Select 2,1;

    Insert Into T_OvdimSafot Select 2,2;

    Insert Into T_OvdimSafot Select 3,1;

    Insert Into T_OvdimSafot Select 3,2;

    Insert Into T_OvdimSafot Select 3,3;

אנה דוברת עברית; בני דובר עברית וספרדית; וכרמל – עברית, ספרדית ואנגלית.
פתרון אפשרי יכול להיות שיפור של הפתרון עם ה-Or שיכלול ספירה מי מופיע שלוש פעמים:

    Select  O.Oved

    From    T_Ovdim O

    Inner Join T_OvdimSafot OS

            On O.ID_Oved=OS.ID_Oved

    Inner Join T_Safot S

            On OS.ID_Safa=S.ID_Safa

    Where   S.Safa In ('Hebrew','Spanish','English')

    Group By O.Oved

    Having  Count(1)=3;

הבעייה נפתרה, אם כי לטעמי לא באופן אלגנטי: בכל פעם שנשנה את רשימת השפות- נצטרך לשנות את התנאי על מספרן.
האם ניתן להימנע מכך?

    Select O.Oved

    From   T_Ovdim O

    Where Not Exists (Select 1

                     From    T_Safot S

                     Where   Safa In ('Hebrew','Spanish','English')

                             And ID_Safa Not In (Select ID_Safa

                                                From    T_OvdimSafot OS

                                                Where   OS.ID_Oved=O.ID_Oved));

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

    Select O.Oved

    From   T_Ovdim O

    Where  Not Exists (Select 1

                       From   T_Safot S

                       Where  ID_Safa Not In (Select ID_Safa

                                              From   T_OvdimSafot OS

                                              Where  OS.ID_Oved=O.ID_Oved));

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

    Insert Into T_Safot Select 4,'Chinese';

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

    Select O.Oved

    From   T_Ovdim O

    Where  0 Not In (Select  Max(ID)

                     From   (Select Case When OS1.ID_Oved=O.ID_Oved Then 1 Else 0 End ID,

                                    ID_Safa

                             From   T_OvdimSafot OS1) OS

                             Group By ID_Safa);

לגבי כל עובד נכין שליפת משנה שתבדוק אם בטבלת עובדי-שפות יש שפה אותה הוא אינו דובר (ה-Max לכל שפה יהיה 0 אם הוא אינו דובר אותה ו-1 אם כן, ונחפש עובדים להם אין אף 0).
אפשר כמובן לנסח זאת באופן "קריא" יותר, אך יש לשים לב שהתבצעו כאן שני Table Scans הכרחיים ותו לא.

מציאת תת קבוצה שסכומה נתון

2012-11-14T01:16:00Z

שאלה שהגיעה לשולחן המערכת: נתונה רשימת מספרים-

     407.16, 536.82, 1187.03, 923.94, 80.39, 651.46, 1547.90, 119.99, 110.66, 621.76, 62.6, 1616.24, 227.85, 878.06, 824.76, 950.04, 1125.95, 208.80, 2004.48, 3358.20, 1272.52

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

    407.16, 536.82, 1187.03, 1547.90, 119.99, 110.66, 62.6, 1616.24, 227.85, 878.06, 824.76, 1125.95, 2004.48, 3358.20

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

    Declare @S1 Varchar(Max);

    Select  @S1='407.16, 536.82, 1187.03, 923.94, 80.39, 651.46, 1547.90, 119.99, 110.66, 621.76, 62.6, 1616.24, 227.85, 878.06, 824.76, 950.04, 1125.95, 208.80, 2004.48, 3358.20, 1272.52';

    If Object_ID('tempdb..#T') Is Not Null Drop Table #T;

    Create Table #T(Valor Decimal(12,2));

    Create Clustered Index Idx_#T On #T (Valor Desc);

    Declare @I Int;

    While @S1<>''

          Begin

          Set @I=CharIndex(',',@S1+',');

          Insert Into #T(Valor) Select Left(@S1,@I-1);

          Set @S1=Stuff(@S1,1,@I,'');

          End

    Select * From #T;

ננסה קודם כל בכח: לקבוצה בת N איברים יש 2^N תתי קבוצות, ובמקרה זה מדובר ב-2²¹=2,097,152.
נעזר בטבלת המספרים של איציק בן-גן ובטבלה הנ"ל:

    Declare @Cnt BigInt;

    Select @Cnt=Count(1) From #T;

    Select @Cnt=Power(2,@Cnt);

    With lv0 As (Select 0 As g Union ALL Select 0),

         lv1 As (Select 0 As g From lv0 As a CROSS JOIN lv0 As b), -- 4

         lv2 As (Select 0 As g From lv1 As a CROSS JOIN lv1 As b), -- 16

         lv3 As (Select 0 As g From lv2 As a CROSS JOIN lv2 As b), -- 256

         lv4 As (Select 0 As g From lv3 As a CROSS JOIN lv3 As b), -- 65,536

         lv5 As (Select 0 As g From lv4 As a CROSS JOIN lv4 As b), -- 4,294,967,296

         Nums As (Select Row_Number() Over (Order By (Select Null)) As n From lv5),

    T As

    (Select Row_Number() Over(Order By Valor) N,

            *

    From    #T),

    S As

    (Select Sum(Valor) Over (Partition By Nm.N) STotal,

            Nm.N Nm_N,

            T.N T_N,

            T.Valor

    From    T

    Inner Join (Select * From Nums Where N<=@Cnt) Nm

            On Nm.N%Power(2,T.N)>=Power(2,T.N-1))

    Select  *

    From    S

    Where   STotal=14007.70

    Order By STotal,

            Valor;

אצלי הריצה נמשכה כ-1:50 דקות.

בגדול- לכל מספר בין 0 (קבוצה ריקה) ל-2,097,152 (הקבוצה כולה) התאמנו את תת הקבוצה שהוא מייצג, חישבנו לכל תת קבוצה כזו את הסכום, ושלפנו את אלו שהסכום הקבוצתי שלהם מתאים למבוקש.
אפשרות חלופית כוללת שימוש ברקורסיה, ולשיפור הביצועים נציין ליד כל מספר את סכום המספרים ממנו ומטה. השיטה תהיה כדלקמן: נשלוף את כל המספרים הקטנים מ-14007.70 ושסכום המספרים מהם ומטה גדול מ-14007.70 (כלומר- יש סיכוי שתת קבוצה מהם ומטה תהיה שווה למבוקש), מכל אחד ממספרים אלו נמשיך ברקורסיה למספרים מתחתיו שקטנים מהשארית (לאחר שהחסרנו מ-14007.70 את המספר הקודם שצורף) ושהסכום מהם ומטה גדול מהשארית, וכך הלאה.
קודם כל נוסיף עמודה לטבלה ונתקן את האינדקס:

    Drop Index Idx_#T On #T;

    Alter Table #T Add Total Decimal(12,2);

    Create Clustered Index Idx_#T On #T (Valor Desc, Total Desc);

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

    Declare @Valor Decimal(12,2),

            @Total Decimal(12,2),

            @Rc Int;

    Select  @RC=1,

            @Total=0;

    Select  @Valor=Min(Valor),

            @Total=@Total+Min(Valor)

    From    #T

    Where   Total Is Null;

    While   @Valor Is Not Null

            Begin

            Update T

            Set Total=@Total

            From   (Select Top 1 *

                    From   #T

                    Where  Valor=@Valor

                           And Total Is Null) T;

            Select  @Valor=Min(Valor),

                    @Total=@Total+Min(Valor)

            From    #T

            Where   Total Is Null;

            End

והשליפה הרקורסיבית:

    Declare @S2 Decimal(12,2);

    Select @S2= 14007.70;

    With T As

    (Select Valor,

            Total,

            Cast(@S2-Valor As Decimal(12,2)) STotal,

            Cast(Valor As Varchar(Max)) SValor

    From    #T

    Where   Total>=@S2

    Union All

    Select  T1.Valor,

            T1.Total,

            Cast(T.STotal-T1.Valor As Decimal(12,2)),

            Cast(T.SValor+','+Cast(T1.Valor As Varchar(Max)) As Varchar(Max))

    From    T

    Inner Join #T T1

            On T.Valor>=T1.Valor

            And T.Total>T1.Total

            And T.STotal<=T1.Total

    Where   T.STotal-T1.Valor>=0)

    Select  *

    From    T

    Where   STotal=0;

הפעם הריצה נמשכה פחות משנייה.

להרחבה ניתן לעיין בויקיפדיה.

שימוש באינדקס לחיפוש לפי עמודה משנית

2012-11-08T01:53:33Z

כאשר יוצרים אינדקס על טבלה לפי עמודה1, עמודה2, עמודה3; הדעת נותנת שהוא יהיה שימושי כשנבצע חיפוש או מיון לפי עמודה1, עמודה2, עמודה3..
מה יקרה אם נחפש לפי עמודה2 דווקא? למשל- יש לנו טבלת מכירות בה העמודה הראשית באינדקס היא התאריך, והעמודה המשנית המוכר; ואנחנו מעוניינים לבצע חיפוש לפי מוכר..
השכל הישר (שלי לפחות) אומר שזה תלוי בסלקטיוויות של העמודה הראשית: אם הסלקטיוויות נמוכה אז כדאי ואם היא גבוהה – לא. כלומר- אם בטבלת המכירות הנ"ל יש רק שני תאריכים שונים (על פני הרבה מאוד שורות) ואנחנו מחפשים את המוכר X, המערכת תבצע Seek ממוקד ותחפש אותו בעזרת האינדקס פעם בתאריך הראשון ופעם בשני; ואם יש הרבה מאוד תאריכים כבר עדיף לבצע Scan מלא על כל הטבלה.
מסתבר למרבה הצער (וכפי שיומחש בהמשך) שאין זה כך: גם אם הסלקטיוויות נמוכה מאוד המערכת תוותר על השימוש באינדקס ותבצע Scan מלא.
לצורך ההמחשה נבנה טבלה בעמודה הראשית יש רק ערך אחד (1), כלומר- אין סלקטיוויות כלל, ונבצע חיפוש לפי העמודה המשנית בה יש ערכים שונים:

     Use tempdb;

    Go

    If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

    Go

    Select  1 Col1,

            Row_Number() Over(Order By GetDate()) Col2,

            *

    Into    T_Messages

    From    sys.messages;

    Alter Table T_Messages Alter Column Col1 Int Not Null;

    Alter Table T_Messages Alter Column Col2 Int Not Null;

    Go

    Alter Table T_Messages Add Constraint PK_T_Messages Primary Key Clustered (Col1, Col2);

    Go

    Select * From T_Messages;

כעת נחפש את השורה בה מתקיים Col2=21 בשתי דרכים שונות:

    Set Statistics IO On;

    Select  *

    From    T_Messages

    Where   Col2=21;

    Select  *

    From    T_Messages

    Where   Col1=1

            And Col2=21;

    Set Statistics IO Off;

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 1, logical reads 2853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

למרות שיש רק ערך אחד בעמודה הראשית Col1 המערכת ביצעה Scan מלא לטבלה למרות שאולי הייתה יכולה לדעת ש-Col1=1 בהכרח גם אם זה לא צויין במפורש.

נעשה בדיקה נוספת, אך הפעם נגדיל את הסלקטיוויות ב-Col1 ל-100 כך:

    Update  T_Messages

    Set     Col1=Col2%100+1;

    Select * From T_Messages;

וכעת ננסה למצוא את Col2=21 במספר דרכים:

    Set Statistics IO On;

    Select  *

    From    T_Messages

    Where   Col2=21;

    Select  *

    From    T_Messages

    Where   Col1=22

            And Col2=21;

    Select  *

    From    T_Messages

    Where   Col1 In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100)

            And Col2=21;

    Select  *

    From    T_Messages

    Where   Col1 Between 1 And 100

            And Col2=21;

    Set Statistics IO Off;

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 1, logical reads 3827, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 0, logical reads 312, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Table 'T_Messages'. Scan count 1, logical reads 3799, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

כאשר יש תנאי רק על העמודה השניה- המערכת מבצעת Scan מלא, וניתן לראות זאת גם ב-Execution Plan וגם ב-Statistics IO (מספר ה-Logical Reads).
כאשר יש תנאי על שתי העמודות (לא חוכמה, אבל עשיתי זאת לצורך ההשוואה עם השליפות הבאות) השליפה כמובן יעילה מאוד, מבצעת Seek ממוקד, ושולפת את הנתון המבוקש מהר (Statiscs IO) ובזול (Execution Plan).

מה שמעניין הוא מה שקורה בשליפה השלישית בה אנו מציבים תנאי טריוויאלי על העמודה הראשונה ומציינים את כל הערכים האפשריים שלה (יש להניח שאיננו יודעים מה הערך של Col1): כמובן שהשליפה פחות טובה מהשניה, אבל הרבה הרבה יותר טובה מהראשונה. היא ניגשה אחד-אחד לכל מאה הערכים השונים של Col1 ועבור כל אחד מהם ביצעה Seek. זו הסיבה שמספר ה-Logical Reads שלה הוא פי מאה משל השניה.
השליפה הרביעית בודקת מה קורה אם אנחנו עושים לעצמנו "חיים קלים" ובמקום לציין במפורש את כל הערכים האפשריים של העמודה הראשונה אנחנו מציינים את הטווח שלה כדי שיהיה עליה תנאי. הביצועים שהיו אמורים להיות לכאורה כמו בשליפה השלישית – דומים יותר לאלו של השליפה הראשונה, אם כי קצת יותר טובים. לפי ה-Statistics IO ניתן להעריך שלאחר שסיימה לחפש את Col2=21 ב-Col1=100 ה-Scan הסתיים ולכן יש קצת פחות Logical Reads.
לסיכום- יש חשיבות רבה לסדר העמודות באינדקס, בעיקר כשמשתמשים רק בחלק מהן. לו היה מתבצע חיפוש רק על Col1 המערכת הייתה יכולה לנצל את האינדקס לשם כך, אך כשהחיפוש הוא על Col2 – היא אינה משתמשת בו, גם כשהיא יכולה עדיין להפיק ממנו תועלת (בשל סלקטיוויות נמוכה בעמודה הראשית).
כדי "לאלץ" אותה להשתמש באינדקס ניתן ליצור תנאי מלאכותי על העמודה הראשית בעזרת האופרטור In ורשימת כל הערכים האפשריים (אנחנו מניחים שמדובר בסלקטיוויות נמוכה). מדוע המערכת אינה עושה זאת בעצמה? אין לדעת..

איך נדע אם המערכת מבצעת Table Scan מלא

2012-11-07T00:09:33Z

פרסמתי לפני מספר ימים פוסט שהשווה את הביצועים של In ושל Not In עם החלופות השונות, וציינתי מספר פעמים שהמערכת במקרה של In מבצעת Table Scan (בהנחה שאין אינדקס מתאים) עבור כל שורה מהטבלה הראשית כדי לבדוק אם היא נמצאת במשנית, ועוצרת את החיפוש כשהיא מוצאת כי אין צורך לחפש התאמות נוספות כשדי באחת כדי להציג אותה;
ובמקרה של Not In מבצעת באופן דומה אך הפוך Table Scan עבור כל שורה מהטבלה כדי לבדוק אם היא במשנית, וכשהיא מוצאת היא עוצרת כי די בכך כדי לא להציג אותה.
אם לצטט פתגם סיני עתיק- כדי לדעת שהביצה מקולקלת אין צורך לאכול אותה עד תום, ומספיק לטעום כדי המינימום ההכרחי..
מישהו טען באוזני שלדעתו (או לדעת אותו מקור עלום אותו הוא ציטט) מתבצע בכל מקרה Scan מלא ולכן In אינו יעיל.
זה כמובן אינו נכון, לא צריך להמציא ל-In חסרונות שאינם קיימים ואפשר להסתפק במה שיש.
כיצד נבדוק איפוא אם המערכת מבצעת Table Scan מלא בכל מקרה או רק עד לנתון המתאים שמאשר או מונע את הצגת השורה? לשם כך ניצור טבלה עם מוקש בתוכה, שתשמש בתור הטבלה המשנית:

     If Object_ID('tempdb..#T2','U') Is Not Null Drop Table #T2;

    Create Table #T2(ID Tinyint,

                     M As 12./(4-ID));

    Insert Into #T2 Select 1;

    Insert Into #T2 Select 2;

    Insert Into #T2 Select 3;

    Insert Into #T2 Select 4;

    Go

המוקש טמון בעמודה המחושבת M שהנוסחה שלה יוצרת שגיאה במקרה של ID=4 (חלוקה באפס).
כל עוד לא שלפנו את השורה הרביעית אינה בעייה, אך כשנשלוף אותה נקבל הודעת שגיאה.
בנוסף- בהיעדר אינדקסים בכלל ו-Clustered Index בפרט, השורות שמורות בטבלה על פי סדר הכנסתן, וכך הן גם נשלפות.
נעיין להמחשה בארבע השליפות הבאות ששולפות בכל פעם מספר גדול יותר של שורות:

    Select Top 1 * From #T2;

    Select Top 2 * From #T2;

    Select Top 3 * From #T2;

    Select Top 4 * From #T2;

המערכת שלפה על פי הסדר- שורה אחת, שתי שורות, שלוש שורות, ובנסיון לשלוף ארבע שורות הצליחה לשלוף רק שלוש וקיבלה שגיאה ברביעית.
האם אפשר לסמוך במאה אחוז שתמיד זה כך? תיאורטית כנראה שלא, ולו הייתי שולח חללית מאויישת למאדים לא הייתי בונה על כך בקוד שהיה נכתב עבור הנחתתה על הכוכב השכן, אבל מכל בחינה מעשית- זה עובד, ובפרט בטבלה זעירה שכזו שמתכווצת ב-Page בודד.
כעת ניצור טבלה ראשית בה תהיה שורה אחת בלבד, ולאחר מכן נראה מה קורה כשהמערכת מנסה למצוא או לא למצוא אותה (In ו-Not In בהתאמה) בטבלה המשנית הנ"ל:

    If Object_ID('tempdb..#T1','U') Is Not Null Drop Table #T1;

    Create Table #T1(M Tinyint);

    Go

לתוך הטבלה נכניס ערך בודד – 12 (ערך שמופיע בעמודה M בטבלה המשנית):

    Insert Into #T1 Select 12;

    Go

וכעת נבדוק אם M מהראשית נמצא במשנית (כן!) ותוך כדי כך נוודא שה-Scan נעצר כשאותו ערך נמצא:

    Select *

    From   #T1

    Where  M In (Select M

                From #T2);

מה היה קורה לו הוא לא היה נמצא והמערכת הייתה מבצעת Scan מלא אחריו עד הסוף?
נכניס גם את הערך 99 שאינו קיים בטבלה המשנית:

    Insert Into #T1 Select 99;

    Go

    Select *

    From   #T1

    Where  M In (Select M

                From #T2);

במקרה זה המערכת מצאה ללא כל שגיאה ש-12 נמצא בטבלה המשנית,
אך כשניסתה למצוא את 99 הגיעה עד לשורה הרביעית עם הערך השגוי והחזירה הודעת שגיאה.
כך הם פני הדברים גם עם Not In: נבצע שליפת Not In עם ערך שנמצא ונראה שהמערכת תמצא אותו, תפסיק לחפש ותחזיר סט ריק; וכננסה עם ערך שאינו נמצא – היא תחפש אותו עד הסוף ואז תקבל שגיאה והריצה תיפול.
נתחיל עם ערך שקיים:

    Truncate Table #T1;

    Insert Into #T1 Select 12;

    Go

    Select *

    From   #T1

    Where  M Not In (Select M

                    From #T2);

שוב- המערכת הגיעה עד ל-12 שבטבלה המשנית, עצרה, ולא עלתה על "המוקש".
כעת נוסיף ערך שאינו קיים (ולכן מקיים את תנאי Not In) אך שמחייב לבדוק את כל הטבלה המשנית:

    Insert Into #T1 Select 99;

    Go

    Select *

    From   #T1

    Where  M Not In (Select M

                    From #T2);

כעת המערכת גילתה קודם כל ש-12 ישנו ולכן לא הציגה אותו,
אך עלתה על "המוקש" כשבדקה אם 99 נמצא;
והמסקנה- המערכת אינה מבצעת Scan מלא בבדיקות In ו-Not In, אלא רק עד לנתון הרלוונטי הראשון.

האופרטור In / Not In: חלופות, וביצועים

2012-11-01T01:32:23Z

כשהייתי בראשית דרכי ב-SQL Server, שמעתי באחד הכנסים ש"בדקו ומצאו" שהשימוש ב-Inner Join מעט יותר יעיל מאשר השימוש ב-In. אינני זוכר בוודאות ממי שמעתי את זה, אך עובדה שבמשך שנים התייחסתי לכך כאל תורה מסיני, ואף ציטטתי זאת בידענות פה ושם.
לאחרונה כשנזכרתי בכך, קמטתי את מצחי ותהיתי האמנם.. דווקא הנסיון והידע שצברתי מאז הובילו אותי לחשוב כיום שאולי דווקא In יעיל יותר: בניגוד ל-Join בו לכל שורה מצד ה-From יש למצוא את כל ההתאמות בצד ה-Join, ב-In יש למצוא רק את ההתאמה הראשונה.. ואם כבר- אז מה עם השימוש ב-Exists? האם מדובר רק ב-In משופר או אולי לא רק?
ואם כבר- מה קורה במקרה ההפוך של Not In?
בקיצור- צריך לבדוק!
יצרתי שלושה העתקים של טבלת sys.Objects (קישור לכל הקודים שבפוסט):
1. Heap
2. Index
3. Primary Key Clustered Index
יצרתי חמישה העתקים של טבלת sys.tables:
1. Heap
2. Index
3. Clustred Index
4. Primary Key Clustered Index
כעת ביצעתי 3*4=12 בדיקות של ה-Execution Plans של טבלת Objects מול טבלת Tables, פעם ב-In, פעם ב-Join ופעם ב-Exists:

     Select *

    From   T_Objects_

    Where  object_id In (Select object_id

                        From    T_Tables_);

    Select O.*

    From   T_Objects_ O

    Inner Join T_Tables_ T

           On O.object_id=T.object_id;

    Select *

    From   T_Objects_ O

    Where  Exists (Select 1

                  From    T_Tables_ T

                  Where   T.object_id=O.object_id);

בשני המקרים אני מחפש שורות מ-Objects שנמצאות גם ב-Tables,
ולהלן תוצאות האמת (כולל קולות החיילים):

ברוב המקרים ה-Execution Plans זהים וגם הביצועים, למעט כאשר יש ל-Objects אינדקס פשוט.
רקע כללי כדי להבין את הסיבה לכך: על פניו נראה ש-In יעיל יותר, מכיוון שאז המערכת אמורה לחפש לכל שורה מ-Objects את ההתאמה הראשונה, בעוד שבמקרה של Join המערכת תצטרך למצוא את כל ההתאמות (ולא רק את הראשונה).
יחד עם זאת, הודות לסטטיסטיקות, המערכת יכולה לדעת שהערכים בכל אחת מהטבלאות הם יחודיים פחות או יותר, גם כשאין אינדקסים שמבטיחים זאת (Primary Key), ויוצרת Execution Plans דומים.

להמחשה, נתבונן בסטטיסטיקות של Tables_1 ו-Objects_A (שתיהן Heaps):

    DBCC Show_Statistics(T_Tables_1,'object_id') With Histogram;

    DBCC Show_Statistics(T_Objects_A,'object_id') With Histogram;

ניתן לראות בעמודה הימנית (Avg_Range_Rows) שכל ערך Object_ID מופיע פעם אחת.
ההבדל באותם מקרים בהם היחס הינו 28:45:28 או 33:35:33 הוא שהמערכת מעדיפה לבצע Group By או Sort על טבלת Tables במקרים של In ושל Exists כדי להבטיח שהערכים יהיו יחודיים וממויינים, דבר שלא תוכל לעשות במקרה של Inner Join בו יש למצוא את כל ההתאמות ולא רק את הראשונה.
שוב- במקרה זה הביצועים בדרך כלל זהים, אם כי במספר מקרים יש יתרון ל-In ול-Exists.
מאי נפקא מינה מקרה זה? במה הוא שונה ממקרים אחרים?
אם בטבלת Tables הערכים לא יהיו יחודיים התוצאות של Inner Join יהיו גרועות יותר מכיוון שהמערכת תצטרך לחפש את כל ההתאמות ולא תוכל להעריך שתהיה רק אחת. לכך יש להוסיף מקרים בהם המשתמש יבצע Select * מיותר במקרה של Join ואז השליפה של Tables תהיה מסורבלת יותר.
סיכום ביניים חלקי: אם אנחנו רוצים לדעת לאילו שורות בטבלה אחת יש שורות מתאימות בטבלה השניה- In ו-Exists עדיפות.
גם במקרה ההפוך בו נרצה למצוא אילו שורות מ-Objects אינן ב-Tables התוצאות יהיו דומות, כאשר השאילתות יהיו כדלקמן:

    Select *

    From   T_Objects_

    Where  object_id Not In (Select object_id

                            From    T_Tables_);

    Select O.*

    From   T_Objects_ O

    Left Join T_Tables_ T

           On O.object_id=T.object_id

    Where  T.object_id Is Null;

    Select *

    From   T_Objects_ O

    Where  Not Exists (Select 1

                      From    T_Tables_ T

                      Where   T.object_id=O.object_id);

התוצאות והמסקנות דומות לקודמות, והשינויים הקטנים באחוזים נובעים מהתמחור של תנאי ה-Where במקרה של Left Join.
עד כאן ניתן לצאת עם כותרת בסגנון של Myth Busters ולהזים את השמועות.
היכן ה-Catch? כדאי לשים לב שאת הטבלאות יצרנו על ידי פקודות Select Into, ובכולן עמודת המפתח Object_ID הוגדרה כ-Not Null (מפתח חייב להיות Not Null אך לא בכל הטבלאות הוא הוגדר ככזה). אם נשנה את ההגדרה ל-Null היכן שאפשר, תהיה לכך השפעה דרמטית על הביצועים והתוצאות.
הקוד מופיע בסקיפט המצורף, ויש רק להדגיש שבטבלאות T_Objects_C & T_Tables_4 בהן הוגדרו Primary Key לא ניתן לשנות את העמודות ל-Null ולכן השארתי אותן על כנן.
בתוצאות במקרה של In לא חלו שינויים דרמטיים (שינויים קוסמטיים באחוזים):

אך שורו הביטו וראו מה קורה במקרה של Not In:

הביצועים של In בכל הגזרות (למעט כאשר בשני הצדדים יש עמודות מסוג Not Null) מתדרדרות פלאים, וה-Execution Plan הופך להזוי; למשל במקרה בו שתי הטבלאות הן Heap (ללא אינדקסים):

מתבצעות שלוש פניות שונות לטבלת Tables, ולמרות כל מאמצי לא הצלחתי לרדת לפשרן.
ה-Table Scan האדום הוא היקר ביותר (32%) והוא משתמש ל-Nested Loops מול Objects שבסופו כל השורות מ-Objects שאין בהן ערך Null עוברות הלאה (כרגע העמודה מוגדרת כ-Null אך בפועל אין בה ערך כזה). לא ברור לי מדוע מתבצע Table Scan ב-Tables, וזה כך גם כש-Tables ריק.
ה-Table Scan הכחול סופר את השורות מ-Tables בהן יש Null (כנ"ל לגבי ה-Null), ושומר את התוצאה בטבלה זמנית, ומבצע Nested Loops עם הפלט של האדום. כלומר- כל שורה מהפלט של האדום מושווית מול התוצאה באותה טבלה זמנית, ורק אם הסכום הוא 0 – היא ממשיכה הלאה.
ה-Table Scan הירוק כל שורה מ-Objects (זה שליד האדום) שצלחה את שני הקודמים (כלומר- היא עצמה אינה Null וב-Tables אין אף Null) נבדקת סופסוף מול Tables ואם אינה שם (הפעם בלי "להתקטנן" על כך שערך קיים אינו שונה מ-Null) היא עוברת הלאה.
כדי להבין מדוע יש טיפול נפרד בערכים וב-Nulls נהפוך בכל טבלה שורה אחת ל-Object_ID=Null (הקוד בסקריפט המצורף), והפעם לא נבדוק את הביצועים אלא את הפלט – כמה שורות חוזרות:

כעת כבר לא מדובר בענייני ביצועים (דבר שלפעמים הוא זניח כשמדובר בשרת חזק ובטבלאות לא גדולות) אלא בפלט המוחזר: מסתבר ש-Not In אינו מחזיר שורות כשיש Null, ולשיטתו Null גם אינו שווה וגם אינו שונה מערך כלשהו, ולכן לא חוזר דבר. במילים אחרות- כל הערכים מ-Objects מושווים מול Null שב-Tables, אף אחד מהם אינו שונה ממנו, ולכן אף ערך לא מוחזר. לוגית זה נכון מפני ש-Null אינו ערך, אך מעשית לא תמיד לכך אנחנו מצפים..
דווקא Left Join ו-Not Exists מחזירים פלט "הגיוני" המתיישב בדרך כלל עם האינטואיציה שלנו (אינטואיציה האומרת ש-Null "שונה" מ-2 למשל..).
סיכום (הפעם סיכום מלא וסופי):
1. In ו-Exists יעילים יותר מ-Join מכיוון שהם מוצאים את ההתאמה הראשונה ולא את כל ההתאמות (בתנאי שמדובר בעמודות המוגדרות כ-Not Null).
2. Not In הוא מאוד לא יעיל כשמדובר בעמודות המוגדרות כ-Null.
3. Not In מחזיר תוצאות לא צפויות כשמדובר בעמודות המוגדרות כ-Null (נכונות לוגית אך לא אינטואיטיבית).
כתוצאה מכל זה- מומלץ להשתמש ב-Exists שבכל הנסיבות הוא יעיל יותר (או לא פחות) מהחלופות ותוצאותיו אינטואיטיביות; כל זאת בצד יכולות משופרות שאין ל-In. יחד עם זאת, הסינטקס שלו מורכב יחסית לזה של In, ומפתחים מתחילים עלולים להסתבך מעט בשימוש בו.

הקודים השונים שנעשה בהם כאן שימוש

התאמות בין מחרוזות

2012-10-23T00:21:09Z

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

     Create Table #T(ID Int Primary Key, 

                    Nm Varchar(20));

    Go

    Insert Into #T Select 1,1230007590

    Insert Into #T Select 2,5656781123

    Insert Into #T Select 3,3235577910

    Insert Into #T Select 5,0235879421

    Insert Into #T Select 4,8224479590

    Go

    Select * From #T;

כעת יש להשוות את חמש המחרוזות שבטבלה למחרוזת 123456789 ולבדוק כמה התאמות יש במקומות.
למשל- במחרוזת הראשונה 1230007590 יש שש התאמות (הספרות 1,2,3,7,9,0).
יש בוודאי דרכים שונות לעשות זאת, הפעם אשתמש בטבלת מספרים, אבדוק עבור אילו מספרים יש התאמה, ואסכם לגבי כל מחרוזת:

    Declare @Num Varchar(20);

    Set @Num='1234567890';

    With Numbers As

    (Select Top 100 Row_Number() Over(Order By GetDate()) N

    From    sys.messages),

    T1 As

    (Select Numbers.N,

            T.*

    From    Numbers

    Right Join #T T

            On SubString(T.Nm,Numbers.N,1)=SubString(@Num,Numbers.N,1)

            And Numbers.N<=Len(T.Nm)

            And Numbers.N<=Len(@Num))

    Select  ID,

            Nm,

            Count(N) Cnt

    From    T1

    Group BY ID,

            Nm;

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

"הלב" של השליפה הוא T1 שמחפש את כל המספרים עבורם יש התאמה, ולמעשה מפרק את המחרוזות לסט של תווים בודדים. אני משתמש ב-Right Join במקום ב-Inner Join כדי לא לאבד מחרוזות עבורן אין התאמות.
שליפת ה-Group By שבסוף מקבצת מחדש את הסט המפורק לפי המחרוזות המקוריות, וסופרת כמה התאמות יש בכל אחת.

הרצת פקודות דינמיות: Exec מול SP_ExecuteSQL

2012-10-11T00:59:03Z

כשמריצים SQL דינמי ניתן לעשות שימוש באחת משתי פרוצדורות המערכת הנ"ל.
ה-DBA-ים הוותיקים בוודאי זוכרים במעורפל משהו על כך ש-SP_ExecuteSQL עדיפה מפני שהיא יכולה למחזר Execution Plans, אך מה זה בדיוק אומר ומי מבין השתיים עדיפה ומתי?
נפתח ברשימת המצרכים- טבלה עליה נוכל להריץ את הקודים הדינמיים:

     Use tempdb;

    Go

    If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

    Go

    Select * Into T_Messages From sys.messages;

    Select * From T_Messages;

וכך נשלוף בעזרת SQL דינמי (זו דוגמה מאולצת להמחשה מכיוון שבמקרה זה אין באמת צורך ב-SQL דינמי):

    Declare @SQL NVarchar(Max);

    Select @SQL='Select * From T_Messages Where message_id=21;'

    Exec(@SQL);

    Exec SP_ExecuteSQL @SQL;

    Go

כדאי לציין ש-Exec תסתפק במשתנה Varchar ואילו SP_ExecuteSQL תדרוש NVarchar; אך אלו לא יותר מזוטי דברים, וניתן לדלג במקרה הצורך מעל המהמורה הזו בקלות.
מה קורה אם נרצה ליצור את הפקודה הדינמית "באוויר" (On the fly)?

    Declare @SQL NVarchar(Max),

            @ID NVArchar(Max);

    Select  @SQL='Select * From T_Messages Where message_id=',

            @ID='21';

    Exec(@SQL+@ID);

    Exec    SP_ExecuteSQL @SQL+@ID;

    Go

הפקודה הדינמית נכשלה בגלל ה-SP_ExecuteSQL: היא צריכה לקבל את הפקודה מוכנה להגשה, בעוד שה-Exec יכול לשרר את המחרוזות תוך כדי. למה זה טוב? בימי קדם, כשהשתמשנו ב-SQL 2000 ובשורת ה-(Nvarchar(Max טרם הגיעה לשרתינו, פקודות דינמיות היו מוגבלות כעקרון לאורך של 4000 תווים, שזו הייתה מגבלת ה-Nvarchar. הפתרון היה להשתמש ב-Exec גם מפני שהוא יכול היה להריץ Varchar שאורכו הגיע ל-8000 תווים, וגם מפני שניתן היה לפצל את הפקודה הדינמית למספר משתנים ולשרשר אותם במהלך ביצוע ה-Exec.
למי שעדיין משתמש ב-SQL 2000 – חשוב לזכור זאת, ומי שלא- יוכל גם במקרה זה להסתדר מצויין גם עם SP_ExecuteSQL.
ומה לגבי אותה שמועה שגונבה לאוזנינו לגבי מיחזור של Execution Plans?
ננסה להריץ עם כל אחת מהפרוצדורות שתי פקודות Select, כל אחת עם פרמטרים שונים (לא לפני שננקה את ה-Cache):

    DBCC FreeProcCache;

    Go

    Exec('Select * From T_Messages Where message_id=20;');

    Go

    Exec('Select * From T_Messages Where message_id=21;');

    Go

    Exec SP_ExecuteSQL N'Select * From T_Messages Where message_id=20;';

    Go

    Exec SP_ExecuteSQL N'Select * From T_Messages Where message_id=21;'; 

    Go

וכעת נבדוק בטבלאות המערכת מה המצב:

    Select  [text],

            usecounts

    From    sys.dm_exec_cached_plans CP

    Cross Apply sys.dm_exec_sql_text(CP.plan_handle) ST

    Where   [text] Like 'Select * From T_Messages Where message_id=%';

חזיז ורעם- יש לנו שני Execution Plans ב-Cache שכל אחד בוצע פעמיים: פעם עם הפרמטר 21 ופעם עם הפרמטר 20, ללא קשר לשאלה אם השתמשנו ב-Exec או ב-SP_ExecuteSQL..
אכן כן- כשהמחרוזות זהות המערכת יודעת למחזר את ה-Execution Plan הקיים, אך כשיש הבדל – ואין זה משנה אם הפרמטר הפך מ-21 ל-20 או אם הוספנו תו רווח תמים בין ה-* ל-From..
אם כך- מה בכל זאת ההבדל בין השתיים? (וכאן אנחנו מגיעים ללב העניין)
ל-SP_ExecuteSQL יש אופציות הפעלה נוספות זולת הנ"ל, שאינן קיימות ב-Exec (שוב נקפיד לנקות ה-Cache):

    DBCC FreeProcCache;

    Go

    Exec('Select * From T_Messages Where message_id=20;');

    Go

    Exec('Select * From T_Messages Where message_id=21;');

    Go

    Exec SP_ExecuteSQL N'Select * From T_Messages Where message_id=@ID;',

                       N'@ID Int',

                       @ID=20;

    Exec SP_ExecuteSQL N'Select * From T_Messages Where message_id=@ID;',

                       N'@ID Int',

                       @ID=21;

כלומר- הפעלה כך שהפרמטר מועבר מבחוץ ובאופן מפורש (יש להגדיר את הפרמטר).
מה אומרות טבלאות המערכת?

    Select  [text],

            usecounts

    From    sys.dm_exec_cached_plans CP

    Cross Apply sys.dm_exec_sql_text(CP.plan_handle) ST

    Where   [text] Like '%Select * From T_Messages Where message_id=%';

מהשורה הראשונה אפשר להתעלם- היא מתייחסת לשליפה עצמה מטבלאות המערכת ולא להרצה של הקוד הדינמי.
לגבי הקוד הדינמי- ניתן לראות שהשימוש ב-SP_ExecuteSQL יצר Execution Plan אחד שההמערכת השתמשה בו פעמיים, ואילו השימוש ב-Exec יוצר בכל פעם Execution Plan חדש, אלא אם כן הרצנו אותה פקודה בדיוק נמרץ (גם ללא הבדלים זניחים ברווחים וכו').
יש להדגיש- היכולת הזו קיימת כשמפעילים קוד דינמי כנ"ל הכולל בתוכו פרמטרים פנימיים, ולא בקוד דינמי שנוצר בעזרת פרמטרים חיצוניים. למשל- לא ניתן ליצור טבלה כך ((Create Table @Tbl (ID Int, Txt Varchar(Max (כאשר @Tbl הוא פרמטר המקבל ערך כלשהו), אלא יש ליצור קודם כל את הפקודה הדינמית תוך שירשור הפרמטר לפקודת ה-Create ורק אז לבצע אותה, ובמקרה זה אין כל יתרון לשימוש ב-SP_ExecuteSQL.
ליתרון שהוזכר קודם יש להוסיף את היכולת של SP_ExecuteSQL להעביר תוצאה של חישוב מתוך הקונטקסט של הקוד הדינמי החוצה, יכולת שכתבתי עליה בעבר.
לסיכום: כשמריצים "סתם" קוד דינמי, אין הבדל משמעותי בין שתי הפרוצדורות, ואולי אפילו יתרון קטן במקרים מיוחדים ל-Exec. לעומת זאת, במקרים מאוד ספציפיים, בהפעלה פרמטרית של SP_ExecuteSQL ניתן להפיק תועלת מהיכולת שלה למחזר את ה-Execution Plans שב-Cache.