ייעול פרוצדורת חיפוש

Question

היי

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

הפרוצדורה עובדת בצורה פשוטה יחסית: יש לי עשר טבלאות שמכילות מידע גולמי, בכל טבלה יש:

• עמודה/עמודות לפיהן מתבצע החיפוש
• עמודות החרבה (מידע נוסף אודות תוצאת החיפוש)
• עמודת SCORE (דירוג "איכות" תוצאת החיפוש)

הפרוצדורה לחיפוש, מריצה שאילתה שמבצעת UNION על כל הטבלאות, בכל טבלה מתבצע החיפוש בצורה שונה: לפי אינדקס, לפי FULLTEXT או לפי LIKE.

בסופו של דבר, מתבצע מיון של כל התוצאות לפי עמודת SCORE. כך שהתוצאות הכי רלוונטיות יופיעו בהתחלה. לאחר מכן מתבצע OFFSET X ROWS FETCH NEXT 10 ONLY על מנת לבצע דיפדוף בתוצאות (בסופו של דבר זה נשלח לממשק WEB).

המטרה הסופית היא לבצע חיפוש בפחות משתי שניות.

החיפוש עובד מהר כאשר יש לי מספר נמוך של תוצאות, עד 10 אלף תוצאות פחות או יותר.

כאשר חוזר מספר גבוה של תוצאות החיפוש אורך מעל 10 ו-20 שניות, עבוד 200 אלף תוצאות החיפוש רץ מעל 20 שניות.

כמובן שהבעיתיות היא בגלל ה-ORDER BY שלוקח הרבה זמן בכל הרצה.מה שחשבתי לעשות זה לבנות CLUSTER לפי עמודת SCORE. אבל זה לא פותר לי שום דבר, שכן המיון מתבצע על ה-UNION של כל הטבלאות ולא על כל טבלה בנפרד.

יש למישהו רעיון כיצד ניתן (אם בכלל) לשפר את הביצועים של הפרוצדורה הזו?

תודה!

Answer 1

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

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

נניח שעבור פרמט X<1000 תוכנית ההרצה המיטבית היא A ועבור X>1000 תוכנית ההצרה המיטבית היא B

אם נריץ תחילה את הפרוצדורה עם X=100 אז התוכנית שתישמר לנו תהיה A ולכן ההרצה של X=2000 יכולה לתת תוצאה גרועה (והפוך אם נריץ תחילה עם X=2000)

אחת הדרכים היעילות במקרים כאלה זה להכין 2 פרוצדורות זהות לחלוטין A ו B ולהכין פרוצדורה מנהלת C. כאשר אתה רוצה תוצאה אז אתה מריץ את C. הפרוצדורה C שלך בוחרת את A אם הפרמטר היה 1 ובוחרת את B אם הפרמטר היה 2 וכך קיבלנו 2 תוכניות הרצה שונות מותאמות לפרמטרים.

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

יש אין-סוף אפשרויות בהתאם למערכת שלכם, אנא העבר אלינו DL+DML ונוכל לעיין בנושא יותר לעומק

---

[Personal Site] [Blog] [Facebook]

Answer 2

הי קדפי,

יצירת Clustered Index על עמודת Score בכל הטבלאות בהחלט תעזור לך. כל מה שאתה צריך לעשות זה להוסיף את אותו משפט ORDER BY על כל אחת מהטבלאות בנוסף למשפט ה-ORDER BY החיצוני. בצורה כזאת, כל טבלה תחזיר את 10 התוצאות העי טובות במהירות, ובסוף ישאר לך רק למיין 100 רשומות ולהחזיר את ה-10 הכי טובות.

מה אתה אומר? יעבוד?

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

Answer 3

זה לא שאני שולף 10 רשומות מכל אחת מהטבלאות.

בסופו של דבר זה חיפוש, יכול להיות שממקור הראשון יהיו 200 רשומות שיתאמתו על החיפוש, מהמקור השני 1000 רשומות ומהמקור השלישי בכלל לא יהיו תוצאות..לאחר שיש לי את כל המידע הזה, אני ממיין אותו לפי Score (אחרי UNION) ורק אז אני עושה Fetch next 10 only

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

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

Answer 4

הי,

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

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

ומכאן אני חוזר להצעה שלי... אם תיצור Clustered Index על העמודה Score בכל אחת מהטבלאות (בהנחה שאתה יכול לעשות את זה, וזה לא מייצר לך בעיות אחרות), אז השאילתה שלך תטוס.

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

Answer 5

תודה גלנצר.

זה כנראה מה שאני יעשה.

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

אבל לאור הביצועים כרגע אני יאלץ להגביל את כמות התוצאות שחוזרות למשתמש כמו שאתה הצעת