none
למה אי אפשר עם משתנה? RRS feed

  • שאלה

  • שלום לכולם,

    כדי לחסוך במילים הכנתי סקריפט

    אני ממש רוצה להשתמש באינדקס אבל גם חייב להשתמש במשתנה, מה עושים?


    CREATE TABLE dbo.aa(a INT)
    INSERT INTO aa VALUES(0),(1),(2),(3),(4),(5),(6),(7)
    GO

    SELECT *
    FROM dbo.aa
    GO

    CREATE INDEX idx ON aa (a) WHERE a > 4
    GO
    -- using the index
    SELECT *
    FROM dbo.aa
    WHERE a > 5
    GO

    -- ignoring the index
    DECLARE @a AS INT = 5

    SELECT *
    FROM dbo.aa
    WHERE a > @a
    GO

    -- cannot make it work
    DECLARE @a AS INT = 5
    SELECT *
    FROM dbo.aa WITH(INDEX (idx))
    WHERE a > @a
    GO

    תודה 

    יום ראשון 26 ינואר 2014 16:57

תשובות

  • הי יניב,

    כל עוד אתה משתמש ב-Filtered Index, יש רק שתי דרכים לגרום ל-SQL Server להשתמש באינדקס כאשר נעשה שימוש בפרמטרים או במשתנים.

    הדרך הראשונה היא להשתמש בקוד דינמי ולשרשר את הערך של המשתנה לתוך השאילתה. בפועל השאילתה שתרוץ תכיל את הערך 5, ולכן היא תתנהג בדיוק כמו השאילתה הראשונה שכתבת.

    DECLARE
    	@a			AS INT	= 5 ,
    	@Statement	AS NVARCHAR(MAX);
    
    SET @Statement =
    	N'
    		SELECT
    			*
    		FROM
    			dbo.aa
    		WHERE
    			a > ' + CAST (@a AS NVARCHAR(MAX)) + N';
    	';
    
    EXECUTE sys.sp_executesql
    	@statement = @Statement;
    GO
    

    הדרך השניה היא לקמפל את השאילתה בזמן ריצה ע"י הוספת (OPTION (RECOMPILE. בצורה כזאת, SQL Server ידע בזמן ריצה את הערך של המשתנה, ומכיוון שהוא צריך לייצר תוכנית ביצוע עבור אותה ריצה ספציפית, הוא יוכל להרשות לעצמו להשתמש ב-Filtered Index.

    DECLARE
    	@a AS INT = 5;
    
    SELECT
    	*
    FROM
    	dbo.aa
    WHERE
    	a > @a
    OPTION
    	(RECOMPILE);
    GO

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי כהן יניב יום שני 27 ינואר 2014 07:42
    יום שני 27 ינואר 2014 06:40
    מנחה דיון

כל התגובות

  • הבעיה פשוטה :-)

    בזצן יצירת האינדקס אתה מבצע סינון רק לרשומות עם a > 4

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

    אם האינדקס שלך היה בלי פילטר הכל היה עובד טוב

    CREATE INDEX idx2 ON aa (a)
    GO
    DECLARE @a AS INT = 5
    SELECT *
    FROM dbo.aa WITH(INDEX (idx2))
    WHERE a > @a



    [Personal Site] [Blog] [Facebook]signature

    יום ראשון 26 ינואר 2014 17:15
    מנחה דיון
  • הי יניב,

    כל עוד אתה משתמש ב-Filtered Index, יש רק שתי דרכים לגרום ל-SQL Server להשתמש באינדקס כאשר נעשה שימוש בפרמטרים או במשתנים.

    הדרך הראשונה היא להשתמש בקוד דינמי ולשרשר את הערך של המשתנה לתוך השאילתה. בפועל השאילתה שתרוץ תכיל את הערך 5, ולכן היא תתנהג בדיוק כמו השאילתה הראשונה שכתבת.

    DECLARE
    	@a			AS INT	= 5 ,
    	@Statement	AS NVARCHAR(MAX);
    
    SET @Statement =
    	N'
    		SELECT
    			*
    		FROM
    			dbo.aa
    		WHERE
    			a > ' + CAST (@a AS NVARCHAR(MAX)) + N';
    	';
    
    EXECUTE sys.sp_executesql
    	@statement = @Statement;
    GO
    

    הדרך השניה היא לקמפל את השאילתה בזמן ריצה ע"י הוספת (OPTION (RECOMPILE. בצורה כזאת, SQL Server ידע בזמן ריצה את הערך של המשתנה, ומכיוון שהוא צריך לייצר תוכנית ביצוע עבור אותה ריצה ספציפית, הוא יוכל להרשות לעצמו להשתמש ב-Filtered Index.

    DECLARE
    	@a AS INT = 5;
    
    SELECT
    	*
    FROM
    	dbo.aa
    WHERE
    	a > @a
    OPTION
    	(RECOMPILE);
    GO

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי כהן יניב יום שני 27 ינואר 2014 07:42
    יום שני 27 ינואר 2014 06:40
    מנחה דיון
  • * בכל מקרה צריך לזכור השלכות של כל פתרון שהוצע.

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


    [Personal Site] [Blog] [Facebook]signature

    יום שני 27 ינואר 2014 12:14
    מנחה דיון
  • גיא,

    תודה על התשובה.

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

    יניב

    יום שני 27 ינואר 2014 14:52
  • יניב זו צריכה להיות דילמה מאוד קלה אם מכירים את המערכת ויודעים כיצד אינדקס עובד.

    דרך אגב, אינדקס "כבד" על טור INT כמו שאתה אומר, ניתן לחשב בדיוק רב הן את המשקל שלו והן את עלויות התחזוקה שלו (החד פעמיות). ההבדל בין מליון רשומות ל 10 מליון יכול להיות עוד פעולת IO אם עברת רמה נוספת באינדקס, ההבדל בין מליארד רשומות למאה מליארד לא יהיה קיים כניראה (תלוי בסוג הטורים והמידע ששומרים כמובן). לעומת זאת השיטות של שאילתה דינאמית או RECOMPILE ופעולת של יצירת תוכנית הרצה בכל הרצה יכול להיות פעולה שכוללת IO + זכרון + מעבד (וזו פעולה יותר קשה לניטור, אבל אפשר גם אותה לנטר כמובן). בסופו של עניין הבחירה שלך כמובן והחלק החשוב ביותר בבחירה הוא הכרה של המערכת והנה של כיצד הדברים פועלים (אינדקס למשל). אני מאחל לך שלא תנסה להריץ שאילתה כזו במערכת פעילה (מערכת שמבצעת מאה אלף SELECT בשנייה עם יצירה של תוכנית הרצה כל פעם מחדש, במקום פעם אחת ביום אינדוקס?!?). כשאני רואה במערכת שימוש בקימפול מחדש זה אומר מבחינתי שיש מקום לבדוק (אני לא פוסל על הסף אבל הניסיון הראה שזה בדרך כלל מצביע על מערכת לא מיטבית וניסיון לעקוף בעיה במקום להתמודד איתה).

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

    ** OFF
    שלח לי מישהו בשנייה זו ב FACEBOOK הודעה בהקשר לשרשור :-) זו אמירה בצחוק אני מניח/מקווה, ואין לה הרבה מקום להשוואה בכלל, אבל התופעה מוכרת.
    "ליצור תוכנית הרצה מחדש כל פעם זה פתרון שמזכיר לי קצת את אלו שכל פעם שיש תקלה מבצעים Restart למחשב והכל עובד לעוד כמה שעות".

    בהצלחה


    [Personal Site] [Blog] [Facebook]signature

    יום שני 27 ינואר 2014 15:39
    מנחה דיון