זמן ריצה של קוד

מומחה באקסס

שלום וברכה
יש לי קוד באקסס שרץ על 6000 רשומות (דרך RecordSet)
זמן הריצה של הקוד הוא בערך 6-7 דקות
במהלך הריצה היישום נתקע ולא מגיב, ולכן גם אי אפשר לקבל חיווי על מצב ההתקדמות
אני רוצה לווסת את הקוד ולגרום לו לעבוד אפילו ביותר זמן אבל בצורה 'רגועה' בלא לתקוע את הקובץ
ניסתי להריץ לולאה אחרת 'פשוטה' (בלי הרבה משתנים ומניפולציות) שהיא גם לקחה 6 דקות, אבל רצה לי בנחת בלי להיתקע
אני מבין שישנם גורמים מסויימים שגורמים לקוד הארוך - גם להיתקע
האם מישהו יודע מה גורם לקוד 'ארוך' להיות גם 'מסורבל'?

OdedDvir

@מומחה-באקסס אמר בזמן ריצה של קוד:

יש לי קוד באקסס שרץ על 6000 רשומות (דרך RecordSet)

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

האם מישהו יודע מה גורם לקוד 'ארוך' להיות גם 'מסורבל'?

המתכנת...
אם תשתף אותנו בקוד ננסה לכוון אותך יותר.

מומחה באקסס

@odeddvir
במקרה הזה ספציפית דוקא בדקתי על ידי שאילתה
לקח לי פי שנים זמן
מדובר בסך הכל על עדכון נתונים על פי Dcount ו Davg
מצו"ב הקוד
זמן הריצה שלו על 6000+ רשומות, הוא בערך 3.5 דקות
המסך שחור כל הזמן
יש לי גם פקד שמציג התקדמות (פס ירוק שמתקדם) והוא אמור להיות מעודכן מערכי הלולאה, אבל כיון שהמסך נתקע זה לא משתקף
תודה!

Dim rs As Recordset
DoCmd.RunSQL "DELETE * FROM DonorsInfoDonations"
Set rs = CurrentDb.OpenRecordset("SELECT Donor_ID, CampaignAdd FROM Donors ")
Set rs1 = CurrentDb.OpenRecordset("DonorsInfoDonations")
Do While Not rs.EOF
rs1.AddNew
    rs1("DID_Donor_id") = rs("Donor_id")
    YearInCampaign = GetCurrentCampaignLNG - GetGimatria(rs("CampaignAdd"))
    If YearInCampaign Then
    rs1("YearInCampaign") = YearInCampaign
    rs1("campaignAbsentee") = DCount("Donation_ID", "Donations", "Donor_ID= " & rs("Donor_ID"))
    rs1("campaignDonation") = DCount("Donation_ID", "Donations", "Donor_ID= " & rs("Donor_ID") & " AND amount >0")
    rs1("averageDonation") = DAvg("amountInILS", "DonationsAllInfromation", "Donor_ID = " & rs("Donor_ID"))
    End If
rs1.Update
rs.MoveNext
Loop

הסבר:
הקוד מאכסן בטבלת DonorsInfoDonations נתוני תרומות ביחס לתורם, הוא רץ על recordSet שמכיל את כל ה Donor_ID ועל כל אחד מהם הוא מוסיף רשומה חדשה בטבלה הנ"ל, ואז מאכלס את השדות הבאים: DID_Donor_id - מזהה התורם, YearInCampaign - ותק שנים (על פי חישוב של GetCurrentCampaignLNG - שזה מציין במספרים את השנה הנוכחית, פחות GetGimatria של השנה בה התורם נכנס למאגר , באם יש לו ותק של שנה ומעלה מאכסן גם campaignAbsentee - שנים שנפקד, על פי Count של רשומות בטבלת Donations , וכן campaignDonation - שנים שתרם, על פי Count נתוני תרומה בטבלה הנ"ל, וכן averageDonation - ממוצע תרומה על פי Avg מהטבלה הנ"ל

OdedDvir

@מומחה-באקסס וואו. יש כאן הרבה עבודה.

GetGimatria תחשב רק פעם אחת לכל תורם ותשמור בשדה נפרד. זה אמור להיות נתון שלא משתנה.
למה לפתוח את כל הרשומות ואחר כך לבדוק אחת אחת בשורה 9? מראש תייבא רק את הרלוונטיות.
על פניו נראה שאפשר לממש הכל בשאילתות.
אתה קורא בכל איטרציה לשלוש פונקציות Domain: פעמיים ל-DCount ופעם ל-DAvg, ובנוסף, שתי הקריאות ל DCount כמעט זהות. אלו פונקציות איטיות להחריד ביחס לשאילתות קיבוץ. זה מיותר לחלוטין. תגיד תודה שיש לך רק 6000 רשומות...
תחשוב שאין DCount ולא DAvg. וזה צריך להיות הקו המנחה שלך.
צור שאילתות קיבוץ שיחשבו את הספירה והממוצע. ואחר כך תשתמש בשאילתת הוספה של הרשומות הרלוונטיות.

מומחה באקסס

@odeddvir
דבר ראשון, תודה!

אתה צודק, משום מה היה לי איזה עיקרון כזה לשמור בטבלה רק את הDATA הגולמי ביותר, אבל במקרה הזה ההפסד הוא גדול
אני כן רוצה שיהיה רשומה עבור תורם שאין לו ותק, ושיהיה לו null בשדות הרלוונטים
אכן, אבל ישנה בעיה קטנה, שים לב של- Count הראשון יש תנאי אחד, ולשני יש 2 תנאים, כך שלא אוכל לעשות את שניהם באותה שאילתה,
אם כן אני חייב לעשות תחילה שאילתת INSERT ל Count הראשון וכן ל avg ואח"כ UPDATE ל Count השני, הבעיה שאי אפשר לעשות Update עם שאילתת קיבוץ
האמת שישנו פתרון לעשות INSETR של השאילתת קיבוץ לטבלה זמנית וממנה לעשות UPDATE אבל משום מה מרגיש לי מסורבל ולא נכון, לא?

OdedDvir

@מומחה-באקסס אמר בזמן ריצה של קוד:

אכן, אבל ישנה בעיה קטנה, שים לב של- Count הראשון יש תנאי אחד, ולשני יש 2 תנאים, כך שלא אוכל לעשות את שניהם באותה שאילתה.

אפשר על ידי Sum עם IIF:

Count(Donor_ID) AS campaignAbsentee, Sum(IIF(Amount>0,1,0)) AS campaignDonation

אם כן אני חייב לעשות תחילה שאילתת INSERT ל Count הראשון וכן ל avg ואח"כ UPDATE ל Count השני

לא צריך. תעשה אפילו 10 שאילתות קיבוץ, ואחר כך תחבר את כולם לשאילתה אחת.

הבעיה שאי אפשר לעשות Update עם שאילתת קיבוץ. האמת שישנו פתרון לעשות INSETR של השאילתת קיבוץ לטבלה זמנית וממנה לעשות UPDATE אבל משום מה מרגיש לי מסורבל ולא נכון, לא?

זה מה שאני עושה למעשה. וזה מהיר פי 100.

מומחה באקסס

@odeddvir אמר בזמן ריצה של קוד:

@מומחה-באקסס אמר בזמן ריצה של קוד:

אפשר על ידי Sum עם IIF:

Count(Donor_ID) AS campaignAbsentee, Sum(IIF(Amount>0,1,0)) AS campaignDonation

ברוך שכיונתי, חשבתי על זה גם כן.
עשיתי כדברך ואכן רץ תוך 4 שניות, במקום 3 דקות... הזוי!
לא יודע למה אני מקובע על ה Dcount ....

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

OdedDvir

@מומחה-באקסס אם הבנתי נכון - אז כן.

מומחה באקסס

@odeddvir
משתף את התוצאה שעשית לבסוף בזכותך, אולי יש לך הארות?

DoCmd.RunSQL "INSERT INTO DonorsInfoDonations ( DID_Donor_id, YearInCampaign, campaignAbsentee, averageDonation, campaignDonation, DonationOnYear ) " & _
"SELECT Q1.Donor_ID, Q1.YearInCampaign, Q2.campaignAbsentee, Q2.averageDonation, Q2.campaignDonation, Q3.DonationOnYear " & _
"FROM ((SELECT Donor_ID, lngGetCurrentCampaign()-[lngCampaignAdd] AS YearInCampaign FROM Donors)  AS Q1 " & _
"LEFT JOIN (SELECT DonationsAllInfromation.Donor_ID, Count(DonationsAllInfromation.donation_ID) AS campaignAbsentee, Avg(DonationsAllInfromation.amountInILS) AS averageDonation, Sum(IIf([amount]>0,1,0)) AS campaignDonation FROM DonationsAllInfromation GROUP BY DonationsAllInfromation.Donor_ID)  AS Q2 ON Q1.Donor_id = Q2.Donor_ID) " & _
"LEFT JOIN (SELECT DonationsAllInfromation.Donor_ID, DonationsAllInfromation.amountInILS AS DonationOnYear FROM DonationsAllInfromation WHERE DonationsAllInfromation.YearCampaign=GetYearToGetDonation())  AS Q3 ON Q1.Donor_ID = Q3.Donor_id"

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

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

סליחה על ההתקפה...

dovid

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

OdedDvir

@מומחה-באקסס תודה על המשוב.
אודה לך אם התגובות בנושא המדריך שלי יתרכזו באשכול המיועד לכך. אשמח לענות לך שם.

מומחה באקסס

@odeddvir אמר בזמן ריצה של קוד:

@מומחה-באקסס תודה על המשוב.
אודה לך אם התגובות בנושא המדריך שלי יתרכזו באשכול המיועד לכך. אשמח לענות לך שם.

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

אם אפשר שאלה נוספת, בבקשה,
כפי שהמלצת, אני כעת מנסה להפוך את כל ה Dcount שלי לערכים של שאילתת קיבוץ
השאלה מה אני עושה בטופס שאני צריך לאפשר בו עריכה, כך שאי אפשר לעשות בו מקור רשומה של משפט SQL המכיל גם GROUP BY
ומצד שני כן חשוב לי שהערך הזה יופיע במקור של הטופס כדי שאני יוכל להפעיל את הטופס עם סינון על הערך הזה
מה שנותן לי רק אפשרות של Dcount (שמצד אחד עדיין מאפשר עריכה, ומצד שני מאפשר פילטור על השדה שלו)
תודה מראש!!

clickone

אחרי ההסבר המקיף של @OdedDvir , נותר לי רק לענות על השאלה הזו, שאפילו שלכאורה היא לא רלוונטית כאן, אבל חשוב לדעת מה עושים במקרה כזה (דוגמא קלאסית היא לופ ששולח מיילים ל1000 אנשים לדוגמא, זה א"א עם שאילתא, [עקרונית אפשר ] - ובכזה מקרה ממש רצוי להציג פס התקדמות או אפילו לכתוב באיזו תיבת טקסט מה קורה כעת)

@מומחה-באקסס אמר בזמן ריצה של קוד:

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

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

DoEvents

בהצלחה

OdedDvir

@מומחה-באקסס לא התכוונתי מעולם לומר שאסור להשתמש ב-DCount ושות', אלא שמיעוטן יפה.
במקרה שבו פתחת את הפוסט, היה ניכר שיש בעיה רצינית ביעילות, וראית את השיפור העצום ששימוש בכמה שאילתות גרם, והוריד את זמן הריצה פי 100 כמעט בדיוק: שניות 4/210 = 0.019.

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

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

תחומים - פורום חרדי מקצועי

זמן ריצה של קוד