تصميم قواعد بيانات التقارير فائقة السرعة « مغامرات برمجية

إغلاق طباعة

تصميم قواعد بيانات التقارير فائقة السرعة

نّشر في: 2012/10/08
تعليقات: 3 تعليق


من أهم أجزاء أي تطبيق أعمال تجارية هو التقارير. استخراج خلاصة كم هائل من البيانات إلى صفحة واحدة هي عملية ليست سهلة أحياناً من الناحية البرمجية. بعض التقارير المعقدة تتطلب أن يكون المرء ضليعاً في لغة SQL. هذه نقطة مفروغ منها، ولن أضيع وقتكم في نقاشها. ولكن ما أجده قاصراً في كثير من التطبيقات التي مرّت علي ليست دقة المعلومات الموجودة في التقارير، بل سرعة تنفيذ هذه التقارير.

ليس من المستغرب أن تجد بعض التقارير المعقدة تأخذ عدة دقائق فقط داخل استعلام الـSQL. وأحياناً كثيرة ينتج عن هذا امتعاض وعدم رضا المستخدم النهائي الذي في الغالب لن يقدّر مدى تعقيد طلبه، ولا يهمه سوى أن يرى النتيجة النهائية في أسرع وقت ممكن.

هناك عدة حلول سريعة، يعرفها معظمنا. وضع الاستعلام داخل Stored Procedure مثلاً، للاستفادة من سرعتها. إضافة index مناسبة للجداول التي يتم الاستعلام منها. وطبعاَ تحسين أداء الاستعلام بالتدقيق في الاستخدام الصحيح لعمليات Join وGroup وغيرها من العمليات المكلفة (إلقاء نظرة على الـexecution plan الخاصة بالاستعلام مقيد جداً).

ولكن أحياناً كثيرة كل ما سبق لا ينجح في تسريع الاستعلام بشكل مقبول. بالذات عندما يكون لدينا كم هائل من البيانات التي يجب أن نقوم بعمليات Join وGroup عليها.

عندها علينا أن نفكر في استخدام حل آخر. تطوير قاعدة بيانات رديفة خاصة فقط بالتقارير.

قد يبدو هذا الحل جذرياً بدرجة مفرطة، وهو كذلك. لذا يجب علينا مراعاة الحذر عند أخذ هذا الطريق. لذا اسألوا نفسكم السؤالين التاليين أولاً:

  • هل لدي عدد هائل من البيانات يجعل عمليات الاستعلام المعقدة تأخذ وقتاً طويلاً؟
  • هل بيانات التقارير “أرشيفية”؟ بمعنى أن التقارير لا يهمها كثيراً أن تكون بياناتها مستقاة من معلومات محدثة كل ثانية بثانية (فرق يوم أو بضعة ساعات لا يهم كثيراً).

إذا كانت إجابتكم بنعم لكلا السؤالين فإن تقاريركم مناسبة لمثل هذا الحل.

لننظر الأن بتفصيل أكثر لما ينطوي عليه تطوير قاعدة بيانات منفصلة كهذه. لنأخذ تطبيقاً حقيقاً عملت عليه (مع تغيير الأسماء لحفظ خصوصية العميل) كمثال. لنقل أن لدينا أربع جداول: Cars يحوي بيانات السيارات التي تم بيعها خلال العشر سنوات الأخيرة، Dealers يحوي بيانات وكلاء الصيانة، Repairs يحوي بيانات الصيانة التي تمت على هذه السيارات طيلة فترة عملها، وRepairParts يحوي علي بيانات قطع الغيار التي تم استبدالها في عمليات الصيانة تلك. كم المعلومات فيها ضخم لأنها تضم فترة زمنية طويلة ويصل عدد سجلاتها إلى الملايين.

لنقل أننا نريد تقريرأ بتكاليف القطع لعمليات الصيانة لكل الوكلاء خلال الفترة من 2005 إلى 2007. سنكتب استعلاماً كهذا:

1
2
3
4
5
6
7
8
9
10
select dlr.DealerName, SUM(prt.PartCost)
from Dealers dlr
left outer join Cars car
    on dlr.DealerID = car.DealerID
left outer join Repairs rpr
    on car.CarContractID = rpr.CarContractID
left outer join RepairParts prt
    on rpr.RepairID = prt.RepairID
where car.SaleYear between 2005 and 2007
group by dlr.DealerName

ثلاث عمليات Join وعملية Group! وهو سيناريو ليس بغريب عن مطوري التقارير. في قاعدة بياناتي أخذ هذا الاستعلام فوق 10 دقائق. وهذا غير مقبول بأي شكل من الأشكال. لنرى الآن كيف يمكننا تصميم جدول رديف مخصص فقط لهذا النوع من التقارير.

أي شخص درس قواعد البيانات بشكل أكاديمي يذكر جيداً أهمية الـNormalization في التصميم الصحيح لقواعد البيانات. ولكننا الآن سنرمي بهذا القانون السرمدي في عرض الحائط!

قبل أن يشتكي أحد، دعنا نراجع سبب أهمية الـNormalization: وحدة البيانات وعدم تكرارها. إذا قام أحدكم بتصميم جدول سيارات لتطبيق حي، فيه عمود لإسم الماركة بدلاً من وضع أسماء الماركات في جدول منفصل وربطه، فلن يلومني أحد إذا أمسكت برقبة هذا المهرطق وقذفت به قذفاً في أقرب فوهة بركان حتى يتعظ أمثاله.

ولكننا لسنا في الوضع الطبيعي. الـNormalization وضعت لضمان سلامة البيانات التي يتم إدخالها في قاعدة البيانات. ولكننا انتهينا من عملية إدخال البيانات منذ زمن. لدينا كم كبير من المعلومات التي نحن متأكدون تماماً من سلامتها. عندها تصبح الـNormalization عبئاً علينا. لإننا سنضطر للقيام بعمليات Join المكلفة لتجميع هذه البيانات المفرقة في مكان واحد.

عودةً لمثالنا، لنقم بصنع جدول رديف يسهل حياتنا. لنسمي هذا الجدول YearlyPartsCost ويحوي على أربع عواميد فقط: السنة المالية، رقم الوكيل المميز، اسم هذا الوكيل ومجموع قطع غيار الصيانة لهذا الوكيل في تلك السنة المالية. ونعبئ هذا الجدول بواسطة استعلام كهذا:

1
2
3
4
5
6
7
8
9
10
11
12
truncate table YearlyPartsCost

insert into YearlyPartsCost
select car.SaleYear, dlr.DealerID, dlr.DealerName, SUM(prt.PartCost) TotalPartCost
from Dealers dlr
left outer join Cars car
    on dlr.DealerID = car.DealerID
left outer join Repairs rpr
    on car.CarContractID = rpr.CarContractID
left outer join RepairParts prt
    on rpr.RepairID = prt.RepairID
group by car.SaleYear, dlr.DealerID, dlr.DealerName

نمسح مكونات الجدول تماماً (truncate أسرع من delete في هذه الحالة) ثم نعيد تعبئته بواسطة جملة استعلام مناسبة. بالمناسبة، اللغة المستخدمة هتا هي T-SQL الخاصة بـMicrosoft SQL Server، ولكن المفاهيم الموجود في هذه المقالة يمكن تطبيقها على أي قاعدة بيانات. هذه العملية أخذت أكثر من نصف ساعة عندي. ولكن هذه ليست مشكلة. لأننا لن نقوم بإعادة بناء بيانات هذا الجدول إلا في أوقات متفرقة. كل يوم بعد نهاية الدوام مثلاً كعملية موقتة آلياً. والآن الاستعلام لنفس التقرير السابق يصبح:

1
2
3
4
select DealerName, sum(TotalPartCost)
from YearlyPartsCost
where SaleYear between 2005 and 2007
group by DealerName

عملية Group فقط ولا وجود لأي Join. والنتيجة؟ هذا الاستعلام أخذ أقل من ثانية للعمل. وهذا تحسن ملحوظ جداً بجميع المقاييس!

بناء قاعدة بيانات رديفة للتقارير يتطلب جهداً ليس ببسيط. ويتطلب فهم عميق للمعلومات الني يتم الاستقاء منها وفهم مشابه لطبيعة التقارير الناتجة. ولكن زيادة السرعة الناتجة عن هذه العملية قد يمثل مسألة حياة أو موت بالنسبة للتطبيق من وجهة نظر العميل.

Post to Twitter

3 تعليق - أضف تعليق
  1. Mosh قال:

    فكرة جميلة صراحة

  2. احمد جمال قال:

    طريقة جيدة ولكن لي سؤال
    ما مدى امكانية تطبيق هذه الطريقة باستخدام VIEW
    هل استخدام VIEW يحوي مشاكل في سرعة استخراج المعلومات منه

    وشكرا

  3. أحمد المهداوي قال:

    فكرة مفيدة جدا
    جزاكم الله خيرا


مرحباً , تاريخ اليوم هو الخميس, 2017/03/23