لا تستعمل DlookUp او DSum واخواتها في استعلام ، واليك البديل

[jjafferr]

السلام عليكم 🙂

 

عند عمل البرنامج ، لا تجرب برنامجك على عدد اقل من 10 الآف سجل ، فسرعة الاستعلام الصحيحة هي لما تجرب البرنامج على اكثر من 10 الآف سجل 🙂

لا تستعمل DlookUp او DSum واخواتها في استعلام ، لأنها تجعل الاستعلام بطيء ، واذا كانت البيانات كثيرة ، فالاستعلام سيكون بطيء بشكل غير مقبول !!

وللعلم ، فالاوامر DlookUp او DSum هي واقعا استعلام ، ولكن تم عمله بهيئة دالة ، ترسل لها المتغيرات ، وترج لك النتيجة 🙂

 

والبديل هو فك كل امر DlookUp او DSum في استعلام مستقل (قد نستطيع جمعهم ، ولكن هذا يعتمد على المعايير) ، بحيث نستخدم معايير الاستعلام بدلا عن معايير الاوامر DlookUp او DSum ، ونستخدم ربط الجداول كنوع من المعيار كذلك.

 

المثال التالي سيوضح قصدي:

احد المؤسسات التي اتعامل معها ، ارسلت لي هذا الملف وبه حوالي مليون سجل ، وطُلب مني النظر في بطئ استعلام الحاقي.

الجدول التالي يحتوي على 100 الف سجل (حذفت الباقي 🙂) ، وسجلات سنة 2019 = 7 سجلات فقط ، وبقية السجلات لسنة 2018 :

.

الاستعلام الالحاقي الاصل هو :

.

ومن اجل هذا المثال ، سنحول الاستعلام الى استعلام عادي ، وسنفك الحقول ، وسنستخدم مجاميع سنة 2019 في استعلام مستقل وكذلك لسنة 2018 :

.

بالنسبة لسنة 2019 ، بدل عن استخدام الامر DSum (سبب بطئ الاستعلام) ،

سنفك الامر DSum الى استعلام مستقل ، هكذا :

.

ونفس الشيء لسنة 2018 ،

والآن نأتي الى المعيار الاخير (3) ، وهو حقل id لجدول var ، يجب ان يساوي حقل المعرف لجدول data ، وانا هنا وضعت استعلام 2019 (والذي يحتوي على 7 سجلات كما رأينا اعلا) ، ووضعت الاستعلام 2018 كذلك ، فيصبح بهذه الطريقة :

.

هذا الاستعلام يأخذ حوالي ثانيتين في تنفيذه ، ورجاء تجربته 3 مرات للحصول على متوسط وقت التنفيذ 🙂

واترك لكم تجربة الاستعلام qry_DSum_Egaza_9 ، رجاء تجربته 3 مرات كذلك ، وكل من يشارك في هذا الموضوع ، رجاء اعطائنا وقت التنفيذ 🙂 

رجاء لا تستخدم الكود في حساب الوقت ، وانما افتح الاستعلام يدويا ، 3 مرات 🙂

 

اما الاستعلام qry_DSum_Egaza_8 ، فلا انصحك بتجربته ، إلا اذا ما كان عندك شغل آخر تعمله 😁

 

جعفر

 

استعمل سجلات هذا الملف لتجاربك:

 

 

---------------------------------------------------------------------------------------------------

 

هذا مثال آخر يؤيد الفرق في سرعة الاستعلام بين الطريقتين :

 

1475.Dont_use_DlookUp_DSum_in_Query.accdb.zip

[SEMO.Pa3x]

اهلا بالمعلم.. هل يمكن استخدام SubQuery ؟

[jjafferr]

الهدف من هذا الموضوع هو ان نتلافى استخدام DlookUp او DSum واخواتها في استعلام 🙂

 

2 دقائق مضت, SEMO.Pa3x said:

هل يمكن استخدام SubQuery ؟

الاستعلام الفرعي ، نعم ، لا مانع من ذلك 🙂

ولكن رجاء النظر في الرابط التالي ، بحيث ستعرف ان الاستعلامات الفرعية ليست جميعا بنفس السرعة ، لذا يجب عليك تجربة عدة طرق :

 

جعفر

[SEMO.Pa3x]

1 دقيقه مضت, jjafferr said:

الهدف من هذا الموضوع هو ان نتلافى استخدام DlookUp او DSum واخواتها في استعلام 🙂

 

الاستعلام الفرعي ، نعم ، لا مانع من ذلك 🙂

ولكن رجاء النظر في الرابط التالي ، بحيث ستعرف ان الاستعلامات الفرعية ليست جميعا بنفس السرعة ، لذا يجب عليك تجربة عدة طرق :

 

جعفر

 

قد يقوم البعض بإستعمالها ويرى انها سريعة جدا لانه يملك عدد سجلات قليلة، وهذا ما حصل معي

أذكر انني في احد برامجي في سنين سابقا استخدمت Dlookup في الاستعلام وكان الاستعلام سريع جدا، لانني املك 10 سجلات فقط.

وعندما وصل عدد السجلات الى الآلاف اصبح برنامجي كالسلحفاة 🐢 ههه

 

[jjafferr]

35 دقائق مضت, jjafferr said:

رجاء تجربته 3 مرات كذلك ، وكل من يشارك في هذا الموضوع ، رجاء اعطائنا وقت التنفيذ 🙂

فما هو وقت تنفيذ الاستعلام على كمبيوترك ؟

[Eng.Qassim]

لا اعلم لماذا النتائج غير دقيقة معي .. رغم ان الاستعلام qry_DSum_Egaza_8 اخذ وقت اطولر

[jjafferr]

2 دقائق مضت, Eng.Qassim said:

لا اعلم لماذا النتائج غير دقيقة معي .. رغم ان الاستعلام qry_DSum_Egaza_8 اخذ وقت اطول

 

بالفعل ، النتائج غير دقيقة حسب تجاربي 🙂

ورجاء ، كل استعلام جربه 3 مرات 🙂

 

جعفر

 

[Eng.Qassim]

الموضوع في غاية الاهمية ...واستاذ جعفر اعطى مالديه مشكورا

ارجوا ممن لديه فكرة ان يدلو بدلوه

[abouelhassan]

شكر وتقدير واحترام من اخيك استاذى الفاضل حبيبى

[الحلبي]

2 ساعات مضت, jjafferr said:

ورجاء ، كل استعلام جربه 3 مرات

طيب الاستعلام الاول اسمه dsum_eqaza هذا لا يفتح معى والكمبيوتر هنج منى 

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

انا الاحظ ان الاستعلام الاول ( المعقد) فيه معيار واحد فقط هو الفرق بين التواريخ  ومعيار كلمة  shard  هو كلمة نعم

انا عندى استعلامات فيها dsum باكثر من معيار يعنى افكها بكام استعلام

وعلى فكرة فى لغات البرامج الاخرى مثل فيجول بيسك استوديو انا تابعت برنامج الصيدلية فيه حوالى 24000 نوع من الدواء وعشان تفتح صفحة الادوية تاخد وقت طويل 

لازم يكون فيه حل اخر غير موضوع الفك 

لو انا حولت قاعدة البيانات الى سيكوال سرفر هل ايضا الاستعلام يكون بطئ

تم تعديل فبراير 28, 2022 بواسطه الحلبي

[jjafferr]

10 ساعات مضت, Eng.Qassim said:

لا اعلم لماذا النتائج غير دقيقة معي

رجاء لا تستخدم الكود في حساب الوقت ، وانما افتح الاستعلام يدويا ، 3 مرات لوسمحت 🙂

 

جعفر

[jjafferr]

9 ساعات مضت, الحلبي said:

طيب الاستعلام الاول اسمه dsum_eqaza هذا لا يفتح معى والكمبيوتر هنج منى

لهذا السبب لم اطلب منك تجربته ، فهذا الاستعلام الاصل الذي طُلب مني النظر في سرعته 🙂

وفي الواقع يُفتح الاستعلام ، ولكن يحتاج الى وقت طويل 😁

 

9 ساعات مضت, الحلبي said:

معنى ذلك ان كل استعلام يحمل اخوات dlookup  نفكة باربع او خمس استعلامات

هناك طريقتين ، وانا اعطيتك الطريقة الاسهل ، لأنك تقدر تشوفها وتعدل فيها ،

الطريقة الثانية مثل ما قال اخوي حسنين @SEMO.Pa3x باستعمال الاستعلام الفرعي ، وهو استعلام بهيئة SQL ، فليس من السهل عمله وتعديله !!

 

اسألك سؤال:

هل الافضل ان تستعمل الاستعلام dsum_eqaza كما هو (لاحظ ان البرنامج المرفق هو نسخة مصغرة من البرنامج الاصل ، والذي عدد سجلاته حوالي مليون سجل) ، وتنتظر 28 دقيقة ليفتح ، او

تعمل 10 استعلامات وتجعل الاستعلام يشتغل بوقت اسرع بكثير ؟

 وللعلم ، هناك مبرمجين وظيفتهم تسريع الاستعلامات 🙂

 

9 ساعات مضت, الحلبي said:

انا عندى استعلامات فيها dsum باكثر من معيار يعنى افكها بكام استعلام

ما ادري ، لازم اشوفه !!

ولكن وكما رأيت في المثال اعلاه ، فككنا كل استعلام DSum باستعلام واحد خاص به ، ثم ربطناهم باستعلام يجمعهم جميعا.

 

9 ساعات مضت, الحلبي said:

لازم يكون فيه حل اخر غير موضوع الفك

 انا شايف ان هناك مبرمجين لا يريدون الكود الطويل ، ولا يريدون استعلامات كثيرة ،

بينما الطريقة الصحيحة في البرمجة هي ان تعمل الافضل لبرنامجك ، والبرنامج يقوم بالباقي 🙂
 

9 ساعات مضت, الحلبي said:

لو انا حولت قاعدة البيانات الى سيكوال سرفر هل ايضا الاستعلام يكون بطئ

معلوماتي كانت قديمة في هذا المجال ، فأخذت الوقت لأقرأ مرة اخرى ، واليك الملخص:

1. اذا واجهة الاكسس والجداول على نفس الكمبيوتر ،

2. واذا عملت على SQL Server من نفس السيرفر ،

سيكون الاكسس اسرع ، وبوقت ملحوظ ، طبعا نتكلم عن بضع عشرات/مئات آلاف السجلات 🙂

اما اذا:

3. الجداول على الشبكة ، وواجهة الاكسس على كمبيوتر في الشبكة ،

4. برنامج SQL Server على السيرفر ، والمستخدم على كمبيوتر في الشبكة ،

فسيكون SQL Server اسرع 🙂

 

وبغض النظر عن مكان تواجد الواجهة والجداول ، فاذا كلامنا عن ملايين السجلات ، فهذا تخصص SQL Server ، ومع ان الاكسس يستطيع التعامل معه اذا كان الاكسس 64بت وكمية الذاكرة المؤقتة RAM على الاقل 4 جيجا (طبعا يُفضل ان يكون اكثر).

 

هناك اعتقاد خاطئ ، بأنه اذا استعملت SQL Server بدل عن جداول الاكسس ، فسيكون البرنامج اسرع ، 

لأنه حتى تستطيع الحصول على افضل سرعة في SQL Server فيجب ان يكون في جداولك اعدادات معينة مثل المفتاح الاساس Primary key (وهو غير عن الفهرسة) ويحتوي على حقل تاريخ ووقت عمل السجل (بالثواني) ، وللاستعلام تستعمل Stored Procedure .

 

جعفر

[الحلبي]

استاذى ومعلمى / @jjafferr

الله الله عليك لقد قمت بالاجابة على جميع تساؤلاتى ـ الان قد وضحت الرؤية 

لعلمك هذا المقال الذى قمت حضرته بكتابته (انا ادعى انه مقال) هام جدا جداجدا فى محيط البرمجة وياريت اى مبرمج ان يبدأ به 

وهذا الموضوع يعتبر اهم من المواضيع التى يسأل عنها اخوانى فى المنتدى (انا الاحظ ان جميع اسئلة المنتدى كلها لا تساوى شيئا جنب هذا الموضوع)

الان قد وضح كل شئ اشكرك استاذى على هذا التوضيح الهام جدا جدا جدا جدا جدا 

يجعله الله فى ميزان حسناتك ويبارك فى صحتك وعافيتك ويرحم والديك 

استاذى / الاستاذ جعفر سوف ارفق لك مثال صغير لكى نتعاون معا فى فكه وهو يحتوى على dsum كثيرة 

جزاك الله كل خير

كل الاحترام والتقدير لكم

 

[Eng.Qassim]

منذ ساعه, الحلبي said:

هام جدا جداجدا فى محيط البرمجة

فعلا اخي من اهم المواضيع ..وعلى المبرمج اختبار اكواده على نسخة استاذ جعفر ليرى سرعة البرنامج

 

[الحلبي]

استاذى ومعلمى / @jjafferr

لا ادرى اين اضع المثال  هل اضعه هنا ام افتح موضوع جديد

استاذنا وبشمهندس / @Eng.Qassim

انت منور المنتدى كله والله العظيم

[jjafferr]

نعم هنا مناسب ، فسيكون مثال آخر على للموضوع 🙂

ولكن رجاء تتأكد ان لا يحتوي المرفق على بيانات خاصة 🙂

 

وطبعا المجال مفتوح للجميع للمشاركة بتجاربهم ، والبرنامج الاسرع يكون الافضل 🙂

 

جعفر

[الحلبي]

هذا مثال مصغر جدا لاستعلام به دالة dsum

طبعا انا الغيت حاجات كثيرة وكن الذى يهم هنا هو dsum

يارب ينفك الاستعلام على استعلام واحد واقدر اطبقة عندى

جزاكم الله خير

الاصناف.accdb

[ازهر عبد العزيز]

19 ساعات مضت, jjafferr said:

وكل من يشارك في هذا الموضوع ، رجاء اعطائنا وقت التنفيذ 🙂 

رجاء لا تستخدم الكود في حساب الوقت ، وانما افتح الاستعلام يدويا ، 3 مرات 🙂

دائما ثلاث ثواني لكن البرنامج توقف بعد تشغيل استعلام الالحاق

[SEMO.Pa3x]

19 ساعات مضت, jjafferr said:

فما هو وقت تنفيذ الاستعلام على كمبيوترك ؟

من وجهة نظري لا يمكن ان تستفيد من السرعات في كومبيوترات الاشخاص لانها تتغير تبعا لمميزات الكومبيوتر كالرام والهارد والمعالج وكرت الشاشة بالنتيجة لا يمكن الحكم على سرعة الاستعلام فربما يظهر لدي في طرفة عين ولو كان يحتوي على الاف السجلات وقد يتم تنفيذه لديك خلال نص ساعة. 

[ازهر عبد العزيز]

8 دقائق مضت, SEMO.Pa3x said:

من وجهة نظري لا يمكن ان تستفيد من السرعات في كومبيوترات الاشخاص لانها تتغير تبعا لمميزات الكومبيوتر كالرام والهارد والمعالج وكرت الشاشة بالنتيجة لا يمكن الحكم على سرعة الاستعلام فربما يظهر لدي في طرفة عين ولو كان يحتوي على الاف السجلات وقد يتم تنفيذه لديك خلال نص ساعة. 

وجهة نظر سديدة لهذا الافضل ادراج الاستعلام بالطريقة التقليدية مع هذة الطريقة للمقارنة بين الطريقتين وان كنت متاكد من ان طريقة الاستاذ جغقر ستكون هي الاسرع لكن للتجرية لا اكثر

[الحلبي]

21 دقائق مضت, SEMO.Pa3x said:

من وجهة نظري لا يمكن ان تستفيد من السرعات في كومبيوترات الاشخاص لانها تتغير تبعا لمميزات الكومبيوتر

استاذنا الكبير الاستاذ / حسنين انا اختلف معاك بشده لان كلام الاستاذ / جعفر صحيح 100% بدليل انا وضعت مثال مصغر وكان ياخذ وقت طويل حتى يفتح

ولكن لما الغيت dsum  وعملت بنفس طريقة الاستاذ / جعفر لم ياخذ الوضع ثانية واحدة على نفس الكمبيوتر .

 

استاذنا ومعلمنا الكبير قوى قوى / @jjafferr

لقد تمكنت من تحويل الاستعلام فى المثال المقدم اعلاه الى استعلام واحد ويفتح معى بسرعة فائقة بعد ان قمت بحذف dsum منها

الله يجزاك خير استاذنا ومعلمنا الاستاذ / جعفر

 

[jjafferr]

51 دقائق مضت, SEMO.Pa3x said:

من وجهة نظري لا يمكن ان تستفيد من السرعات في كومبيوترات الاشخاص لانها تتغير تبعا لمميزات الكومبيوتر

نعم دكتور جسنين ، كلامك صحيح ، ولكن لم يكن هدفي المقارنة بين سرعة استعلامات كمبيوترات مختلفة ، ولكن بمقارنة سرعة الاستعلامات في نفس الكمبيوتر 🙂

 

 

25 دقائق مضت, الحلبي said:

لقد تمكنت من تحويل الاستعلام فى المثال المقدم اعلاه الى استعلام واحد ويفتح معى بسرعة فائقة بعد ان قمت بإستبدال وبحذف dsum منها باستعلام خاص به...(تعديلات جعفر)

اعتقد بعض الناس ☝🙄 بيكونوا مشغولين كم يوم في تحديث برنامجهم بالطريقة اعلاه 😁

دكتور حلبي ، الحمدلله انك اقتنعت بالدليل والبرهان 🙂

ولكن نطلب منك ان تشرح لنا طريقتك ، حتى يستفيد الجميع لو سمحت 🙂

 

جعفر

[SEMO.Pa3x]

السيد الاستاذ، هل اعتبر الهدف من الموضوع هو تحدي لاي شخص يعدل الاستعلام بطريقته الخاصة بحيث نتيجة الاستعلام تكون سريعة. 

اذا كان هذا الهدف يرجى توضيح ماهو المطلوب عمله، لكي احاول معه الليلة. 

[jjafferr]

العفو ، لا ليس تحدي 🙂

وانما الدكتور حلبي كان سيضع برنامجه ، وانا شجعت الجميع في العمل على برنامجه ،

بس الدكتور انهى موضوعه بنفسه ، فخلاص ، مافي عزومة 🙂

 

جعفر

[الحلبي]

24 دقائق مضت, jjafferr said:

س الدكتور انهى موضوعه بنفسه ، فخلاص ، مافي عزومة

اولا الاستعلام المقدم فى المثال صغير جدا وقد قمت بحذف كثير من الاعمدة منه وهو يحمل حوالى 17000 سجل ولذلك كانت ياخذ وقت طويل فى فتحه

والحل هو حسب طريقة حضرتك كالتالى

1. حذف جميع dsum من الاستعلام والتعويض عنها بان يكون الاستعلام تجميعى وفى علامة التجميع وضعت كلمة sum بدلا من كلمة Group By
2. بعد وضع كلمة sum يلزم ان نضع نقطتين فوق بعض امام اسم العمود ونكرر نفس اسم العمود 

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

والتعديل تم فى الاستعلام المسمى rased1

ويبقى سؤال اخر استاذ / جعفر هل العمليات الحسابية مثل (+ ) و(-) و(*) و(/) يعطل الاستعلام ام انه عادى  انظر حضرتك الى عمود rased فى الاستعلام هل يؤثر فى شئ

واليك المرفق

بعد اضافة استعلام rasd1 به الحل

جزاكم الله خيرا

الاصناف.accdb