أعتذ عن التأخر الكبير بمشاركتم الحل المتبّع.
باختصار:
النقطة الطرفية (End Point) هذه تظهر للطالب دروسه والنقاط الحاصل عليها في كل درس. النقاط يتم حفظها في قاعدة البيانات لكل طالب وتمرين على حدى. كل درس فيه عدة تمارين. فهذه الخدمة مسؤولة أن تجد جميع الدروس ثم تقوم بحساب النقاط للطالب لكل درس عن طريق سؤال هذه الجداول الكثيرة.
المشكلة ببساطة هي مشكلة N+1. كان يتم طلب نقاط كل درس على حدى داخل حلقة تكرار. وهذا ما جعل الخدمة بطيئة جدّاً وذات أداء غير مقبول البتّة.
حيث أن كل مستوى من مستويات الدورة حسب تقسيمات تطبيقان فيها 100 درس تقريباً. فلك أن تتخيل مدى انعدام كفائة الحل السابق قبل التحسين.
للإجابة على سؤال الأخ @علي ملص : لا، لم أجرب Eager Loading، ﻷنه سيجبر كل الخدمات الأخرى على تحميل بيانات لا حاجة لها بها. وهنالك أيضا حساب النقاط الكلية للدرس وهذه أيضا معلومة يتم حسابها وليست ثابته.
هنالك طريقة ثالثة كان ممكن أن تستخدم وهي الـJOIN FETCH وهي طريقة معروفة ومتّبعه في JPA Repositories. لم أستخدمها ﻷنها ذهب من ذهني، لكنّي جربتها في خدمة أخرى وأعطتني نتيجة مرضية جدّأً بتحسين أداء يقارب العشرة أضعاف.
لماذا لم أستخدم materialized view؟ ﻷن البيانات ليست ثابتة للدروس التي يتم إنجازها حاليا من طرف الطالب.
وبالنسبة للكاش: فهذه إضافة يجب التفكير فيها والتخطيط لها قليلاً قبل التطبيق العملي كي نتفادى مشكلة تخديم بيانات قديمة أو ملئ الذاكرة ببيانات غير لازمة.
وكما وعدت أيضا هذا هو الـVIEW
**-- Static view: total points per (lesson_id, tenant_id).
-- Exercise types and tenant_preference ids are hardcoded (types 1-11 + submission points).
CREATE OR REPLACE VIEW lesson_total_points (lesson_id, tenant_id, total_sum) AS
WITH new_vocabs_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM theory_vocabs tv
LEFT JOIN exercises e ON tv.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 1 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'new_vocabs_item_points'
),
matching_exercise_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM matching_exercise_vocabs mev
LEFT JOIN exercises e ON mev.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 2 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'matching_exercise_item_points'
),
multichoice_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM multichoice_items mi
LEFT JOIN exercises e ON mi.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 3 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'multichoice_exercise_item_points'
),
true_false_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM true_false_items tfi
LEFT JOIN exercises e ON tfi.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 4 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'true_false_exercise_item_points'
),
pronunciation_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM pronunciation_items pi
LEFT JOIN exercises e ON pi.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 5 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'pronunciation_exercise_item_points'
),
rashidi_pronunciation_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM rashidi_pronunciation_subitems rps
LEFT JOIN exercises e ON rps.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 7 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'rashidi_pronunciation_exercise_subitem_points'
),
tajweed_exercise_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM tajweed_exercise_ayat tea
LEFT JOIN exercises e ON tea.exercise_id = e.id
LEFT JOIN ayah_parts ap ON ap.surah_number = tea.surah_number AND ap.ayah_number >= tea.from_ayah_number AND ap.ayah_number <= tea.to_ayah_number
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 6 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'tajweed_exercise_item_points'
),
arabic_listening_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM arabic_listening_exercise_items alei
LEFT JOIN exercises e ON alei.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 8 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'arabic_listening_exercise_item_points'
),
arabic_pronunciation_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM arabic_pronunciation_exercise_items apei
LEFT JOIN exercises e ON apei.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 9 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'arabic_pronunciation_exercise_item_points'
),
arabic_multichoice_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM arabic_multichoice_exercise_items amei
LEFT JOIN exercises e ON amei.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 10 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'arabic_multichoice_exercise_item_points'
),
arabic_matching_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * tp.value) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM arabic_matching_exercise_first_column_items amef
LEFT JOIN exercises e ON amef.exercise_id = e.id
LEFT JOIN lessons ON lessons.id = e.lesson_id
WHERE e.type = 11 AND lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery
JOIN tenant_preferences tp ON tp.tenant_id = subquery.tenant_id AND tp.id = 'arabic_matching_exercise_item_points'
),
tajweed_submission_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * srg.points) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM tajweed_submission ts
LEFT JOIN ayah_parts ap ON ap.surah_number = ts.surah_number AND ap.ayah_number >= ts.from_ayah_number AND ap.ayah_number <= ts.to_ayah_number
LEFT JOIN lessons ON lessons.id = ts.lesson_id
WHERE lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery,
student_recording_grades srg
WHERE srg.id = 5
),
rashidi_submission_items_points AS (
SELECT subquery.lesson_id AS lesson_id, subquery.tenant_id AS tenant_id, (subquery.cnt * srg.points) AS result
FROM (
SELECT lessons.id AS lesson_id, lessons.tenant_id AS tenant_id, COUNT(*) AS cnt
FROM rashidi_submission_items rsi
LEFT JOIN rashidi_submissions rs ON rsi.submission_id = rs.id
LEFT JOIN lessons ON lessons.id = rs.lesson_id
WHERE lessons.id IS NOT NULL
GROUP BY lessons.id, lessons.tenant_id
) AS subquery,
student_recording_grades srg
WHERE srg.id = 5
),
combined AS (
SELECT lesson_id, tenant_id, result FROM new_vocabs_points
UNION ALL SELECT lesson_id, tenant_id, result FROM matching_exercise_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM multichoice_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM true_false_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM pronunciation_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM rashidi_pronunciation_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM tajweed_exercise_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM arabic_listening_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM arabic_pronunciation_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM arabic_multichoice_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM arabic_matching_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM tajweed_submission_items_points
UNION ALL SELECT lesson_id, tenant_id, result FROM rashidi_submission_items_points
)
SELECT lesson_id, tenant_id, SUM(result)::INT AS total_sum
FROM combined
GROUP BY lesson_id, tenant_id;
`
