none
התייעצות לגבי פרוצדורה שמבצעת SELECT מטבלת לוג RRS feed

  • שאלה

  • אהלן.

    התייעצות, אשמח לשמוע את דעתכם.

    יש טבלת לוג גדולה. אני כותב פרוצדורה שתחזיר רשומות מהטבלה לפי פרמטרים שיישלחו אליה:

    • סינון שדה תאריך לפי פרמטרים של מ-עד תאריך.
    • 4 פילטורים על שדות INTEGER
    • פילטור LIKE על שדה nvarchar

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

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

    1. שאילתא כוללת לפי כל המאפיינים בסגנון של AND ErrorCode = ISNULL(@ErrorCode, ErrorCode)
    2. בניית SQL דינאמי לפי הפילטורים (פילטור יתווסף ל-STRING רק אם אינו NULL.
    3. פילטור לפי אחד או שני הפרמטרים מובילים (ע"מ לסנן את רוב הרשומות) ואח"כ על השאר.
    4. דרכים אחרות.

    כמובן שעיקר ההתייחסות צריכה להיות לגבי הביצועים.

    תודה1


    itaigitt, http://copypastenet.blogspot.com

    יום שני 14 מאי 2012 08:14

תשובות

  • הי איתי,

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

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

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

    את הערכים של הפרמטרים עצמם אתה צריך להעביר כפרמטרים לקוד הדינמי, להגדיר את הפרמטרים ולהריץ את השאילתה באמצעות sys.sp_executesql. כך תימנע מאינסוף קומפילציות וגם מפירצה ל-SQL Injection. ברגע שאתה עובד בצורה כזו, אתה מקבל Execution Plan יחיד אופטימלי לכל קומבניציה אפשרית של הפרמטרים, בדיוק כמו בפתרון של הרבה Stored Procedures. אתה לא מאבד את היתרונות של ה-Stored Procedure בשמירת ה-Execution Plans, מכיוון שלכל תבנית של שאילתה (שמייצגת קומבינציה ספציפית של הפרמטרים) יש Execution Plan משלה ששמור ב-Cache יחד עם ה-Query Hash. כל שבכל פעם שה-SQL הדינמי יריץ את אותה תבנית (גם אם ערכי הפרמטרים שונים), יהיה שימוש חוזר באותו Execution Plan.

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

    בהצלחה!

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

    • הוצע כתשובה על-ידי pituachMVP, Editor יום שני 14 מאי 2012 20:44
    • סומן כתשובה על-ידי itaigitt יום שלישי 15 מאי 2012 07:02
    יום שני 14 מאי 2012 18:58
    מנחה דיון
  • הי איתי,

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

    אבל בכל מקרה, בגישה הזאת אתה מקבל Execution Plan יחיד לכל קומבינציה אפשרית של הפרמטרים. לכל קומבינציה כזאת מבנה השאילתה הוא קבוע והוא מכיל פרמטרים (ולא ערכים). זה מה שנמצא ב-Cache. בפעם הבאה שתריץ אץ אותה קומבינציה של הפרמטרים, הפרוצדורה תבנה באופן דינמי את אותה שאילתה בדיוק. ערכי הפרמטרים אמנם יהיו שונים, אבל מבנה השאילתה יהיה זהה לחלוטין. ה-Query Processor יזהה שהשאילתה כבר נמצאת ב-Cache וישתמש ב-Execution Plan שלה.

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

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

    • סומן כתשובה על-ידי itaigitt יום שלישי 15 מאי 2012 07:01
    יום שלישי 15 מאי 2012 06:51
    מנחה דיון

כל התגובות

  • הי איתי,

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

    http://www.sommarskog.se/dyn-search.html

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

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    • הוצע כתשובה על-ידי pituachMVP, Editor יום שני 14 מאי 2012 15:12
    • הצעה כתשובה בוטלה על-ידי pituachMVP, Editor יום שני 14 מאי 2012 20:44
    יום שני 14 מאי 2012 10:58
  • היי.

    אני מכיר את הפוסט הזה.

    לגבי האפשויות שלי, זה יותר 6! או משהו כזה, לא?

    מהבחינת ביצועים, משהו כזה הוא לא מומלץ מן הסתם:

    WHERE ( (Column_DateTime BETWEEN @Param1_FromDate AND @Param2_ToDate) OR @Param1_FromDate IS NULL)
    AND Column1_ID = ISNULL(@Param1_ID, Column1_ID)
    AND Column2_ID = ISNULL(@Param2_ID, Column2_ID)
    AND Column3_ID = ISNULL(@Param3_ID, Column3_ID)
    AND Column4_ID = ISNULL(@Param4_ID, Column4_ID)

    AND Column_String LIKE ISNULL(@Param_String, Column_String)

    ולגבי ה-PLANS, אם אני בונה משהו ב-SQL דינאמי אני מאבל את יתרונות ה-SP בשמירת ה-PLANS, לא?


    itaigitt, http://copypastenet.blogspot.com

    יום שני 14 מאי 2012 14:15
  • אני חושב שזה מה שעמי התכוון ואם לא אז אני מציע את זה:

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

    ככה כל SP יבנה תוכנית הרצה מותאמת למקרה של סינון מסויים ויהיה מיטבי עבור מצב זה.

    למשל SP אחד לסינון לפי Column1_ID בלבד
    ואחד נוסף לסינון לפי Column2_ID בלבד
    וכן הלאה...
    + SP כללי אחד למצב שלא מכוסה

    * אני חושב שזה אמור להיות מיטבי כי נבנה תוכנית מיטבית לכל מצב


    signature

    יום שני 14 מאי 2012 15:09
    מנחה דיון
  • 6 פילטרים פוטנציאללים אומר שיש לך 2 בחזקת 6 אפשרויות = 64. לא נורא - ייקח לך פחות משעתיים לכתוב את כולן ולבדוק.

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

    עמי


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום שני 14 מאי 2012 15:47
  • הי איתי,

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

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

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

    את הערכים של הפרמטרים עצמם אתה צריך להעביר כפרמטרים לקוד הדינמי, להגדיר את הפרמטרים ולהריץ את השאילתה באמצעות sys.sp_executesql. כך תימנע מאינסוף קומפילציות וגם מפירצה ל-SQL Injection. ברגע שאתה עובד בצורה כזו, אתה מקבל Execution Plan יחיד אופטימלי לכל קומבניציה אפשרית של הפרמטרים, בדיוק כמו בפתרון של הרבה Stored Procedures. אתה לא מאבד את היתרונות של ה-Stored Procedure בשמירת ה-Execution Plans, מכיוון שלכל תבנית של שאילתה (שמייצגת קומבינציה ספציפית של הפרמטרים) יש Execution Plan משלה ששמור ב-Cache יחד עם ה-Query Hash. כל שבכל פעם שה-SQL הדינמי יריץ את אותה תבנית (גם אם ערכי הפרמטרים שונים), יהיה שימוש חוזר באותו Execution Plan.

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

    בהצלחה!

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

    • הוצע כתשובה על-ידי pituachMVP, Editor יום שני 14 מאי 2012 20:44
    • סומן כתשובה על-ידי itaigitt יום שלישי 15 מאי 2012 07:02
    יום שני 14 מאי 2012 18:58
    מנחה דיון
  • הרעיון של גיא נשמע טוב

    אני אוהב תמיד גמישות בפיתוח ולכן אני מושך את ההצבעה שלי מהתשובה של עמי לכיוון הרעיון של גיא :-)

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

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


    signature

    יום שני 14 מאי 2012 20:44
    מנחה דיון
  • הי,

    זה נכון שמספיק שיש תו אחד שונה בטקסט הדינמי כדי לקבל Execution Plan חדש, אבל זה לא המקרה. כאן מדובר ב-Stored Procedure אחד שבונה את הטקסט הדינמי פעם אחת ויכול לייצר לכל היותר 64 טקסטים שונים.

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

    כך ה-Stored Procedure שלך צריך להיראות, פחות או יותר:

    DECLARE
    	@nvcStatement	AS NVARCHAR(MAX) ,
    	@nvcParams		AS NVARCHAR(MAX);
    
    SET @nvcStatement =
    	N'
    		SELECT
    			Col1 ,
    			Col2 ,
    			Col3
    		FROM
    			SomeSchema.SomeTable
    		WHERE
    			1 = 1' +
    		CASE
    			WHEN @Param1_FromDate IS NULL THEN N''
    			ELSE N'
    		AND
    			Column_DateTime BETWEEN @DynamicParam1_FromDate AND @DynamicParam2_ToDate'
    		END +
    		CASE
    			WHEN @Param1_ID IS NULL THEN N''
    			ELSE N'
    		AND
    			Column1_ID = @DynamicParam1_ID'
    		END +
    		CASE
    			WHEN @Param2_ID IS NULL THEN N''
    			ELSE N'
    		AND
    			Column2_ID = @DynamicParam2_ID'
    		END +
    		CASE
    			WHEN @Param3_ID IS NULL THEN N''
    			ELSE N'
    		AND
    			Column3_ID = @DynamicParam3_ID'
    		END +
    		CASE
    			WHEN @Param4_ID IS NULL THEN N''
    			ELSE N'
    		AND
    			Column4_ID = @DynamicParam4_ID'
    		END +
    		CASE
    			WHEN @Param_String IS NULL THEN N''
    			ELSE N'
    		AND
    			Column_String LIKE @DynamicParam_String'
    		END +
    		N';
    	';
    
    SET @nvcParams =
    	N'
    		@DynamicParam1_FromDate	AS DATETIME2(7) ,
    		@DynamicParam2_ToDate	AS DATETIME2(7) ,
    		@DynamicParam1_ID		AS INT ,
    		@DynamicParam2_ID		AS INT ,
    		@DynamicParam3_ID		AS INT ,
    		@DynamicParam4_ID		AS INT ,
    		@DynamicParam_String	AS NVARCHAR(1000)
    	';
    
    EXECUTE sys.sp_executesql
    	@statement				= @nvcStatement ,
    	@params					= @nvcParams ,
    	@DynamicParam1_FromDate	= @DynamicParam1_FromDate ,
    	@DynamicParam2_ToDate	= @DynamicParam2_ToDate ,
    	@DynamicParam1_ID		= @DynamicParam1_ID ,
    	@DynamicParam2_ID		= @DynamicParam2_ID ,
    	@DynamicParam3_ID		= @DynamicParam3_ID ,
    	@DynamicParam4_ID		= @DynamicParam4_ID ,
    	@DynamicParam_String	= @DynamicParam_String;
    
    אם בעתיד תרצה להוסיף פרמטר שביעי, אז רק צריך להוסיף עוד שורה עם CASE לטקסט הדינמי, ובזה נגמר כל הסיפור. לעומת זאת, אם היית כותב בעצמך את כל 64 השאילתות, אז בשביל להוסיף פרמטר שביעי, היית צריך לכתוב עוד 64 שאילתות (במידה ואתה רוצה לאפשר את כל הקומבינציות, כמובן).
    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il
    יום שלישי 15 מאי 2012 04:25
    מנחה דיון
  • בוקר טוב ותודה לכולם.

    אני מכיר את הפתרון שהציע גיא ומימשתי אותו בעבר כמה פעמים.

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

    1. SQL דינאמי בפרוצדורה פוגע ביתרונות הפרוצדורה על קוד דינאמי מהקוד מבחינת ה-EXECUTION PLAN. גיא - לה הבנתי איך אני מקבל Execution Plan יחיד אופטימלי לכל קומבניציה אפשרית של הפרמטרים.
    2. אולי אם ככה עדיף לרשום את הקוד ה-SQL-י הדינאמי מהקוד של האפליקציה ולא דרך פרוצדורה?
    3. כמו כן, גם לגבי העניין של לדון במקרים האפשריים ומראש להחליט מה מותר ומה אסור (ולאכוף את זה באפליקציה) - זה אכן היה בתכנון שלי. אני אוהב לפתור בעיות ע"י הבנה שהן בכלל לא קיימות. אם אגיע בצורה כזו למספר בודד של מקרים - אני מניח שעדיף לרשום שאילתא לכל מקרה, לא?

    שוב תודה לכולם.


    itaigitt, http://copypastenet.blogspot.com

    יום שלישי 15 מאי 2012 05:59
  • הי איתי,

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

    אבל בכל מקרה, בגישה הזאת אתה מקבל Execution Plan יחיד לכל קומבינציה אפשרית של הפרמטרים. לכל קומבינציה כזאת מבנה השאילתה הוא קבוע והוא מכיל פרמטרים (ולא ערכים). זה מה שנמצא ב-Cache. בפעם הבאה שתריץ אץ אותה קומבינציה של הפרמטרים, הפרוצדורה תבנה באופן דינמי את אותה שאילתה בדיוק. ערכי הפרמטרים אמנם יהיו שונים, אבל מבנה השאילתה יהיה זהה לחלוטין. ה-Query Processor יזהה שהשאילתה כבר נמצאת ב-Cache וישתמש ב-Execution Plan שלה.

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

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

    • סומן כתשובה על-ידי itaigitt יום שלישי 15 מאי 2012 07:01
    יום שלישי 15 מאי 2012 06:51
    מנחה דיון