שיטות מומלצות לפונקציות

במאמר הזה מוסבר איך לייעל שאילתות שמשתמשות בפונקציות SQL.

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

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

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

דוגמה לשימוש בפונקציה REGEXP_CONTAINS:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

אפשר לייעל את השאילתה הזו באופן הבא:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

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

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

אם פונקציית הצבירה של SQL שבה אתם משתמשים כוללת פונקציית קירוב מקבילה, פונקציית הקירוב תניב ביצועים מהירים יותר של השאילתה. לדוגמה, במקום להשתמש ב-COUNT(DISTINCT), צריך להשתמש ב-APPROX_COUNT_DISTINCT. מידע נוסף זמין במאמר בנושא פונקציות צבירה משוערות.

אפשר גם להשתמש בפונקציות HyperLogLog++ כדי לבצע קירובים (כולל צבירות מותאמות אישית של קירובים). מידע נוסף זמין במאמר בנושא פונקציות HyperLogLog++‎ בחומר העזר בנושא GoogleSQL.

דוגמה לשימוש בפונקציה COUNT:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

אפשר לייעל את השאילתה הזו באופן הבא:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

אופטימיזציה של פונקציות קוונטיליות

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

הפעלת שאילתה שמכילה את הפונקציה NTILE עלולה להיכשל עם השגיאה Resources exceeded אם יש יותר מדי רכיבים לORDER BY במחיצה אחת, מה שגורם לנפח הנתונים לגדול. חלון הניתוח לא מחולק למחיצות, ולכן NTILE החישוב דורש ORDER BY גלובלי לכל השורות בטבלה שיעובדו על ידי עובד/משבצת יחידים.

במקום זאת, אפשר לנסות להשתמש ב-APPROX_QUANTILES. הפונקציה הזו מאפשרת להריץ את השאילתה בצורה יעילה יותר, כי היא לא דורשת ORDER BY גלובלי לכל השורות בטבלה.

דוגמה לשימוש בפונקציה NTILE:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

אפשר לייעל את השאילתה הזו באופן הבא:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

הגרסה האופטימלית נותנת תוצאות דומות אבל לא זהות לתוצאות של השאילתה המקורית, כי APPROX_QUANTILES:

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

אופטימיזציה של פונקציות UDF

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

הפעלת UDF ב-JavaScript מחייבת יצירת מופע של תהליך משנה. הפעלת התהליך הזה והרצת ה-UDF משפיעות ישירות על ביצועי השאילתה. אם אפשר, כדאי להשתמש בפונקציה מוגדרת על ידי המשתמש (UDF) מקורית (SQL).

פונקציות UDF מתמידות

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

בדוגמה הבאה מוצג אופן ההפעלה של פונקציית UDF זמנית בשאילתה:

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

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

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;

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

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

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