بهینه سازی این کوئری سنگین

[sara_aryanfar]

با سلام کوئری زیر زمانی معادل 10 ثانیه برای اجرا نیاز داره حالا تعداد کاربران در حدود 400 هست و جدول های دیگه هم دیتای کمی دارن اگر تعداد بیشتر بشه این خیلی خیلی زمانش بالاتر میره ممنون میشم دوستان راهنمایی کنن تا بهینش کنم

select usertb.*, usertb.uid,

  count(distinct co.coid) as conversation_count,

  count(distinct pc.pcid) as paracliniccount_count,

    count(distinct tr.trid) as reservition_count,

    count(distinct ms.msid) as message_count,

    count(distinct pre.preid) as prescription_count,

    count(distinct utl.utlid) as user_test_list_count



from

  x_user

  left join co  ON co.uid = usertb.uid

  left join pc  ON pc.uid= usertb.uid

  left join tr  ON tr.uid= usertb.uid

  left join ms   ON ms.uid= usertb.uid

  left join pre   ON pre.uid= usertb.uid

  left join utl   ON utl.uid= usertb.uid



 WHERE usertb.uid=14  GROUP BY usertb.uid

[farhad_shiri_ex]

با سلام کوئری زیر زمانی معادل 10 ثانیه برای اجرا نیاز داره حالا تعداد کاربران در حدود 400 هست و جدول های دیگه هم دیتای کمی دارن اگر تعداد بیشتر بشه این خیلی خیلی زمانش بالاتر میره ممنون میشم دوستان راهنمایی کنن تا بهینش کنم

select usertb.*, usertb.uid,

  count(distinct co.coid) as conversation_count,

  count(distinct pc.pcid) as paracliniccount_count,

    count(distinct tr.trid) as reservition_count,

    count(distinct ms.msid) as message_count,

    count(distinct pre.preid) as prescription_count,

    count(distinct utl.utlid) as user_test_list_count



from

  x_user

  left join co  ON co.uid = usertb.uid

  left join pc  ON pc.uid= usertb.uid

  left join tr  ON tr.uid= usertb.uid

  left join ms   ON ms.uid= usertb.uid

  left join pre   ON pre.uid= usertb.uid

  left join utl   ON utl.uid= usertb.uid



 WHERE usertb.uid=14  GROUP BY usertb.uid

اگر نرمال سازی و دی نرمال کردن پایگاه داده را تا سطح مناسب رعایت کرده باشید و ایندکس های کاربردی بر روی جداول ایجاد کرده باشید و همچنین از ابزارهای پروفایلر برای بهینه کردن کوئری ها و estimate plan , actual plan را محاسبه شده باشه! قطعا در سرعت استخراج داده ها تاثیر گذار خواهد بود. البته به نظر میاد با توجه به تعداد زیاد جداول بیش از حد لازم نرمال سازی را انجام دادید که قطعا این مسئله هم هزینه بر خواهد بود.
و راه حل دیگه هم به نظرم بهتره فیلد های شمارشی را در یک کوئری جداگانه به صورت job service بنویسید که این سرویس به طور منظم یک جدول را برای شما به روز رسانی کنه!
بنابراین خواهیم داشت...
1- ساخت یک Job service که همین کوئری را اجرا میکند.
2- نتیجه کوئری در سرویس در یک جدول ذخیره خواهد شد به همراه زمان دقیق به روز رسانی جدول فوق
3- در این مرحله باید چک کنید که فقط داده های جدید را به روز رسانی نمایید تا سرباری هم نداشته باشید
4- در قسمتی که به کوئری اصلی احتیاج دارید یک کوئری بر روی جدول موقت داده میزنید البته اگر داده های شما حساس هستند کافی که یک بار سرویس را اجرا نمایید تا داده به روز شوند
و برای استفاده از این روش همزمانی ابتدا باید سرویس Change Data Capture را روی پایگاه داده خودتون ایجاد کنید وبعد هم Sp بنویسید تا هم سرویس ها براتون استارت کنه وهم منطق های برنامه را پیاده سازی کنه
البته کمی پیچیده هست ولی بسیار کاربردی می باشد و البته به نوع داده های شما و اهمیت و حساسیت این داده ها بستگی داره که بخواهید از این روش استفاده کنید.

ویک روش هم سمت محیط توسعه نرم افزار می باشد به این صورت که کافی که این کوئری را به شش کوئری تقسیم کنید و اگر در سی شارپ هستید می تونید با استفاده از async , TPL این کوئری ها را همزمان اجرا کنید و نتایج را باهم merge کنید و نمایش داده ها و اگر هم که در جاوا هستید می تونید از کلاس های همزمانی ForkJoin استفاده کنید اگر هم در سی پلاس پلاس هستید می تونید از کتابخانه Boost برای همزمانی استفاده کنید

[plague]

روشی که استفاده میکنی اشتباهه به طور خلاصه مقادیر عددی روبایداز پیش شمرده شده ذخیره داشته باشی یجا
تو همون تیبل usertb یه فیلد به ازی هر کدوم از ایناعداد شمارشی بزار مثلا یکیشونف یلد conversation_count
بعد برای جدول co یه تریگر بنویس که هربار یه ردیف بهش اضافه شد بره تو تیبل usertb و فیلدش رو افزایش بده
یه همچین چیزی میشه

DELIMITER $$

CREATE TRIGGER insert_co  AFTER INSERT ON co 

  FOR EACH ROW

    BEGIN

      UPDATE usertb  set conversation_count  = conversation_count + 1  WHERE  uid = NEW.uid ;

    END;

$$

DELIMITER ;

[ali_sed]

سلام

دوستان توضیحات کاملی دادند. بهترین روش این است که تعداد را در جدول کاربران ذخیره کنید و در زمان ایجاد، ویرایش یا حذف داده ها شمارنده خود را بروز کنید.

اما در مورد کوئری دقت داشته باشید تا زمانی که دلیل قانع کننده ای ندارید از جوین استفاده نکنید. اگر صرفا قصد شمارش سطرهای داده های یک کاربر در چند جدول را دارید کافیست برای هر جدول یک کوئری جدا بنویسید حتی می توانید با استفاده از دستور union انها را با هم تجمیع کنید. یا اینکه از روش ساده زیر استفاده کنید. (توجه: ممکن است بسته به جداول شما نیاز به تغییرات داشته باشد)

select usertb.*, 

(select count(*) from co where co.uid = usertb.uid) as conversation_count,

(select count(*) from pc where pc.uid = usertb.uid) as paracliniccount_count,

(select count(*) from tr where tr.uid = usertb.uid) as reservition_count,

(select count(*) from ms where ms.uid = usertb.uid) as message_count,

(select count(*) from pre where pre.uid = usertb.uid) as prescription_count,

(select count(*) from utl where utl.uid = usertb.uid) as user_test_list_count,

from x_user as usertb

WHERE usertb.uid=14

[sara_aryanfar]

روشی که استفاده میکنی اشتباهه به طور خلاصه مقادیر عددی روبایداز پیش شمرده شده ذخیره داشته باشی یجا
تو همون تیبل usertb یه فیلد به ازی هر کدوم از ایناعداد شمارشی بزار مثلا یکیشونف یلد conversation_count
بعد برای جدول co یه تریگر بنویس که هربار یه ردیف بهش اضافه شد بره تو تیبل usertb و فیلدش رو افزایش بده
یه همچین چیزی میشه

DELIMITER $$

CREATE TRIGGER insert_co  AFTER INSERT ON co 

  FOR EACH ROW

    BEGIN

      UPDATE usertb  set conversation_count  = conversation_count + 1  WHERE  uid = NEW.uid ;

    END;

$$

DELIMITER ;

بله ظاهرا روش درستی نیست و البته ظاهرا کلا جوین کردن دردسر های این چنینی زیاد داره به خصوص در تعداد جدول بالای 3 تا با جستجوهایی که انجام دادم و تلاش های زیاد عملا سرعت کوئری کاهش چندانی نکرد و ناچار شدم کوئری رو بشکنم و در چند کوئری دیتا بگیرم و ترکیب کنم .
اما منطقی که شما می فرمایید که برای هر کاربر به تعداد جداولی که درگیرش هست یه فیلد کانتر بزارم خوبه اما تنها به شرطی که حتما چک بشه در صورت حذف و اضافه فیلد ما حتما به روز بشه البته با تشکر از شما و سایر دوستان حتمان این نکته رو در ذهن خواهم داشت که منبعد این مقادیر شمارشی رو یا در یک جدول یا در همون جدول کاربران داشته باشم

[sara_aryanfar]

ممنون از پاسختون

[sara_aryanfar]

سلام

دوستان توضیحات کاملی دادند. بهترین روش این است که تعداد را در جدول کاربران ذخیره کنید و در زمان ایجاد، ویرایش یا حذف داده ها شمارنده خود را بروز کنید.

اما در مورد کوئری دقت داشته باشید تا زمانی که دلیل قانع کننده ای ندارید از جوین استفاده نکنید. اگر صرفا قصد شمارش سطرهای داده های یک کاربر در چند جدول را دارید کافیست برای هر جدول یک کوئری جدا بنویسید حتی می توانید با استفاده از دستور union انها را با هم تجمیع کنید. یا اینکه از روش ساده زیر استفاده کنید. (توجه: ممکن است بسته به جداول شما نیاز به تغییرات داشته باشد)

select usertb.*, 

(select count(*) from co where co.uid = usertb.uid) as conversation_count,

(select count(*) from pc where pc.uid = usertb.uid) as paracliniccount_count,

(select count(*) from tr where tr.uid = usertb.uid) as reservition_count,

(select count(*) from ms where ms.uid = usertb.uid) as message_count,

(select count(*) from pre where pre.uid = usertb.uid) as prescription_count,

(select count(*) from utl where utl.uid = usertb.uid) as user_test_list_count,

from x_user as usertb

WHERE usertb.uid=14

ممنون از پاسخ شما تقریبا با راهکاری نزدیک به این عمل کردم اما در مورد جوین خب ما اکثرا مجبوریم چون داده های ما در جداول متفاوتی حضور دارن و ما بدون جوین کردن اونا نمی تونیم اون نتیجه هدف رو داشته باشیم مثلا سه جدول رو در نظر بگیرید یکی کاربر دیگری فیش های حقوقی و دیگری ساعات کارکرد فرد بدون اینکه این رو جوین کنید چطور می خواهید داده ها رو واکشی کنید؟

[plague]

بله ظاهرا روش درستی نیست و البته ظاهرا کلا جوین کردن دردسر های این چنینی زیاد داره به خصوص در تعداد جدول بالای 3 تا با جستجوهایی که انجام دادم و تلاش های زیاد عملا سرعت کوئری کاهش چندانی نکرد و ناچار شدم کوئری رو بشکنم و در چند کوئری دیتا بگیرم و ترکیب کنم .
اما منطقی که شما می فرمایید که برای هر کاربر به تعداد جداولی که درگیرش هست یه فیلد کانتر بزارم خوبه اما تنها به شرطی که حتما چک بشه در صورت حذف و اضافه فیلد ما حتما به روز بشه البته با تشکر از شما و سایر دوستان حتمان این نکته رو در ذهن خواهم داشت که منبعد این مقادیر شمارشی رو یا در یک جدول یا در همون جدول کاربران داشته باشم

راجب trigger ها تحقیق کن میتونی برای همه عملیات Insert , update , delete تریگر بنویسی که اتوماتیک شمارنده ها رو به روز کنن (من تریگر اینسرت رو نوشتم برات )
فوقالعاده به درد بخور و مفید هستن چون نیازی به کد نویسی php ندارن و مستقیم تو دیتابیس کد اجرا میشه و کاملا بدون اشتباه کار میکنن

[ali_sed]

ممنون از پاسخ شما تقریبا با راهکاری نزدیک به این عمل کردم اما در مورد جوین خب ما اکثرا مجبوریم چون داده های ما در جداول متفاوتی حضور دارن و ما بدون جوین کردن اونا نمی تونیم اون نتیجه هدف رو داشته باشیم مثلا سه جدول رو در نظر بگیرید یکی کاربر دیگری فیش های حقوقی و دیگری ساعات کارکرد فرد بدون اینکه این رو جوین کنید چطور می خواهید داده ها رو واکشی کنید؟

خواهش می کنم. بحث در مورد استفاده مناسب از جوین در کوئری مورد نظر شما بود نه بیشتر.