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

במאמר הזה מפורטות שיטות מומלצות לשיפור הביצועים של השאילתות.

כשמריצים שאילתה, אפשר לראות את תוכנית השאילתה במסוף Google Cloud . אפשר גם לבקש פרטי הרצה באמצעות תצוגות INFORMATION_SCHEMA.JOBS* או ה-method jobs.get ב-API בארכיטקטורת REST.

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

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

הפחתת כמות הנתונים שעוברים עיבוד

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

לא להשתמש ב-SELECT *

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

המונח 'הקרנה' מתייחס למספר העמודות שנקראות על ידי השאילתה. הצגת עמודות עודפות גורמת לפעולות קלט/פלט נוספות (מיותרות) ולמימוש (כתיבת תוצאות).

  • שימוש באפשרויות התצוגה המקדימה של הנתונים. אם אתם מתנסים עם נתונים או מנתחים נתונים, השתמשו באחת מאפשרויות התצוגה המקדימה של הנתונים במקום באפשרות SELECT *.
  • שאילתה של עמודות ספציפיות. החלת פסקה LIMIT על שאילתת SELECT * לא משפיעה על כמות הנתונים שנקראו. החיוב הוא על קריאת כל הבייטים בכל הטבלה, והשאילתה נספרת במסגרת המכסה של התוכנית ללא תשלום. במקום זאת, כדאי לשלוח שאילתה רק לעמודות שאתם צריכים. לדוגמה, אפשר להשתמש ב-SELECT * EXCEPT כדי להחריג מהתוצאות עמודה אחת או יותר.
  • שימוש בטבלאות עם חלוקה למחיצות. אם אתם צריכים להריץ שאילתות על כל עמודה בטבלה, אבל רק על קבוצת משנה של הנתונים, כדאי לשקול:

  • שימוש ב-SELECT * EXCEPT. שאילתה על קבוצת משנה של נתונים או שימוש ב-SELECT * EXCEPT יכולים להפחית באופן משמעותי את כמות הנתונים שנקראים על ידי שאילתה. בנוסף לחיסכון בעלויות, הביצועים משתפרים כי כמות נתוני הקלט/פלט וכמות המימוש שנדרשים לתוצאות השאילתה קטנות יותר.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

הימנעות משימוש יתר בטבלאות עם תווים כלליים

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

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

  • כמה טבלאות עם שמות דומים וסכימות תואמות
  • טבלאות מפוצלות למחיצות Shard

כששולחים שאילתה לטבלת תו כללי לחיפוש, מציינים תו כללי לחיפוש (*) אחרי הקידומת המשותפת של הטבלה. לדוגמה, FROM bigquery-public-data.noaa_gsod.gsod194* השאילתה מחפשת בכל הטבלאות נתונים משנות ה-40.

קידומות עם רמת פירוט גבוהה יותר משיגות ביצועים טובים יותר מקידומות קצרות יותר. לדוגמה, השאילתה FROM bigquery-public-data.noaa_gsod.gsod194* עדיפה על השאילתה FROM bigquery-public-data.noaa_gsod.* כי יש פחות טבלאות שתואמות לתו כללי.

הימנעות מטבלאות עם שברי נתונים לפי תאריך

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

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

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

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

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

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

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

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

צמצום שאילתות עם חלוקה למחיצות

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

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

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

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

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

צמצום הנתונים לפני השימוש ב-JOIN

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

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

בשביל שאילתות עם GROUP BY ו-JOIN, כדאי לבצע צבירה מוקדם יותר בשאילתה כדי לצמצם את כמות הנתונים שעוברים עיבוד. לדוגמה, השאילתה הבאה מבצעת JOIN על שתי טבלאות גדולות בלי לבצע סינון מראש:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

השאילתה הזו מבצעת צבירה מראש של ספירת התגובות, וכך מצמצמת את כמות הנתונים שנקראים עבור JOIN:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

שימוש בסעיף WHERE

שיטה מומלצת: משתמשים בפסוקית WHERE כדי להגביל את כמות הנתונים שמוחזרת בשאילתה. אם אפשר, משתמשים בעמודות BOOL,‏ INT64,‏ FLOAT64 או DATE בסעיף WHERE.

בדרך כלל, פעולות בעמודות BOOL, ‏ INT64, ‏ FLOAT64 ו-DATE מהירות יותר מפעולות בעמודות STRING או BYTE. כדי לצמצם את כמות הנתונים שמוחזרים על ידי השאילתה, מומלץ להשתמש בעמודה שמשתמשת באחד מסוגי הנתונים האלה בסעיף WHERE.

שימוש בתצוגות מהותיות

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

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

שימוש ב-BI Engine

שיטה מומלצת: כדאי להשתמש ב-BigQuery BI Engine כדי להאיץ את השאילתות באמצעות שמירת הנתונים שבהם אתם משתמשים הכי הרבה במטמון.

כדאי להוסיף הזמנה של BI Engine לפרויקט שבו מחושבות השאילתות. ‫BigQuery BI Engine משתמש במנוע שאילתות וקטורי כדי להאיץ את SELECT ביצועי השאילתות.

שימוש באינדקסים של חיפוש

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

אינדקס חיפוש הוא מבנה נתונים שנועד לאפשר חיפוש יעיל מאוד באמצעות הפונקציה SEARCH, אבל הוא יכול גם להאיץ שאילתות באמצעות אופרטורים ופונקציות אחרים, כמו האופרטורים שווים (=), IN או LIKE, ופונקציות מסוימות של מחרוזות ו-JSON.

אופטימיזציה של פעולות בשאילתות

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

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

שיטה מומלצת: אם אתם משתמשים ב-SQL כדי לבצע פעולות ETL, כדאי להימנע ממצבים שבהם אתם משנים את אותם נתונים שוב ושוב.

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

הימנעות מהערכות מרובות של אותם ביטויי CTE

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

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

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

הימנעות מצירופים חוזרים ומשאילתות משנה

שיטה מומלצת: מומלץ להימנע מצירוף חוזר של אותם טבלאות ושימוש חוזר באותן שאילתות משנה.

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

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

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

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

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

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

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

כדי לראות את הגודל של הטבלאות ב-JOIN, אפשר לעיין במאמר קבלת מידע על טבלאות.

ציון אילוצים של מפתח ראשי ומפתח זר

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

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

אופטימיזציה של סעיף ORDER BY

שיטה מומלצת: כשמשתמשים בפסוקית ORDER BY, חשוב לפעול לפי השיטות המומלצות הבאות:

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

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

  • משתמשים בסעיף LIMIT. אם אתם מזמינים מספר גדול מאוד של ערכים אבל לא צריכים שכולם יוחזרו, השתמשו בסעיף LIMIT. לדוגמה, השאילתה הבאה מסדרת קבוצת תוצאות גדולה מאוד ומחזירה שגיאה Resources exceeded. השאילתה ממוינת לפי העמודה title בסדר mytable. העמודה title מכילה מיליוני ערכים.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    כדי להסיר את השגיאה, משתמשים בשאילתה כמו זו שבהמשך:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

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

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    הרצת השאילתה הזו תימשך כ-15 שניות. שאילתה זו משתמשת ב-LIMIT בסוף השאילתה, אבל לא בפונקציה אנליטית (window function) DENSE_RANK() OVER. לכן, השאילתה מחייבת מיון של כל הנתונים בצומת עובד יחיד.

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

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

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

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

פיצול שאילתות מורכבות לשאילתות קטנות יותר

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

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

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

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

שימוש בשדות בתוך שדות ובשדות חוזרים

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

שימוש ב-INT64 סוגי נתונים בשאילתות איחוד

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

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

הפחתת פלט השאילתה

כדי לצמצם את הפלט של השאילתה, אפשר להשתמש באפשרויות שמתוארות בקטעים הבאים.

יצירת תצוגה חומרית של קבוצות גדולות של תוצאות

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

המגבלה על תוצאות שמאוחסנות במטמון ב-BigQuery היא בערך 10GB בדחיסה. שאילתות שמחזירות תוצאות גדולות יותר חורגות מהמגבלה הזו, ולעתים קרובות מובילות לשגיאה הבאה: Response too large.

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

כדי לעקוף את ההגבלה על גודל התוצאה במטמון, אפשר להשתמש באפשרויות הבאות:

  • שימוש במסננים להגבלת קבוצת התוצאות
  • כדאי להשתמש בסעיף LIMIT כדי לצמצם את קבוצת התוצאות, במיוחד אם משתמשים בסעיף ORDER BY
  • כתיבת נתוני הפלט בטבלת יעד

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

הימנעות מתבניות של SQL

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

הימנעות מאיחודים עצמיים

שיטה מומלצת: במקום להשתמש ב-self-joins, כדאי להשתמש בפונקציית חלון (אנליטית) או באופרטור PIVOT.

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

הימנעות מאיחודים צולבים

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

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

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

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

  • משתמשים בפסוקית GROUP BY כדי לצבור את הנתונים מראש.
  • משתמשים בפונקציה אנליטית (window function). לרוב פונקציות אנליטיות יעילות יותר מאשר שימוש בשאילתת איחוד. מידע נוסף זמין במאמר בנושא פונקציות חלון.

אל תשתמשו בפקודות DML שמעדכנות או מוסיפות שורות בודדות

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

שימוש בהצהרות DML ספציפיות לנקודות הוא ניסיון להתייחס ל-BigQuery כמו למערכת עיבוד עסקאות אונליין (OLTP). המיקוד של BigQuery הוא בעיבוד אנליטי אונליין (OLAP) באמצעות סריקות טבלאות ולא חיפושים נקודתיים. אם אתם צריכים התנהגות שדומה לעיבוד טרנזקציות באינטרנט (OLTP) (עדכונים או הוספות של שורה אחת), כדאי לשקול מסד נתונים שנועד לתמוך בתרחישי שימוש של OLTP, כמו Cloud SQL.

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

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

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

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

שימוש בשמות חלופיים לעמודות עם שמות דומים

שיטה מומלצת: כשעובדים עם עמודות עם שמות דומים בשאילתות, כולל שאילתות משנה, מומלץ להשתמש בכינויים לעמודות ולטבלאות.

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

המאמרים הבאים