ارتقا Query در جهت افزایش Performance

[persdollar]

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


107433


ایندکس های تعریف شده:

- TID

- Name

- Field n در واقع برای تمامی ستون ها



اما از اونجایی که تعداد ستون ها زیاد و همچنین برای هر رکورد اساسا ثابت هم نیست، از ساختار زیر استفاده کرده ام. یعنی در جدول فرعی زیر ممکن است برای یک TID تنها Field1 و Field2 وارد شود و برای TID دیگری تنها Field4 وبه همین شکل برای TID های دیگر.



107435



ایندکس های تعریف شده:

- TID

- Name


107436


ایندکس های تعریف شده:

- SID

- TID

- (FieldName,FieldValue)



اما از اونجایی که می خواستیم Performance را تا حد قابل قبول تری ارتقا دهیم و از join برای بدست آوردن مقدار Name در Query استفاده نکنیم از مقدار Redundace فیلد Name در جدول فرعی هم استفاده کردیم، که با توجه به نوع پروژه مشکلی بوجود نخواهد آمد.مانند زیر:

107437



ایندکس های تعریف شده:

- SID
- Name


- (TID,FieldName,FieldValue)



تا اینجا مشکلی نداریم. و حتی می توانیم با مکانیزم های مختلف مثل Pivot در Query هایمان، جدول را به حالت اولیه اش بر گردانیم. اما مشکل اصلی سر Performance انجام Query هست. از آنجایی که Performance واسمون مهمه دنبال یه Query خوب هستم.

اما نمونه ای از Query اصلی که روی جدول اصلی شکل 1 قرار است اجرا گردد.(تعداد and ها در هر query می تواند متفاوت باشد)





select TID from T where Name = v and Field1= v1 and Field15 = v2 and Field110 = v3 and …





ما این Query را در ساختار جدید بصورت زیر نوشتیم.


select distinct temp.TID from (select * from S where Name = v) as temp where
exists(select TID from S where TID = temp.TID and FieldName = ‘Field1’ and FieldValue = v1) and
exists(select TID from S where TID = temp.TID and FieldName = ‘Field15’ and FieldValue = v2) and
exists(select TID from S where TID = temp.TID and FieldName = ‘Field110‘ and FieldValue = v3) and…










قابل ذکر است که FieldName ها از نوع عددی در نظر گرفته می شود که سرعت کار بالا رود.

حال با این تفاسیر از تمامی دوستان خواهش می کنم در صورت امکان در بهبود Performance این Query بنده را راهنمایی کنید. پیشاپیش متشکرم.

[hamid_hr]

من خيلي بلد نيستم
ولي يه سوال دارم
اون Name تو جدول دوم كارش چي هست؟

فك كنيد شما 1000 تا نام داريد ولي به ازاي هر نام 7 تا فيلد داريد حالا تو جدول دوم 7000 فيلد اضافي برا Name داريم
ولي اگه دليل خاصي داري پس جدول اول كارش چي ميشه ما تو جدول دوم هم نام رو داريم هم TID رو. يعني اصلا jion لازم نداريم

با يك join ساده هم ميتوني اطلاعاتو بگيري

select * from T2
left join T1 on T2.TID = T1.TID
where T1.Name = '' and (T2.FildName = '' and T2.Value ='')
and (T2.FildName = '' and T2.Value ='') and ...

[persdollar]

مرسی دوست عزیزم. name دوم که در جدول S قرار دارد، یک دیتا افزونه با تکرار تقریبا 20 برابر است. که البته همین Name در حالت درستش در جدول T هم قرار گرفته است. استفاده از این نوع افزونه زمانی استفاده می گردد که بخواهیم زمان اجرای پرس و جو های SQL را کاهش دهیم. کاهش از این جهت که دیگر احتیاجی به یک JOIN به ظاهر کوچک اما فوق العاده زمانبر نباشد. قابل ذکر است استفاده از افزونه Name در این جدول بیشتر از آن لحاظ است که اولا در تمامی پرس و جوهای ما از Name استفاده شده و بنابراین استفاده از آن JOIN وحشتناک اجتناب ناپذیر است. دوما با قرار دادن چنین فیلد افزونه ای در این جدول می توانیم با استفاده از یک ایندکس سه تایی Name,FieldName,FieldValue سرعت اجرا را به طرز وحشتناکی نسبت به JOIN افزایش دهیم. نکته ای که بیان آن در اینجا خالی از لطف نیست، این است که استفاده از افزونه ها در جاهایی که این افزونه ها مدام در حال تغییرند به صلاح نیست. چون درست است که به ظاهر سرعت انجام کوئری بالا میرود اما هزینه سربار اضافی Update مقادیر این گونه فیلدها و همچنین Update شدن Index های مربوطشان غیرقابل اقماض میباشد. که البته در مورد مسئله ما این مورد مشکلی وجود ندارد. نکته دیگر اینکه صحبت در اینجا سر میلی ثانیه است. کوئری ما در حالت اولیه (بدون لود صفحات ملزوم آن) به طور تقریبی 60 میلی ثانیه طول میکشد که سعی ما بر این است که این زمان را تا 5 میلی ثانیه کاهش دهیم. البته هزینه انجام کار و تغییرات هم مدنظر ما می باشد. دستتون بابت کوئری درد نکنه، گرچه که منظور شما رو از T1 , T2 متوجه نشدم، که احتمالا همان جدول S مدنظرتان بوده است. منتهی اصلا خروجی مد نظر ما رو تولید نمی کند , و البته به لحاظ ساختاری هم دچار مشکلاتی هست. چون وقتی دو جدول از نوع S یا T یا هر جدول دیگری رو با هم Left Join یا هر Join دیگری میکنیم، حداکثر تعداد فیلد های هم Source میتواند دوتا باشد و نه بیشتر. این درصورتی است که شما از and های پیاپی به قصد مقایسه فیلدهای متفاوت استفاده کردید، قافل از اینکه هم به لحاظ ساختاری اشکال دارد و هم به لحاظ محتوی و البته مفهوم کوئری مورد نظر ما. نکته مهمی که باید ذکر کنم این است که مورد استفاده این نوع جداول بسیار خاص و معمولا به لحاظ پیچیدگی در پرداختن به کوئری های متفاوت که نشان از Flexibility آن می باشد، استفاده نمیگردند. منتهی از اونجایی که پروژه خاص میباشد، مجبوریم نه تنها از این ساختار بلکه از ساختارهای عجیب تر از این هم استفاده کنیم. مرسی Hamid_hr :-)

[محمد سلیم آبادی]

به جای این Query از Query پیشنهادی من استفاده کنید
select distinct temp.TID from (select * from S where Name = v) as temp where
exists(select TID from S where TID = temp.TID and FieldName = ‘Field1’ and FieldValue = v1) and
exists(select TID from S where TID = temp.TID and FieldName = ‘Field15’ and FieldValue = v2) and
exists(select TID from S where TID = temp.TID and FieldName = ‘Field110‘ and FieldValue = v3) and…



select TID
from S
where name = 'v'
group by TID
having sum(case when (fieldname = 'field1' and fieldvalue = 'v1')
or (fieldname = 'field15' and fieldvalue = 'v2')
or (fieldname = 'field110' and fieldvalue = 'v3') then 1 else 0 end) = 3;

[persdollar]

سلام.

مرسی از زحمتی که کشیدید. کوئری رو تست کردم، اما بهبود قابل لمسی در اجرا دیده نشد. اما ما هنوز دیتا واقعی در اختیار نداریم. و دیتاهایی که تست ها روش انجام میدهیم، تولید شده به دست خودمون هست. دیتاهای واقعی با کوئری واقعی که کمی شاخ و برگش بیشتر هست، به احتمال قوی نقاط ضعف بیشتری رو نشان خواهد داد:افسرده:

[یوسف زالی]

سلام.
اصلا متوجه دلیل کارتون نشدم.
ولی به نظر می رسه این باید جواب بده:


select TID
from S
where
(Name = v)
and
(
(FieldName = ‘Field1’ and FieldValue = v1)
or
(FieldName = ‘Field15’ and FieldValue = v2)
or
(FieldName = ‘Field110‘ and FieldValue = v3)
)

[محمد سلیم آبادی]

سلام

مرسی از زحمتی که کشیدید. کوئری رو تست کردم، اما بهبود قابل لمسی در اجرا دیده نشد. اما ما هنوز دیتا واقعی در اختیار نداریم. و دیتاهایی که تست ها روش انجام میدهیم، تولید شده به دست خودمون هستquery که شما پست کردین از سه subquery تشکیل شده است. و اگر بخواهید شرط را گسترش بدین نیاز دارین دائما این subquery ها را نیز افزایش بدین که باعث افت عملکرد میشه. در صورتی که در query که نوشتم نیاز به هیچگونه subquery یا عمل join نیست. و داده های جدول یکبار بیشتر scan نمی شوند.

اما در مورد پست جناب یوسف

اصلا متوجه دلیل کارتون نشدم.همانطور که کاربر در جملاتش اشاره کرد تعداد ستون ها یا بهتر بگویم attribute ها به ازای هر سطر متغیر است ممکن است یک سطر دارای دو خصیصه باشد و یک سطر دارای بیست خصیصه. از طرفی چون ممکن است تعداد این خصیصه ها محدود نباشد به هیچ وجه امکان ایجاد یه جدول با ساختار معمولی را ندارد. از این رو از ساختار EAV استفاده کرده است.
و مساله ی دیگری که وجود دارد ادغام دو جدول T و S به جهت از بین بردن نیاز به عمل join است. در این حالت چون جداول از حالت نرمال خارج شدن مشکلاتی چون افزونگی داده ها و مشکل در modify دور از انتظار نیستند که کاربر به خوبی به آنها اشاره کردن.


ولی به نظر می رسه این باید جواب بده:شما متوجه صورت مساله نشدین.
کاربر می خواهد بداند که کدام TID در فیلد 1 مقدار v1 و در فیلد 5 مقدار v2 و در فیلد 10 مقدار v3 ثبت شده است. توجه کنید که باید هر سه شرط برقرار شود تا نتیجه حاصل شود... و هر کدام از این فیلد ها در یک سطر جدا قرار دارند نه در یک سطر...

[یوسف زالی]

مرسی بابت توضیحات.
اصلا متوجه توضیحات دوستمون نشده بودم.
و یک سوال:


داده های جدول یکبار بیشتر scan نمی شوند.

چطور مطمئن می شید از این که داده ها چند بار اسکن می شن؟
آیا روش خاصی داره یا همون روش Execution Plan و این داستان هاست؟
و این که EAV مخفف چیه؟

ممنون.

[محمد سلیم آبادی]

برای اینکه بدانیم دقیقا یک query برای اجرا شدن چند بار جداول موجود را scan می کند ابتدا بایستی قبل از اجرای آن این کد را اجرا کرده set statistics io on سپس پس از پایان یافتن اجرا آمار را در قسمت message رویت کنید.
البته تا حدودی می شود با توجه به خود query تعداد scan جداول را پیش بینی کرد. گرچه برخی پارامتر در جابجایی این مقدار دخیل اند مثل index های استفاده شده در plan و مقدار داده های موجود در جداول.

فرض کنید دو جدول داریم بدون هیچ گونه شاخصی. یکی به نام مشتری و دیگری به نام سفارشات. حال میخواهیم تعداد سفارشات مشتری همراه با مشخصات آن را بدست بیاوریم به این شکل:

SELECT customer.*, (SELECT COUNT(*) FROM Orders WHERE customerID = T.customerID) AS cnt
FROM Customer AS T
حال اگر سه مشتری در جدول مربوطه ثبت شده باشد. تعداد scan جدول Customer یک و جدول Orders سه خواهد بود. چرا که به ازای هر مشتری یکبار جدول Orders فراخوانی می شود. این موضوع را به سادگی می توانید بررسی کنید.

EAV برابر با Entity Attribute Value البته به Open Schema نیز شناخته می شود.

[persdollar]

با سلام خدمت دوست خوبم آقای سلیم آبادی. مرسی از زحمت دوباره اتان. خیلی خوبه این مسائل با این جزئیات مورد بررسی قرار گیرد. چهار نکته در مورد کوئری که زحمتش رو کشیدید من اضافه کنم:

نکته اول اینکه Scan شدن کامل دیتای جدول بحث کاملا مهمی است و قطعا Query ای که در آن Scan دیتا به شکلی باشد که قابل کاهش باشد (در مواردی که Performance نقش حیاتی را بازی کند) قابل تحمل نمیباشد. اما در اینجا میبایست به نکته دیگری نیز توجه کرد و اون نقش index ها و statistics ها در Scan جداول میباشد. در صورتی که index ها دارای پراکندگی حداقلی باشند، Scan از طریق index ها و statistics ها، دارای مقداری کمتر از log n می باشد. بنابراین نمیتوان به صرف تعداد Scan بالا یا تعداد Scan حداقلی در مورد Performance اظهار نظر قطعی کرد.

نکته دوم که خیلی هم ظریف میباشد short circuit بودن توابع بولی در اغلب زبان های برنامه نویسی میباشد. که این پارامتر می تواند به عنوان یک پارامتر تاثیر گذار در اجرای پرس و جوها مد نظر قرار گیرد. این امر در کوئری پیشنهادی شما لحاظ نشده است این در صورتی است که در کوئری نوشته شده اولیه از این پارامتر در جهت اجرای سریعتر کوئری استفاده گردیده است.


نکته سوم اینکه هزینه اجرای group by نسبتا بالاست.

نکته انتهایی اینکه هر دو کوئری روی داده های یکسان، و در شرایط همسان اجرا گردید. سرعت اجرای کوئری پیشنهادی بر خلاف انتظار اولیه ما از سرعت کمتری برخوردار می باشد.

ممنونم که وقت می گذارید. واقعا ممنونم.

[محمد سلیم آبادی]

من متوجه صحبت هاتون نشدم.
این راه حل هم خیلی مناسبه:

SELECT S1.TID
FROM S S1
INNER JOIN S S2
ON S1.TID = S2.T.ID
INNER JOIN S S3
ON S1.TID = S3.TID
WHERE Name = v
AND S1.FieldName =‘Field1’ and S1.FieldValue = v1
AND S2.FieldName = ‘Field15’ and S2.FieldValue = v2
AND S3.FieldName = ‘Field110‘ and S3.FieldValue = v3

ضمنا اگر علاقه ی زیادی به این بحث دارین می تونید این مقاله هم مطالعه کنید.
http://weblogs.sqlteam.com/davidm/articles/12117.aspx