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

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

כשמריצים שאילתה, אפשר לראות את תוכנית השאילתה במסוף 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* מחפשת בכל הטבלאות מ-1940.

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

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

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

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

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

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

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

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

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

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

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

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

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

בטבלאות עם חלוקה למחיצות לפי יחידת זמן, סינון הנתונים באמצעות _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

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

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

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

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

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

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

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

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

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

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

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

כשמציבים טבלה גדולה בצד ימין של JOIN וטבלה קטנה בצד שמאל של JOIN, נוצרת הצטרפות לשידור. ב-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. לכן, השאילתה מחייבת מיון של כל הנתונים בצומת עובד יחיד.

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

    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 בשאילתות איחוד (join)

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

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

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

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

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

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

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

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

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

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

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

הימנעות מדפוסי SQL שמונעים את השימוש ב-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 ב-BigQuery, כמו UPDATE ו-DELETE, מיועדות לכתיבה מחדש של הנתונים באופן תקופתי, ולא לשינויים בשורה אחת. מומלץ להשתמש במשפט 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, כולל איתור העמודות שבהן נעשה שימוש בשאילתות משנה.

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