مشکل در Join جدولی با تعداد FK زیاد

[persdollar]

با سلام...
امیدوارم که جای پستم درست باشه اگه اشتباه است لطفا مدیران به مکان صحیح انتقال بدهند.
مشکلی که من با اون برخورد کردم در Performance و زمان اجرای Query هست، که تعداد زیادی کلید خارجی دارد.
Query مورد نظر مربوط به DB هست که تعداد Table های اون تقریبا زیاد هست. این Query با تعدادی از این جداول کار می کند. که ساختار ارتباطی این جداول در عکس Upload شده مشخص شده است.


71222
در واقع عمده مشکل این Query در تعداد Join زیاد (مثلا 15) از جدولی به یک جدول خاص می باشد، که مستلزم تعدادی Join به ازا هر کلید خارجی به آن جدول می باشد. جدول اصلی که ما از آن برای گرفتن Base گزارش استفاده می کنیم 51 کلید خارجی دارد. که تعداد زیادی از این کلیدهای خارجی به جداول مشابه و یکسانی می باشد. همچنین خود جداولی که در طرف دیگر این کلیدهای خارجی قرار گرفته اند نیز، خود شامل تعدادی کلید خارجی دیگر می باشند.
دو راه حلی که به نظر خودم رسید، اولا کمک گرفتن از Persistent Computed Columns برای نگه داری مقادیری است که در این Relation های ملزوم در گزارشگیری نیاز است و راه حل دیگر استفاده از Indexed View ها می باشد.
اما در استفاده از هر یک از این راه حل ها نیز مشکلاتی پیش رو می باشد. لطفا در صورت امکان در این مورد بنده را راه نمایی بفرمایید.
با تشکر م.م.ر

[حمیدرضاصادقیان]

سلام.
دوست عزیز به این شکل که نمیشه راهنمایی کرد.چون نه از اطلاعات شما ما اطلاعی داریم. نه از ساختار جداولتون. نه اینکه Query شما به چه شکله. نه Plan اونو دیدیم.
شما اول ساختار جداول رو قرار بدید حداقل اون جداولی که برای گزارشتون نیاز دارید. شاید اصلا نیازی به این همه جدول نباشه. یا اینکه حداقل Query مورد نظر با Plan رو اینجا قرار بدید .

[persdollar]

با سلام.
در ابتدا تشکر می کنم از توجه جنابعالی. احساسم این بود که، عکسی که از دیاگرام تعدادی از جداول مربوط به Query گذاشته ام، به نحوی کامل و بیانگر طرح مسئله می باشد. به نظرم بهتر است مقداری در مورد خود DB اطلاعاتی را اضافه کنم و سپس خود Query.
ببنید DB مربوط به سیستم ما، حدود 200 تا Table بزرگ و کوچک داره. که به طور میانگین هر یک از این جداول چیزی حدود 50 تا فیلد را در خودشان نگه داری می کنند. از آنجایی که ما از Replication استفاده می کنیم تمامی کلیدهای اصلی و خارجی را به صورت Guid انتخاب کرده ایم.
از طرفی چون بسیاری از جداول ما یک سری اطلاعات تکراری را در خود نگه داری می کرده اند، ناچارا از روابط IS-A در طراحی سیستم استفاده کرده ایم. که البته این امر در سمت خود برنامه و در لایه Dal و Bol از آن جهت که همین روابط را به شکل ارث بری پیاده کرده ایم، خیلی خیلی کار ما را ساده و تقریبا اصولی کرده است.
خوب حالا مشکل از اینجا شروع شد که قرار است گزارشی را در برنامه اضافه کنیم که یک سری اطلاعات را از جداول مربوطه در دیاگرام شکل فوق بازیابی کند. با اینکه سیستم هنوز عمر خودش را نکرده و دیتابیس حجیم نشده و جدول اصلی گزارش تنها 8000 رکورد بیشتر ندارد، زمان لازم برای Run شدن Query تقریبا 30 ثانیه می شود!! که این زمان با توجه به استفاده مداوم از این گزارش، غیر قابل قبول می باشد.
در شکل فوق جدول اصلی ما در سمت چپ شکل قرار گرفته است و Base گزارش از روی آن ساخته می شود. هر رکورد از این جدول 51 کلید خارجی دارد، که Refrence این 51 FK به جداول شماره 1،2،3،7 در شکل بر می گردد.
تا همینجا ما کلی مشکل سرعتی داریم. چون باید مثلا به جدول شماره 2، نزدیک به 20 تا Join بزنیم. و یا مثلا به جداول 1 و 3 تقریبا 15 و 15 Join بزنیم. چاره ای هم البته نداشتیم!!
خوب حالا مشخصات خود Query. پایه Query را از جدول اصلی شروع کرده ایم. طرحی از Query در شکل زیر آورده شده است:
71261

قسمتهای قرمز رنگ که با 2 ستاره مشخص شده است، نشان دهنده تکرار کروشه به تعداد عدد مشخص شده است.
اگر اشتباهی نکرده باشم، Query مربوطه یک همچین حالتی دارد. البته روی تک تک جداولی که Join می شوند دستورات Select ی قرار داده ایم که تعداد فیلدهای مربوطه را به حداقل لازم برساند. این حداقل لازم در جداولی که از جدول اصلی دورتر هستند تقریبا 2 یا 3 فیلد می باشد. همچنین در جداولی که Index به سرعت Query کمک می کرده، Index هایی را اضافه کرده ام.
لطفا در صورتی که فکر می کنید طریقه Join کردن جداول اشتباه می باشد و یا بهینه نیست، متذکر شوید. همچنین اگر راه حلی به غیر از دست کاری Query مدنظر شما می باشد، که بتواند مثلا از تکنولوژی خاصی یا شگرد خاصی در حل مشکل استفاده کرد، بفرمایید، تا بتوانم از راهنمایی شما در حل مشکل استفاده کنم.
یک عکس دیگر نیز در ادامه Upload کرده ام، که در آن Relation های مهم را که می بایست در Query آورده شود با یک سری برچسب نشان داده ام.
71262
با تشکر ویژه از توجه شما، خواهشمندم در صورت امکان بنده را راهنمایی بفرمایید.

[elahe khani]

سلام دوست عزیز
لازم نیست به ازای هر کلید خارجی یک Join بزنید بلکه بایذ به ازای هر دو جدول مرتبط یک join با تعداد زیادی شرط جلوی on بزنید. مثل:
select *
from cusomers c
join orders o
on o.customerid=c.customerid and o.city=c.city and ...

[حمیدرضاصادقیان]

سلام مجدد.
اولین مشکلی که به نظرمن وجود داره تعداد بالای Relation ها بین جداول است. وابستگی بین جداول شما خیلی زیاده. اطلاعاتی که وارد دیتابیس میکنید مربوطه به سیستم مالی هست؟
باز با اینکه این همه Relation دارید تعداد فیلدهای جداول نیز به نظرم زیادن.
لطفا Query که اجرا میکنید Execution Plan اونو اینجا قرار بدید.

[persdollar]

سلام دوست عزیز
لازم نیست به ازای هر کلید خارجی یک Join بزنید بلکه بایذ به ازای هر دو جدول مرتبط یک join با تعداد زیادی شرط جلوی on بزنید. مثل:
select *
from cusomers c
join orders o
on o.customerid=c.customerid and o.city=c.city and ...

ممنون از توجه جنابعالی.
اگر کاری که شما گفتید انجام بدهیم، اون موقع دیگه خروجی Query ما هیچ رکوردی را بر نمی گرداند...

[AMIBCT]

جدا از اينكه اين مشكل چه طوري حل مي‌شه

خيلي كنجكاو شدم بدونم چه منطقي پشت ۱۹ تا كليد خارجي از يه جدول به يه جدول ديگه مي‌تونه باشه

اگه اين جدول‌ها واقعي هستن لطفا اطلاعات بيشتر بديد

[persdollar]

سلام دوباره خدمت جنابعالی.
باز هم از توجه جنابعالی تشکر می کنم. ببینید Relation های موجود اجتناب ناپذیر هستند. چون ما به ازا هر یک از رکوردهای جدول اصلی، می خواهیم تعدادی اطلاعات تکراری نگه داریم. مثلا فرض کنید به ازا هر رکورد می خواهیم مشخصات 20 آیتم از نوع خاصی را نگه داری کنیم. Execution Plan ، را نتونستم به خاطر حجمش ضمیمه کنم. با اجازتون فایل رو براتون ایمیل کردم. ممنون...

[persdollar]

میشه کوری که نوشتید و هیچ پاسخی نداشت را قرار بدین تا ببینم

از اون جایی که حجم Query خیلی بالاست، اون رو نمی تونم اینجا بگذارم. Query که با مکانیسمی که شما فرمودید، کار کند، را اصلا ننوشتم. اما وضعیتی را در نظر بگیرید، که دو Table داریم با نام های Course و Teacher. فرض را بر این بگذارید که هر درس توسط دو استاد تدریس می شود. به ازا هر استاد یک کلید خارجی در جدول Course داریم با نام های ID_1 و ID_2 . حال خودتان دو Query زیر را که در شکل آمده بررسی کنید. Query درست Query دوم است.


71321

[حمیدرضاصادقیان]

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

Select TblHeader.*,TblDetail.* from TblHeader
Inner join TblDetail
On TblHeader.Code=TblDetail.Code

[persdollar]

سلام

جداول نام گذاری درستی دارند. اما از آنجا که نام ها برای شما مفهومی نداشت مجبور شدم که نام ها را تغییر دهم...
به مثالی که شما زدید کاملا واقف هستم، اما یک تفاوت میان سیستم ما و سیستمی که شما مثال می زنید هست!!

مثال شما:
فرض کنید شما می خواهید یکی از فاکتورهاتون را Fetch کنید. پس با Join ی که انجام می دهیم در واقع 10 رکورد برگردانده می شود(اگر فرض کنیم که هر فاکتور 10 قلم دارد).

سیستم ما:
فرض کنید که هر رکورد از جدول اصلی ما 10 کلید خارجی به یک جدول خاص (جدول یکتا) دارد. آنگاه با Fetch کردن تنها یک رکورد به وسیله Query مورد نظر ما که به 10 عدد Join احتیاج دارد، تنها یک رکورد برگردانده خواهد شد.که در واقع این رکورد با Join هایی که انجام گرفته، تنها از نظر عرضی یا در واقع فیلدها کامل تر شده است. قابل ذکر است که خروجی Query مورد نظر ما در یک Grid نمایش داده خواهد شد. و استفاده از این Query اجتناب ناپذیر است.