ייעול פרוצדורה

[24sharon]

ייעול פרוצדורה

שלום,
טוב אני לא מצפה ממש לקוד רק להערות "מלמעלה"
יש לי פרוצדורה שמחזירה עד 6500 רשומות, אבל עם הרבה קישורים לטבלאות משנה מה שיוצר שקריאה לפרוצדורה דרך הmanagment לוקחת 17 שניות
ואני בכלל לא מדמיינת מה יהיה כשיעלה לאוויר.
מצורף כאן תצלום מסך של הפרוצדורה, אשמח להנחיות שאפשר לשפר (אינדקסים, עדכון שיטה וכד')
ברור לי שייעול הפרוצדורה הוא שלב 1 מתוך 2
כי לאחר מכן יהיה לי צורך לייעל גם את השאילתא עצמה, אבל זה יהיה שלב הבא

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

 

[pitoach]

מה שיש לנו זה תמונה ולא פרוצדורה

אנא צרף DDL+DML רלוונטי (טבלאות שקשורות לפרוצדורה וכמובן את הקוד של ה SP עצמו)

 

[24sharon]

המערכת חסמה אפשרות להעלות את הקוד

אנסה להעלות איפשהו לרשת
&nbsp
&nbsp
תודה

 

[pitoach]

 

[גרי רשף]

נתחיל מהשאילתה הראשונה בפרוצדורה

ה-Execution Plan צריך להשתנות בכל פעם בהתאם לערכים של הפרמטרים: אם משתנה פלוני הוא Null - לא לעשות כלום,
ואם לא - לחפש התאמה בתוך שליפה מורכבת (זה חוזר על עצמו מספר פעמים).
ההמלצה (זה מה שחשוב..

)- להפוך את כל השאילתה לדינאמית, ולבנות את ה-Where כך שיכלול רק את מה שרלוונטי, ולבסוף- להפעיל את הקוד הדינאמי שהתקבל (מקווה שההנחייה מספיק ברורה).
במקרה זה- ה-Execution Plan יהיה מותאם ספציפית לפרמטרים.
למשל- אם effect@ שווה למחרוזת ריקה - אפשר להוריד את כל התנאי שבשורה שלו מה-Where.

 

[גרי רשף]

תוספת

בדקי שיש לך ב-strain אינדקס על Published, Deleted,IsActive;
וב-Include שאר העמודות המופיעות ב-Where.

 

[גרי רשף]

ולבסוף (עדיין לגבי הראשונה..)

בכל הטבלאות שפונים אליהן בתוך ה-In (למשל- Review) לדאוג שיהיה אינדקס שהעמודה הראשונה בו תהיה זו שב-Where וב-Include זו שב-Select.
&nbsp
מקווה שההמלצות לגבי האינדקסים ברורות (הסברתי מה לעשות אך לא למה).

 

[24sharon]

תודה רבה.

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

נאמר שם הטבלה שלי היא strains
ואני הוספתי לה שדה שנקרא cachedEffect שאוגר את כל האפקטים על אותו השורה.

דוגמא לנתונים בשדה הזה הוא ",1,3,6,8,9,11,"

בפרוצדורת השליפה אני מקבלת פרמטר "1,9" , ואני מעוניינת שיציג אם קיים 1 או קיים 9
האם יש אפשרות לבצע כזו שליפה בשאילתת where שהרי זה לא like רגיל.

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

שוב תודה

 

[גרי רשף]

במקרה כתבתי על כך פוסט לפני כמה שנים..

http://blogs.microsoft.co.il/gerireshef/2011/08/24/חיתוך-בין-רשימות/
(החלק הרלוונטי לך הוא זה המתחיל במילים: הבעייה הקשה יותר)
&nbsp
רקע טכני קצר: מכיוון שמדובר ב-Or (כלומר- לפחות אחד הערכים נמצא), צריך לבצע Split לכל אחד מהמחרוזות - זו שבטבלה וזו שבקלט - כך שיהפכו ממחרוזת לסט, לבצע בינהן Join, ולראות אם יש התאמות..
הפתרון היעיל במקרה זה (כך יטען בצדק ידידנו Pitoach) הוא להשתמש ב-CLR. אם את מיודדת עם הנושא הזה - לכי על זה.
אני מספק פתרון שהיתרון הגדול בו שלא צריך להתקין כלום ולא צריך שום הרשאה מיוחדת בנוסף למה שכבר יש לך.
בחלק גדול מהמקרים - אין חשיבות ליעילות: מריצים את זה פעם ביום או פעם בחודש, ואם זה ירוץ במשך 2 אלפיות שנייה או 2 שניות - זה זניח. לעומת זאת, אם זה רץ הרבה פעמים או הרבה זמן, אזי יש לשקול את נושא ה-CLR בכובד ראש.

 

[24sharon]

וואו, לא מצאתי שם ידיים ורגליים

וגם האמת לא נראה לי שזה בסופו של דבר ייעל, ואולי עדיף את שאילתת הin כבר ובכלל שדה ה"קאש" לא ממש עוזר.

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

אני חשבתי על סוג של שרשור דינמי של Split לנתונים

לדוגמא
var str = '1=1'
foreach (s in @effect.split(',')){
str + "( and cachedEffect like '%,'" + s + ",%' )"
}
select * from strain where str

האם אי אפשר לבצע כזה דבר בלי לבצע exec לשאילתא?
שאגב אם זה פתרון יותר יעיל אני אלך על זה

 

[pitoach]

שרון, אני י8ותר עצלן אולי מגרי, אבל אני ממליץ לצרף קוד

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

 

[eyalgorithm]

כמה רעיונות

א. כמה מהרשומות עומדות בתנאי הראשון בכל שאילתה (Published=1; Deleted=0; IsActive=1)?
אם מדובר באחוז נמוך מהרשומות - שווה קודם כל לעשות את הסינון הזה, ורק על התוצאה להריץ את התנאים הבאים (לוגיקה מורכבת + חיפושים והמרות של טקסט).
ב. כמה רשומות יש בכל אחת מהטבלאות בתנאים 3-6 בכל אחת מהשליפות? האם ניתן לבצע שם סינון מקדים, כך שה-CHARINDEX יופעל על מספר מצומצם של רשומות?

 

[24sharon]

מנסה עכשיו לצרף

אוקיי עבדתי קשה לייעל, אך בהחלט השליפות איטיות אפילו בעמדת הפיתוח

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

שאילתא ליצירת הטבלאות הרלוונטיות והכנסת נתוני דמה (שזה סדר הגודל של המסד בערך)

 

[24sharon]

שאילתא ליצירת הפרוצדורה

זו פרוצדורת השליפה
מקבלת פרמטרים של
1. שדות מיון
2. אפשרות דפדוף
3. אפשרויות פילטור

 

[24sharon]

וזו הדוגמא להרצה

אפילו בדוגמא הזו השליפה די איטית

ובהתחשב בזה שזה אמור להיות יותר מורכב מבחינת טבלאות ופילטורים

 

[24sharon]

וזו סכימת הנתונים

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

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

תודה

 

[24sharon]

נמצא הפתרון

יצרתי פונקציה שמקבלת מחרוזת ויוצרת טבלה מופרדת

http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

ואז ההתייחסות היא כאל שאילתת IN רגילה

וזה ממש עובד מהר בלי פרופורציות

ממש תודה לכולם על המאמץ