יש מה לנסות? SQL ענק ומלא שאילתות וחישובים

[ש.ב.ח.]

שלום וברכה
במשך לאשכול: Having OR Where

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

SELECT
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 16
    ) AS `__column_16`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 17
    ) AS `__column_17`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 21
    ) AS `__column_21`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 22
    ) AS `__column_22`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 21
    ) - (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `meta_records`
        WHERE
            `meta_records`.`record_id` = `records`.`id`
            and `meta_records`.`field_id` = 22
    ) AS `__column_25`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `records` AS `records100`
            INNER JOIN `meta_records` AS `data0` ON `data0`.`VALUE` = `records100`.`id`
            LEFT JOIN `meta_records` ON `meta_records`.`record_id` = `records100`.`id`
            and `meta_records`.`field_id` = 8
        WHERE
            `data0`.`record_id` = `records`.`id`
            and `data0`.`field_id` = '18'
    ) AS `__column_18`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `records` AS `records100`
            INNER JOIN `meta_records` AS `data0` ON `data0`.`VALUE` = `records100`.`id`
            LEFT JOIN `meta_records` ON `meta_records`.`record_id` = `records100`.`id`
            and `meta_records`.`field_id` = 13
        WHERE
            `data0`.`record_id` = `records`.`id`
            and `data0`.`field_id` = '19'
    ) AS `__column_19`,
    (
        SELECT
            `meta_records`.`VALUE`
        FROM
            `records` AS `records100`
            INNER JOIN `meta_records` AS `data0` ON `data0`.`VALUE` = `records100`.`id`
            LEFT JOIN `meta_records` ON `meta_records`.`record_id` = `records100`.`id`
            and `meta_records`.`field_id` = 12
        WHERE
            `data0`.`record_id` = `records`.`id`
            and `data0`.`field_id` = '20'
    ) AS `__column_20`,
    (
        SELECT
            sum(`meta_records`.`VALUE`)
        FROM
            `meta_records`
            INNER JOIN `records` AS `laravel_reserved_0` ON `laravel_reserved_0`.`id` = `meta_records`.`record_id`
            INNER JOIN `meta_records` AS `data0` ON `data0`.`record_id` = `laravel_reserved_0`.`id`
        WHERE
            `records`.`id` = `data0`.`VALUE`
            and `meta_records`.`field_id` = 25
            and `data0`.`field_id` = '24'
    ) AS `__column_26`,
    (
        SELECT
            sum(`meta_records`.`VALUE`)
        FROM
            `meta_records`
            INNER JOIN `records` AS `laravel_reserved_1` ON `laravel_reserved_1`.`id` = `meta_records`.`record_id`
            INNER JOIN `meta_records` AS `data0` ON `data0`.`record_id` = `laravel_reserved_1`.`id`
        WHERE
            `records`.`id` = `data0`.`VALUE`
            and `meta_records`.`field_id` = 28
            and `data0`.`field_id` = '27'
    ) AS `__column_27`,
    `records`.*
FROM
    `records`
WHERE
    `collection_id` = 7
LIMIT
    10 OFFSET 0

שימו לב שישנם גם עמודות עם חישובים בפנים וגם זה יכול להיות ארוך יותר אם יותר חישובים וכו'

[dovid]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

חובה לחשוב על כיוון אחר

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

[ש.ב.ח.]

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

[dovid]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

השאלה אם למישהו פה יש כח וזמן להבין את המקרה ואת הפתרונות?

בלי נדר.
המאמץ להתבטא טוב ישתלם לך גם אם לא יהיה מי שיעמיק.

[ש.ב.ח.]

@dovid אולי אני בכלל טועה והיה עדיף להביא את כל הנתונים מהשרת ולסנן בjs?
גם אם מדובר ב20000 רשומות אולי זה יהיה יותר מהר ופחות כאבי SQL...

[צבי-ש]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

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

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

[dovid]

@ש-ב-ח אם מדובר בצד לקוח, זה לא אופציה.

[יוס]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

אולי אני בכלל טועה והיה עדיף להביא את כל הנתונים מהשרת ולסנן בjs?
גם אם מדובר ב20000 רשומות אולי זה יהיה יותר מהר ופחות כאבי SQL

רק לזכור כמה זמן יכול לקחת להוריד 20,000 שורות

[ש.ב.ח.]

@dovid אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

המאמץ להתבטא טוב ישתלם לך גם אם לא יהיה מי שיעמיק

@צבי-ש אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

ואתה אפילו רק תרוויח

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

בינתיים מילאתי את הDB שלי בסביבת הפיתוח (לוקאלית) עם חצי מליון שורות
ניסיתי להריץ את השאילתה הזאת ועוד כל מיני דומות לאלו וגם אם עוד עמודות מחושבות
זמן התוצאה היה בין כ15 שניות (ללא עמודות מחושבות) ל כ40 שניות (עם עמודה/ות מחושבות)
כך שאפשר להסיק שאי אפשר להמשיך ככה...
אני יושב על רעיונות אחרים כשיהיו לי כאלו אשמח לשתף בעזרת ה'

[ש.ב.ח.]

@dovid אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

אם מדובר בצד לקוח, זה לא אופציה

ניסיתי להתחקות אחרי הפעילות של https://airtable.com/
וכן נראה שרוב הדברים נעשים בצד הלקוח (כמובן שאין בטבלה בדום יותר מ30-40 שורות)
אל אף שאני חושב כמוך מכיוון שאי אפשר להעריך/לקבוע איזה משאבים יש ללקוח הקצה.

[ארכיטקט]

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

[ש.ב.ח.]

@ארכיטקט
אם תוכל להרחיב מעט על מה שכתבת
@ארכיטקט אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

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

ו... מה זה קליקסנס...?

[ארכיטקט]

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

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

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

• לפעמים בן אדם יכול לחיות גם עם נתונים "לא מעודכנים" ואתה מסביר לו מראש שהנתונים עדכניים לכל היותר עד לפני עשרים דקות, אנשים חיים עם זה זה לא סוף העולם.
  בקיצור עבודה קשה לפניך.
  קליק סנס זו פלטפורמה לניתוח נתונים יש לה יכולת להתמודד עם כמויות אדירות של חומר, אממה זה דורש עקומת למידה מסויימת.
  https://www.qlik.co.il/data-analytics/qlik-sense/

[ש.ב.ח.]

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

@ארכיטקט אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

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

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

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

[ארכיטקט]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

מקווה שהבנת.

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

[ש.ב.ח.]

@ארכיטקט אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

הם רשמית לא עומדים בעומס של מעל 50 אלף שורות

כך דעתך גם על google sheets?
(רק מנסה להבין את כיוון הדברים)

[ארכיטקט]

@ש-ב-ח אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

כך דעתך גם על google sheets?

זו לא דעתי, זו הצהרה שלהם ("טבלת אוויר") מול לקוח ששלחתי אליהם (כפי שאני עושה ל 99% מהלקוחות שמתקשרים, אומר להם יש מוצרים בעשרה דולר לחודש לכו לשם כי אצלי החיים קשים, יקרים מאוד, ומתסכלים) הם אמרו לו שאין להם איך לעזור לו כשהמאגר שלו גדול יותר מהסך הנ"ל.
לגבי גליונות גוגול הם מוגבלים רשמית ל 40,000 שורות, 18,278 עמודות 200 טאבים. איך אני יודע? גיגול פשוט באנגלית: google sheets limit rows
בקיצור זה לא עסק.

[ש.ב.ח.]

@ארכיטקט אמר ביש מה לנסות? SQL ענק ומלא שאילתות וחישובים:

לגבי גליונות גוגול הם מוגבלים רשמית ל 40,000 שורות, 18,278 עמודות 200 טאבים

כן, אחרי שכתבתי גיגלתי (למה לא להיפך? ככה!, וגם כי נחמד לדבר איתך )