none
שאילתא מכמה טבלאות שנמצאות בסכמות שונות RRS feed

  • שאלה

  • שלום לכולם!!

    יש לי data base שמשכפל כמה טבלאות לכל user (בשביל ביצועים)

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

    למשל אם לכל סכמה יש טבלה שקוראים לה orders, אני רוצה שיחפש בכל הסכמות בטבלת orders.

    תודה!!!

    שרה.
    יום שלישי 10 דצמבר 2013 07:56

תשובות

  • היי שרה,

    אם הבנתי נכון את מעוניינת לבדוק אם קיים ערך מסוים במס' טבלאות שונות אך עם סכמה דומה (מס' ושמות העמודות).

    את יכולה לבצע פקודת UNION ALL בין הטבלאות ואז לחפש את הערך ולהתייחס לכל הטבלאות כטבלה בודדה.

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

    -----------------------------
    Tomer Shtrum
    SQL Server consultent
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום שישי 13 דצמבר 2013 15:47
    יום שלישי 10 דצמבר 2013 14:55
  • 1. בהמשך לדברים של תומר (איתם אני מסכים לחלוטין), אני ממליץ לך לחשוב שוב על מדוע הייתם צריכים לבצע את החלוקה לטבלאות. יש לי הרגשה מהשאלה שלך שאת מדברת על מסד נתונים מיזערי שלא עלה על כמה גיגה. שרתי SQL יכולים לעבוד עם כמויות נתונים של מליוני רשומות בקלות ובמהירות אם הן מנוהלות נכון. אני ממליץ לחשוב על כך מעט.

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

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

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

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

    לסיום הנה קישור למדריך בו תוכלי ללמוד ל השמוש ב UNION עליו כתב תור:
    http://www.w3schools.com/sql/sql_union.asp


    [Personal Site] [Blog] [Facebook]signature

    • סומן כתשובה על-ידי Eran Sharvit יום שישי 13 דצמבר 2013 15:47
    יום שלישי 10 דצמבר 2013 17:20
    מנחה דיון

כל התגובות

  • היי שרה,

    אם הבנתי נכון את מעוניינת לבדוק אם קיים ערך מסוים במס' טבלאות שונות אך עם סכמה דומה (מס' ושמות העמודות).

    את יכולה לבצע פקודת UNION ALL בין הטבלאות ואז לחפש את הערך ולהתייחס לכל הטבלאות כטבלה בודדה.

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

    -----------------------------
    Tomer Shtrum
    SQL Server consultent
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום שישי 13 דצמבר 2013 15:47
    יום שלישי 10 דצמבר 2013 14:55
  • 1. בהמשך לדברים של תומר (איתם אני מסכים לחלוטין), אני ממליץ לך לחשוב שוב על מדוע הייתם צריכים לבצע את החלוקה לטבלאות. יש לי הרגשה מהשאלה שלך שאת מדברת על מסד נתונים מיזערי שלא עלה על כמה גיגה. שרתי SQL יכולים לעבוד עם כמויות נתונים של מליוני רשומות בקלות ובמהירות אם הן מנוהלות נכון. אני ממליץ לחשוב על כך מעט.

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

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

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

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

    לסיום הנה קישור למדריך בו תוכלי ללמוד ל השמוש ב UNION עליו כתב תור:
    http://www.w3schools.com/sql/sql_union.asp


    [Personal Site] [Blog] [Facebook]signature

    • סומן כתשובה על-ידי Eran Sharvit יום שישי 13 דצמבר 2013 15:47
    יום שלישי 10 דצמבר 2013 17:20
    מנחה דיון
  • תודה על התגובות!!

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

    לעשות script אני יכולה, חשבתי שיש אולי משהו פשוט יותר... :) 

    אני כן צריכה משהו דינמי כי יש לי הרבה סכמות,

    אבל חשבתי אולי שיש אפשרות להשתמש בsysobjects 

    sysobjects where name = 'tableName' 

    כדי לקבל את כל הטבלאות בכל הסכמות, אבל אין לי מספיק ידע...

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

    כתבתי טבלת orders כדי שיהיה מובן...

    תודה!!!

    שרה.

    יום רביעי 11 דצמבר 2013 07:31
  • בוקר טוב

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

    בכיף,

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

    לעשות script אני יכולה, חשבתי שיש אולי משהו פשוט יותר... :) 

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

    אני כן צריכה משהו דינמי כי יש לי הרבה סכמות,

    אבל חשבתי אולי שיש אפשרות להשתמש בsysobjects 

    sysobjects where name = 'tableName' 

    כדי לקבל את כל הטבלאות בכל הסכמות, ...

    אני לא בטוח שאני מבין את הבעיה כרגע אבל אולי זה יעזור:

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

    2. את בהחלט יכולה לעבוד בשיטה של חיפוש טבלאות בצורה דינאמית ואז להריץ על הטבלאות שאילתה. זו שיטה דיי טובה :-)

    אם זה מה שאת מחפשת אז החלק הבא אמור לעזור.

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

    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
    
    SELECT name
    FROM sys.Tables
    where schema_id = 1 -- Using only default schema 'bo' GO

    שלב ראשון תמצאי את כל הטבלאות.

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

    בקישור הבא תוכלי ללמוד יותר על שימוש בשאילתות דינאמיות (מדריך קצת ארוך אבל מקיף ודיי טוב)
    http://www.sommarskog.se/dynamic_sql.htmlהקישור הבא מראה הכל בסירטון הרכה אחד (לא עברתי על הכל אבל בצצתי כמה שניות בהתחלה ובהמשך וזה ניראה הסבר טוב מאוד)
    http://www.youtube.com/watch?v=MiAwOoelu9k

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

    set nocount on;
    declare @SQL varchar(max) = ''
    select @SQL = 
    	@SQL + '
    	select * from ' + TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
    print @SQL
    execute(@SQL)

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

    * אני ממליץ לחפש עוד חומר בגוגל על שמוש ב sp_executesql ולא לעבוד לפי הדוגמה ששמתי כאן.

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

    הנה דוגמה פשוטה לשימוש ב sp_foreachtable:

    -- שארילתה פשוטה על כל הטבלאות במסד הנתונים
    EXEC sp_MSforeachtable
    	@command1='PRINT ''?''' --  השאילתה שרוצים להפעיל על כל הטבלאות
    
    -- שימוש בפרמטר לסינון חלק מהטבלאות
    exec sp_MSforeachtable
      @Command1 = 'print "?"',  --  השאילתה שרוצים להפעיל על כל הטבלאות
      @whereand = ' and o.name like ''%Currency%''' -- פרמטר לסינון הטבלאות עליהן תופעל השאילתה
    כדי לקבל את כל הטבלאות בכל הסכמות, אבל אין לי מספיק ידע...

    מכאן הנחתי שאתם לא עובדים עם מסדי נתונים גדולים. אני מקווה שההנחה שלי לא פגעה :-)

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

    רק כדי לשים דברים במקום הנכון :-)

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

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

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

    גם לפי ההגדרה הרישמית של מייקרוסופט וגם לפי חברות אחרות כמו אורקל, מסד נתונים עד 10 גיגה נחשב קטן מאוד ולכן הם מספקים לנו כלים בחינם לטפל במסדי נתונים קטנים כאלה (גם לאורקל וגם ל מייקרוסופט יש גרסה חינמית של השרת שלהם שיכולה לעבוד עם מסדים עד 10 גיגה בייט)

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


    [Personal Site] [Blog] [Facebook]signature

    יום רביעי 11 דצמבר 2013 11:39
    מנחה דיון