JOIN در مقابل IN

[amir.k64]

با جوین قدرت مانور بیشتری داریم یا با کوری های تو در تو (لطفا توضیح بدین)

[armiin]

اسم تاپیکتونو عوض کنید ، الان پاک میشه ها !!!
با Join خیلی راحت تر میشه نوشت ولی سرعت Query تو در تو بیشتره !

راستی دوستان اسم Query تو در تو چی بود ؟ :کف::کف:

[mohsensaghafi]

اسم تاپیکتونو عوض کنید ، الان پاک میشه ها !!!
با Join خیلی راحت تر میشه نوشت ولی سرعت Query تو در تو بیشتره !

راستی دوستان اسم Query تو در تو چی بود ؟ :کف::کف:

سلام دوست عزیز.
زمانی که Query می خواد اجرا بشه توست خود DBMS بهینه می شه بعد اجرا می شه واسه همین از نظر سرعت و حافظه هیچ تفاوتی نمی کنه.
یا علی!

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

راستی دوستان اسم Query تو در تو چی بود ؟
Nested Query
Subquery

[pesar irooni]

زمانی که Query می خواد اجرا بشه توست خود DBMS بهینه می شه بعد اجرا می شه واسه همین از نظر سرعت و حافظه هیچ تفاوتی نمی کنه.
فک نکنم sql server این قدر هوشمند باشه که بتونه join رو با query های تودرتو معادل کنه . مسلما query های تودرتو خیلی سریع تر و کم حافظه تر در اجرا هستند چون ضربه دکارتی بینه جداول انجام نمیشه (البته اگه وابستگی بین subquery با query بیرونی نباشه). اگه بتونید query نویسی با این رو یاد بگیرید خیلی از مشکلاتتون حال میشه.

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

مسلما query های تودرتو خیلی سریع تر و کم حافظه تر در اجرا هستند چون ضربه دکارتی بینه جداول انجام نمیشه (البته اگه وابستگی بین subquery با query بیرونی نباشه)
البته این هم باز بستگی به این دارد که این subquery در کدام ماده (clause) عبارت SELECT مورد استفاده قرار گرفته است. اگر از subquery های وابسته تک مقداری (correlated scalar subquery) در ماده ی SELECT عبارت SELECT استفاده شود در داده های زیاد مشکل عملکردی خواهد داشت و به عملکرد آسیب می رساند.

[mohsensaghafi]

فک نکنم sql server این قدر هوشمند باشه که بتونه join رو با query های تودرتو معادل کنه . مسلما query های تودرتو خیلی سریع تر و کم حافظه تر در اجرا هستند چون ضربه دکارتی بینه جداول انجام نمیشه (البته اگه وابستگی بین subquery با query بیرونی نباشه). اگه بتونید query نویسی با این رو یاد بگیرید خیلی از مشکلاتتون حال میشه.

سلام دوست عزیز.
این اینک رو مطالعه کنید لطفا
http://en.wikipedia.org/wiki/Query_optimizer

[armiin]

بنده چون یک بار این دو حالت مقایسه کردم به وضوح دیدم از نظر سرعت SubQuery دارای سرعت بیشتری هست !
البته نباید با یک بار آزمایش با این قاطعیت جواب میدادم ، شاید در حالاتی درست نباشه !!!


البته این هم باز بستگی به این دارد که این subquery در کدام ماده (clause) عبارت SELECT مورد استفاده قرار گرفته است. اگر از subquery های وابسته تک مقداری (correlated scalar subquery) در ماده ی SELECT عبارت SELECT استفاده شود در داده های زیاد مشکل عملکردی خواهد داشت و به عملکرد آسیب می رساند.
منظورتونو نفهمیدم
میشه بیشتر توضیح بدید !؟:کف:

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

منظورتونو نفهمیدم
میشه بیشتر توضیح بدید !؟
فرض کنید دو جدول داریم که یکی لیست دانشجویان و دیگری دروسی که انتخاب کرده اند. در جدول دروس انتخابی نام درس و کد دانشجو وجود دارد. حالا اگر بخواهیم تمام سطرهای جدول دروس را انتخاب کنیم به طوری که نام دانشجو در کنار کد دانشجویی نمایش داده شود دو راه وجود دارد یکی JOIN و دیگری استفاده از Subquery در ماده ی SELECT یعنی:


SELECT T.*, (SELECT Student_name
FRON students
WHERE s# = t.s#) AS Student_name
FROM courses AS T;

همانطوری که در کوئری فوق مشاهده می شود اگر در جدول Courses یک هزار سطر وجود داشته باشد، به همین تعداد subquery موجود در ماده ی SELECT اجرا خواهد شد. در واقع با اجرای یک Query ما 1000 کوئری را اجرا خواهیم کرد!! که این بجد باعث آسیب رسانی به عملکرد کوئری خواهد شد.

امید وارم توضیحاتم شفاف و مفهوم بوده باشد.

[pesar irooni]

این هم باز بستگی به این دارد که این subquery در کدام ماده (clause) عبارت SELECT مورد استفاده قرار گرفته است
این همون وابستگی که گفتم
جور دیگه ای هم وابستگی داریم. اما اگه نداشته باشیم که در اغلب موارد نداریم سرعت فوق العاده بیشتره

[ASKaffash]

سلام
درست است که خیلی کارها را هم با Join و هم با SubQuery می توان انجام داد ولی همیشه اینطور نیست و مواقعی مثلا Join را نمی توان با SubQuery جانشین کرد موضوع بعدی اینکه در شرایط مساوی سرعت Join خیلی بهتر از SubQuery است من دو سال قبل تحقیقی داشتم و همین موضوع را تائید می کند اینهم یکی از شکلها :

[pesar irooni]

در شرایط مساوی سرعت Join خیلی بهتر از SubQuery است
بعید میدونم
بلکه همون شرایط وابستگی مطرح بشه که مثالش رو دوستمون زدند. شما هم اگه مثال بزنید خیلی بهتره تا ببینیم این شرایط مساوی منظورتون چیه؟

[ASKaffash]

بعید میدونم
بلکه همون شرایط وابستگی مطرح بشه که مثالش رو دوستمون زدند. شما هم اگه مثال بزنید خیلی بهتره تا ببینیم این شرایط مساوی منظورتون چیه؟
سلام
بعید! مطمئن باش این شکل نتیجه یک تحقیق روی داده های واقعی بود که فقط یک شکل ساده آن قرار داده شده است (خود مقاله را نمی توانم قرار دهم)
شرایط مساوی هم مشخص است یعنی دستور یک بد پیاده سازی نشود / حجم داده ها / شرایط سخت افزاری / ....

[mohsensaghafi]

سلام
بعید! مطمئن باش این شکل نتیجه یک تحقیق روی داده های واقعی بود که فقط یک شکل ساده آن قرار داده شده است (خود مقاله را نمی توانم قرار دهم)
شرایط مساوی هم مشخص است یعنی دستور یک بد پیاده سازی نشود / حجم داده ها / شرایط سخت افزاری / ....

سلام دوست عزیز.
میشه بگید منظورتون از تحقیق روی داده های واقعی چیه؟! شرایط رو بیشتر توضیح بدید لطفاً. زمانی که یک optimizer روی DBMS هست، فرقی نمی کند که شما دستورت رو چطوری بنویسی. چون خود DBMS اون Query رو Optimize می کنه. حالا به هر صورتی که می خواید query رو بنویسید. پس تفاوتی بین کوئری های تو در تو و Join وجود ندارد.

[ASKaffash]

سلام
اصلا اینطوری نیست یک نمونه را آقای msalim گفت یک SubQuery به تعداد n رکورد به جدول رجوع می کند ولی یک Join اینطوری است ؟ درضمن بهینه سازی به این معنی نیست که یک SubQuery شبیه سازی به یک Join می شود دریک تحقیق روی 100 میلیون رکورد اطلاعاتی در یک جدول و شرایط متفاوت و در هربار 100 بار با ایندکس کلاستری / غیرکلاستری / بی ایندکس / با Join / با SubQuery / ... شرایط آزمایش را ایجاد کرده بود(وخیلی موارد دیگر)

[pesar irooni]

من اینجا یه مثلا میزنم. لطفآ از ملاحضات طراحی اجتناب کنید.
سیستم انتخاب واحد دانشجویی ، یه جدول که اطلاعات دانشجو ها رو داره ، یه جدول هم که اطلاعات گروه ها رو داره (گروه میگه هر درسی در چه روزی با چه استادی و ... ارائه میشه) ، یه جدول هم به نام اخذ واحد که میگه هر دانشجو چه گروه هایی انتخاب میکنه
میخایم ببینیم دانشجوهای مشروطی چه واحدهایی رو برداشتند (فعلا فقط id شون)
دو راه داریم
راه اول

select gid from selection where sid in (select id from student where avgScore <12)
راه دوم

select gid from selection inner join student
on student.id = selection.sid and avgScore < 12
اگه جدول دانشجو n تا رکورد و جدول اخذ m تا رکورد داشته باشه اولی n + m رکورد و جدول دوم n * m رکورد رو پردازش میکننند
البته where هم تاثیر گزاره که فعلا در نظر نمیگیریم

[mohsensaghafi]

سلام
اصلا اینطوری نیست یک نمونه را آقای msalim گفت یک SubQuery به تعداد n رکورد به جدول رجوع می کند ولی یک Join اینطوری است ؟ درضمن بهینه سازی به این معنی نیست که یک SubQuery شبیه سازی به یک Join می شود دریک تحقیق روی 100 میلیون رکورد اطلاعاتی در یک جدول و شرایط متفاوت و در هربار 100 بار با ایندکس کلاستری / غیرکلاستری / بی ایندکس / با Join / با SubQuery / ... شرایط آزمایش را ایجاد کرده بود(وخیلی موارد دیگر)

سلام دوست عزیز.
مشکل شما اینجاست که فکر می کنید قرار است SubQuery به Join تبدیل شود. تمام این دستورات SQL ، به یک زبان ریاضی که قابل فهم برای DBMS هست تبدیل می شود. و این زبان ریاضی بهینه می شود. حال تفاوتی نمی کند که شما دستور اولیه را به چه صورتی نوشته باشید.

[mohsensaghafi]

من اینجا یه مثلا میزنم. لطفآ از ملاحضات طراحی اجتناب کنید.
سیستم انتخاب واحد دانشجویی ، یه جدول که اطلاعات دانشجو ها رو داره ، یه جدول هم که اطلاعات گروه ها رو داره (گروه میگه هر درسی در چه روزی با چه استادی و ... ارائه میشه) ، یه جدول هم به نام اخذ واحد که میگه هر دانشجو چه گروه هایی انتخاب میکنه
میخایم ببینیم دانشجوهای مشروطی چه واحدهایی رو برداشتند (فعلا فقط id شون)
دو راه داریم
راه اول

select gid from selection where sid in (select id from student where avgScore <12)
راه دوم

select gid from selection inner join student
on student.id = selection.sid and avgScore < 12
اگه جدول دانشجو n تا رکورد و جدول اخذ m تا رکورد داشته باشه اولی n + m رکورد و جدول دوم n * m رکورد رو پردازش میکننند
البته where هم تاثیر گزاره که فعلا در نظر نمیگیریم
سلام دوست عزیز.
این از نگاه من و شماست که بصورت برنامه نویسی داریم به این قضیه نگاه می کنیم. در DBMS ها اتفاق دیگری می افته. همونطوری که گفتم تمام این دستورات SQL به یه زبان ریاضی قابل فهم برای DBMS تبدیل می شه و اون زبان بهینه می شه و نهایتا اجرا می شه. در این صورت نوع نوشتن Query تفاوتی نمی کنه.

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

سلام دوست عزیز.
این از نگاه من و شماست که بصورت برنامه نویسی داریم به این قضیه نگاه می کنیم. در DBMS ها اتفاق دیگری می افته. همونطوری که گفتم تمام این دستورات SQL به یه زبان ریاضی قابل فهم برای DBMS تبدیل می شه و اون زبان بهینه می شه و نهایتا اجرا می شه. در این صورت نوع نوشتن Query تفاوتی نمی کنه.
این چیزی که شما سعی در بیان آن دارین کاملا درست است.
Optimizer یک نرم افزار بهینه کنند است که بهترین Plan را برای اجرای یک کوئری انتخاب می کند. یعنی ابتدا تمام راه هایی که برای اجرای شدن یک Query موجود است را بررسی می کند و بهترین را از لحاظ Cost کم انتخاب کرده و اجرا می کند. (این Plan در حافظه ی Cache قرار می گیرد تا دفعات دیگر از آن استفاده شود)
نکته ای که وجود دارد این است که RDBMS هایی چون Microsoft SQL Server چندان به مباحث Query Logical Ordering پایبند نیستند. یعنی اینکه ممکن است کوئری ما طبق مراحل منطقی که ماده ی های عبارت SELECT اجرا می شوند عمل نکند و فیلترها را جابجا کند.
از طرفی JOIN و IN تنها در بحث Semi-Join قابل مقایسه هستند. ولی زمانی که بخواهیم ستونهایی را از جدول یا جداول دیگر انتخاب کنیم دیگر Subquery اصلا روش موثری نخواهد بود.
در تئوری به Relational Calculus بهای بیشتری به Relational Algebra داده شده است. بطور مثال آقای دیت در کتاب های تالیفی خود بیشتر از Correlated Subquery استفاده می کند تا JOIN و امثالهم.

و باز مجددا تکرار می کنم RDBMS اصلا کاری به بحث منطقی کوئری ندارد و مباحثی که راجب ضرب دکارتی و اینگونه مسائل شده است تنها در بحث منطقی می گنجند.

[ASKaffash]

سلام دوست عزیز.
مشکل شما اینجاست که فکر می کنید قرار است SubQuery به Join تبدیل شود. تمام این دستورات SQL ، به یک زبان ریاضی که قابل فهم برای DBMS هست تبدیل می شود. و این زبان ریاضی بهینه می شود. حال تفاوتی نمی کند که شما دستور اولیه را به چه صورتی نوشته باشید.
از کجای حرف من شما نتیجه گرفتید که Join قرار است به SubQuery تبدیل شود ؟
در ضمن شما یک منبع معتبر ارائه دهید که این جمله را تائید کند : دستورات SQL ، به یک زبان ریاضی که قابل فهم برای DBMS هست تبدیل می شود.

[mohsensaghafi]

از کجای حرف من شما نتیجه گرفتید که Join قرار است به SubQuery تبدیل شود ؟
در ضمن شما یک منبع معتبر ارائه دهید که این جمله را تائید کند : دستورات SQL ، به یک زبان ریاضی که قابل فهم برای DBMS هست تبدیل می شود.

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

http://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/

فایل مربوط به Query Processing رو ببین، صفحه 2 و 3.

[pesar irooni]

این از نگاه من و شماست که بصورت برنامه نویسی داریم به این قضیه نگاه می کنیم. در DBMS ها اتفاق دیگری می افته. همونطوری که گفتم تمام این دستورات SQL به یه زبان ریاضی قابل فهم برای DBMS تبدیل می شه و اون زبان بهینه می شه و نهایتا اجرا می شه. در این صورت نوع نوشتن Query تفاوتی نمی کنه.
من بعد میدونم DBMS ها بیاند و تمام query plan ها رو بسازند تا ببینند کدوم بهتره و بعد یکیش رو انتخاب کنند. optimization در تئوری بسیار مطرحه ولی در عمل زیاد بها داده نمیشه چون برسی query plan ها بسیار وقت گیر است. هر چند در حد query های کوچیک این کار رو بکنه ولی برای query های پیچیده اینطور نیست.

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

[pesar irooni]

سلام دوست عزیز.
در مورد سوال اولتون کاملا حق با شماست. این سوء تفاهم از جانب من بود. عذرخواهی می کنم.
اما در مورد سوال دومتون که منبع معتبر خواستید. به این لینک یه نگاهی بندازید. فکر نمی کنم اسم مولف جایی برای شک در مورد اعتبار منبع باقی بگذاره.
ممنون
لینک خوبی بود
من کتابش رو داشتم اما چون عکس از صفحات کتاب بود خوندن و جستجوش سخت بود.
اما میشه بگی کدوم slide از بخش optimization گفته که in با join فرقی نداره. مخصوصا که گفته باشه مثلا فلان DBMS این کار رو میکنه.

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

RDBMS ها تمامه موفقیت و قدرتشون اینه که مبتنی بر ریاضیات هستند و تمامه query ها از نظاره ریاضی قابل اثبات است. پس اگر قرار باشه کاری به منطق نداشته باشه که ماهیتش زیره سوال میره.
من نمیدونم یعنی منظوره شما اینه که دیگه از ضرب دکارتی استفاده نمیشه.این امکان نداره. join هم نوعی ضرب دکارتیه. بلاخره ضرب دکارتی یک جوری باید اعمال بشه که ساده ترین حالت همون ضرب هر سطر تو کل جدول دومه
ویرایش: منظورم من از "منطق کوئری" در واقع "ترتیب منطقی اجرای کوئری" بوده است.

ببینید مواد یک عبارت SELECT به یک ترتیب منطقی اجرا می شوند:


(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>


ولی SQL Server مختار است که ترتیب اجرای این مواد را طبق صلاح دید خودش جابجا کند.
البته در مثال فوق ماده ی TOP در نظر گرفته شده است که جزئی از استاندارد زبان SQL محسوب نمی شود.
مثلا در JOIN ابتدا ضرب دکاری بین دو جدول سمت چپ و راست صورت گرفته سپس سطرها بر اساس شرط اتصال فیلتر می شوند (که اگر JOIN از نوع Outer باشد سطرهای خارجی هم اضافه شده) و سپس نتیجه ی مرحله قبل که داخل یک جدول مجازی ریخته شده است در ماده ی WHERE فیلتر می شود. ولی ممکن است SQL Server تشخیص دهد ابتدا سطرها را طبق شرط ماده ی WHERE فیلتر کند سپس ضرب دکاری انجام دهد (برای اینکه تعداد سطرهای کمتری با همدیگر ضرب شوند)

من تماما راجب SQL Server دارم گفتگو می کنم. برای اطلاعات کامل راجب بحث Logical/Physical Query Processing می توانید به این کتاب مراجعه کنید:
Microsoft Press: Microsoft SQL Server 2005/2008 T-SQL : Querying By Itzik Ben Gan

(نسخه ی PDF اش از سایت Rapid Share بسادگی پیدا میشه)

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

من بعد میدونم DBMS ها بیاند و تمام query plan ها رو بسازند تا ببینند کدوم بهتره و بعد یکیش رو انتخاب کنند. optimization در تئوری بسیار مطرحه ولی در عمل زیاد بها داده نمیشه چون برسی query plan ها بسیار وقت گیر است. هر چند در حد query های کوچیک این کار رو بکنه ولی برای query های پیچیده اینطور نیست.

نمیشه به این سادگی در این مورد بحث کرد. یک کتاب به شما معرفی می کنم که ویژه ی Execution Plan نوشته شده است. از سایت simple-talk می تونید دانلودش کنید (البته جستجو کنید فایل pdf اش پیدا میشه، این کتاب رایگان است)
SQL Server Execution Plan Grant Fritchy

این مطالب یک بخشی از این کتاب هست:


The Query Optimizer
The query optimizer is essentially a piece of software that "models" the
way in which the database relational engine works. Using the query
processor tree and the statistics it has about the data, and applying the
model, it works out what it thinks will be the optimal way to execute the
query – that is, it generates an execution plan.
In other words, the optimizer figures out how best to implement the
request represented by the T-SQL query you submitted. It decides if the
data can be accessed through indexes, what types of joins to use and
much more. The decisions made by the optimizer are based on what it
calculates to be the cost of a given execution plan, in terms of the
required CPU processing and I/O, and how fast it will execute. Hence,
this is known as a cost-based plan.
The optimizer will generate and evaluate many plans (unless there is
already a cached plan) and, generally speaking, will choose the lowestcost
plan i.e. the plan it thinks will execute the query as fast as possible
and use the least amount of resources, CPU and I/O. The calculation
of the execution speed is the most important calculation and the
optimizer will use a process that is more CPU-intensive if it will return
results that much faster. Sometimes, the optimizer will select a less
efficient plan if it thinks it will take more time to evaluate many plans
than to run a less efficient plan.

[ASKaffash]

http://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/

سلام
این لینک را نمی تونم بیارم مشکل کجاست ؟

[mohsensaghafi]

ممنون
لینک خوبی بود
من کتابش رو داشتم اما چون عکس از صفحات کتاب بود خوندن و جستجوش سخت بود.
اما میشه بگی کدوم slide از بخش optimization گفته که in با join فرقی نداره. مخصوصا که گفته باشه مثلا فلان DBMS این کار رو میکنه.

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

[mohsensaghafi]

http://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/

سلام
این لینک را نمی تونم بیارم مشکل کجاست ؟

دوست عزیز، من الان چکش کردم مشکلی نداشت. یکی دیگر از دوستان هم انگار دیده بودن مطلب رو، یعنی از سبک و صیاق نوشتشون اینطوری بر می اومد.

[pesar irooni]

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

من بعد میدونم DBMS ها بیاند و تمام query plan ها رو بسازند تا ببینند کدوم بهتره و بعد یکیش رو انتخاب کننداما بحث اصلی من اینه که optimization در حد همه query plan ها اینقدر cost اش بالاست که عملا هیچ DBMS تجاری این کار رو نمیکنه و همه تا یک حدی بیشتر انجام نمیدند و من هم همین رو گفتم

ین مطالب یک بخشی از این کتاب هستاین مطلب هم حرف من رو نقض نکرده
من بیشتر از این جملتون ترسیدم

مباحثی که راجب ضرب دکارتی و اینگونه مسائل شده است تنها در بحث منطقی می گنجند
و گرنه

مختار است که ترتیب اجرای این مواد را طبق صلاح دید خودش جابجا کندهیچ مشکلی نداره


با تشکر

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

اما بحث اصلی من اینه که optimization در حد همه query plan ها اینقدر cost اش بالاست که عملا هیچ DBMS تجاری این کار رو نمیکنه و همه تا یک حدی بیشتر انجام نمیدند و من هم همین رو گفتم

می توانید یک مستند معتبر از این مطلب به ما ارائه بدین؟ یا اینکه این یک نظر شخصی بدون دانش در این مورد است؟

اون مطلب حرف شما را نقض نکرده ولی بحثی راجب اینکه "همه ی query ها بهینه نمی شوند/ تنها برخی از query ها بهینه می شوند" هم صورت نگرفته.

[pesar irooni]

یا اینکه این یک نظر شخصی بدون دانش در این مورد است؟
این مطلب رو من از خودم نمیگم
استاد دوره لیسانسم که تخصصش پایگاه داده ها بود و دانشجوی دکتری دانشگاه تهران بود خیلی رو این مطلب تاکید داشت

همه ی query ها بهینه نمی شوند/ تنها برخی از query ها بهینه می شوند
من گفتم بازم تکرار میکنم که DBMS ها این کار رو نمی کنند وگرنه که ماهیتا همه query ها قابل بهینه شدن هستند ولی کیه که (DBMS منظورمه) بره واقعا به ازای هر query که کاربر میده تمام query plan هاش رو در بیاره با هم مقایسه کنه و بهترینش رو اجرا کنه

می توانید یک مستند معتبر از این مطلب به ما ارائه بدین؟
الان که منبعی ندارم و وقت هم متاسفانه ندارم که برم سرچ کنم. ولی اگه پیدا کردم چشم، حتما
ضمنا سعی میکنم از دانشجوهای دکتری (با تخصص پایگاه داده) دانشگاه خودمون هم بپرسم، اگه یادم نره، (چون این ترم فقط یه جلسه دیگه دارم)

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

RDBMS ها با هم فرق می کنند دارای تکنولوژی های مختلفی هستند، هر شرکتی (مثلا Oracle و یا Microsoft) از یک شیوه ی خاصی در محصولاتشان استفاده می کنند.
جزئیات عجیب و پیچیده ای دارن این DBMS ها بطور مثال بحثی وجود داره به نام Cost-based evaluation که خیلی جالبه و شیرینه، در مورد Predicate و Logic است. (یعنی SQL Server می تونه ابتدا عبارتی را اجرا کند که کمترین هزینه را بین عبارات موجود دیگر دارد بدون توجه به ترتیب عبارات)

موفق باشید!

ویرایش:
بهتر است از کلمه ی "سنجیدن" بجای "اجرا کردن" در جمله ی داخل پرانتز استفاده شود.

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

راستی این مقاله مربوط به بحث evaluation هست، اگر علاقه دارین یک نگاهی بهش بندازین:
http://pratchev.blogspot.com/2008/10/predicates-in-sql.html

[mohsensaghafi]

دوست عزیز جناب آقای pesar irooni ، سلام

ما اگه بی ادبی نباشه این اصلا منطقی نیست که این فرض رو کنیم
بعد میدونم شما یه مرجع معتبر برای استنتاجی که کردید پیدا کنید.
اما تا حدودی با شما ها موافقم، یعنی در حده یه query خیلی ساده شاید فرقی نداشته باشه اما برای query پیچیده که بسیار استفاده میشه نمیشه این نتیجه رو انجام داد و هر جور که خواستیم query بنویسیم
باید دید تعریف و درک و دریافت شما از منطق چی باشه.
من نمی دونم منطور شما از فرقی نداشته باشه چیه؟! اجرای کوئری یا بهینه سازی اون. اگر منظورتون اجرا باشه که پس این وسط بهینه سازی یعنی کشک ولی اما اگر نطرتون بهینه سازی باشه باید بگم که واقعاً اون بهینه سازیی که بخواد مسائل کوچک رو بهینه کنه و مسائل پیچیده رو کاری نداشته باشه طبق منطق من اصلا بهینه سازی نیست. چون مسائل کوچک که به اون صورت بهینه سازی لازم ندازند، مشکل با مسائل پیچیدست. بر اساس هر کدوم از نظرات بالا، بهینه سازی یعنی هیچ.

اما بحث اصلی من اینه که optimization در حد همه query plan ها اینقدر cost اش بالاست که عملا هیچ DBMS تجاری این کار رو نمیکنه و همه تا یک حدی بیشتر انجام نمیدند و من هم همین رو گفتم
اما در مورد اینکه هیچ DBMS تجاری این کار رو نمی کنه، می شه دلیل بیارید. لطفا اینکه استاد لیسانسمون گفته رو نگید چون من هم استاد فوق لیسانسمون گفت که همشون بهینه می شن.؟!!!
موفق و در پناه حی توانا
یا علی!

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

ظاهرا اینگار دوستان خیلی علاقه به ادامه ی این بحث دارند.


من گفتم بازم تکرار میکنم که DBMS ها این کار رو نمی کنند وگرنه که ماهیتا همه query ها قابل
بهینه شدن هستند ولی کیه که (DBMS منظورمه) بره واقعا به ازای هر query که کاربر میده تمام query plan هاش رو در بیاره با هم مقایسه کنه و بهترینش رو اجرا کنه

بقول دوستمان نمیشه تنها به گفته ی استاد دانشگاه اکتفا کرد. نیاز به تحقیقات و مطالعات جزئی تر هست.
فرق زبان های relational/Set_Oriented با زبان های رویه ای این هست که در زبان نوع اول کاربران مشخص می کنند که چه چیزی باید انجام شود و نه این که چگونه باید انجام شود به بیان دیگر، کاربران آنچه را که می خواهند اعلام می کنند بدون این که مشخص کنند چگونه باید فرایند آن انجام شود. فرایند "پیمایش" روی داده های ذخیره شده انجام می شود تا به درخواست های کاربر توسط سیستم به طور اتوماتیک پاسخ داده شود و نه به صورت دستی توسط کاربر.
تصمیم گیری در مورد چگونگی انجام پیمایش خودکار که در بالا مطرح شد بر عهده ی بخش مهمی از DBMS به نام بهینه ساز است. به عبارت دیگر، برای هر درخواست رابطه ای از کاربر، این وظیفه ی بهینه ساز است که راه موثر و کارائی را برای پیاده سازی آن درخواست انتخاب کند. (بخشی از مطالب کتاب آقای C.J. Date)

اگر جملات فوق را با دقت بخوانید متوجه می شوید که هر درخواستی بالاخره باید یک Execution Plan برایش انتخاب شود و طبق آن منابع سخت افزاری روی داده ها پیمایش کنند. حالا شاید ممکن باشد برای هر کوئری تمام Plan ها تولید و سنجیده نشود ولی بالاخره یک Plan تولید خواهد شد.

برای رسیدن به جواب دقیق باید دید که Optimizer در DBMS ها چه رفتارهای متفاوتی در برابر Query های ساده (Simple) و پیچیده (Complex) دارند. آیا بسته به پیچیدگی کوئری Plan های کمتری تولید و سنجیده می شوند یا نه؟

باید در مورد این مسائل مطالعه و تحقیق کرد تا بشه به جواب دقیق رسید.

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

(من ممکن اشتباه بکنم چون استاد دانشگاه نیستم حتی استاد دانشگاه هم ممکنه اشتباه کنه مخصوصا اونم از نوع ایرانیش (: )
تصویر زیر که از کتاب الکترونیکی آقای Silberzchats هست دقیقا حرف مرا در مورد اینکه در بعد فیزیکی ممکنه عملیات منطقی جایشان تعویض شود را تصدیق داده است! به عبارت دیگر قبل از ضرب دکارتی که اولین فاز در اجرای منطقی یک کوئری است، می تواند فیلتر سطرها اتفاق افتد! البته این به بحث اصلی ما ارتباط چندانی نداره ولی چون جالب دیدمش پست کردم.

(Cartesian Product ضرب دکارتی)

تصویر کاملا گویا هست. اگر ابهامی وجود داره یا من اشتباه می کنم حتما اعلام کنید.
http://www.barnamenevis.org/forum/attachment.php?attachmentid=50309&stc=1&d=1275549769



همچنین طبق گفته ی آقای Silberzchats در مواقعی که Query پیچیده است Optimization معنا و مفهوم پیدا می کنه. البته برداشتی که من از جمله اش کردم، شاید منظورش این هست که هرچی کوئری پیچیده تر باشه استراتژی های بیشتری برای پردازش کوئری وجود دارد...
از طرفی یکی از وظایف Optimization انتخاب Index های مطلوب هست تا داده ها را از طریق آن پیمایش کند. حالا فرقی هم نمی کنه کوئری چقدر پیچیدگی داشته باشه ولی این امر بهینه سازی چیز قطعی هست! نمیشه حتی برای کوئری های بسیار ساده هم از این صرف نظر کرد.
فرض کنید یک کوئری نوشتیم که تنها یک شرط در ماده ی WHERE دارد اگر Optimizer از شاخص مطلوب داده ها را استخراج نکند ممکن است داده های کل جدول Scan شود این به معنای فاجعه هست! (در مجموعه داده های زیاد منظورم هست مثلا میلیونی)
در این مورد می تونم وارد جزئیات بیشتری شوم در صورتی که احتیاج به آن وجود داشته باشد.




Query optimization

is the process of selecting the most efficient query-evaluation


plan from among the many strategies usually possible for processing a given query,


especially if the query is complex.

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

من گفتم بازم تکرار میکنم که DBMS ها این کار رو نمی کنند وگرنه که ماهیتا همه query ها قابل بهینه شدن هستند ولی کیه که (DBMS منظورمه) بره واقعا به ازای هر query که کاربر میده تمام query plan هاش رو در بیاره با هم مقایسه کنه و بهترینش رو اجرا کنه

راستی اینو یادم رفت بگم. سنجیدن اینکه کدام plan هزینه ی کمتری داره از روی یک سری آمار و ارقام (که قبلا ذخیره شدن) به شکل تخمینی بدست می آید پس قرار نیست یک محاسبه ی پیچیده اتفاق افتد که اینقدر هزینه داشته باشه که RDBMS از آن صرف نظر کنه! (در این مورد هم منابع در دسترس دارم تا توضیح مفصل بدم اگر تمایل دارین یا برایتان شفاف نیست میتونم کامل با جزئیات شرح بدم)

اینم تصدیق من از طرف Silber عزیز :)




In Section 14.2 we describe how to estimate statistics of the results of each operation
in a query plan. Using these statistics with the cost formulae in Chapter 13 allows
us to estimate the costs of individual operation. The individual costs are combined to
determine the estimated cost of evaluating a given relational-algebra expression, as
outlined earlier in Section 13.7.

[pesar irooni]

لطفا اینکه استاد لیسانسمون گفته رو نگید چون من هم استاد فوق لیسانسمون گفت که همشون بهینه می شن.؟!!!
من هم سعی میکنم از استاد فوق لیسانسم (دکتر حق جو) بپرسم و اگه حرفای من رو تایید کنه یر به یر میشه با این تفاوت که استاد لیسانس شما چیزی نگفته (شاید هم حرف من رو رد کنه)

اگر جملات فوق را با دقت بخوانید متوجه می شوید که هر درخواستی بالاخره باید یک Execution Plan برایش انتخاب شود و طبق آن منابع سخت افزاری روی داده ها پیمایش کنند. حالا شاید ممکن باشد برای هر کوئری تمام Plan ها تولید و سنجیده نشود ولی بالاخره یک Plan تولید خواهد شد.
خوب این یعنی اینکه یک سری از plan ها سنجیده نمیشند. و من هم میگم شاید بهترین plan همونی بوده که سنجیده نشده.

صویر زیر که از کتاب الکترونیکی آقای Silberzchats هست
من کتابهای silber و date رو خوندم و تا جایی که یادمه با حرفای من تناقض نداشت.
در مورد اینکه اول باید select جبر رابطه ای کرد و بعد join زد اصلا بحثی نیست و من همیشه این کار رو میکنم چون دلیلش رو از رو همین کتابا خوندم. بحثم اینه که ما یه query داریم اگه اجرا بشه مثلا ۵ ثانیه طول میکشه. حالا optimizer میاد همه ی plan هاش رو در بیاره و تخمین بزنه (با فرضی اینکه قبلا یه سری آمار هایی داره و مشابهه این plan رو قبلا دیده) و بعد یکی رو انتخاب کنه و در نهایت اجرا کنه، آیا این بیشتر طول نمیکشه؟ میدونی برای هر query چند حالت ممکن وجود داره و چند تا از این درخت ها میشه کشید.
حالا یه کم این مطالب رو ببینید (شایدم قانع نشید)

Improving and fine tuning query optimization is a
never ending task
Some queries are inherently more difficult than
others for a query optimizer to generate efficient
plans.

http://downloads.ingres.com/online/media/pdf/Ingres-DBMS/The_Ingres_Optimizer.pdf
slide 1 and 27
اگه وقت بشه بعد از امتحانها یه بحث مفصل با مراجع داشته باشیم

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

بحثم اینه که ما یه query داریم اگه اجرا بشه مثلا ۵ ثانیه طول میکشه. حالا optimizer میاد همه ی plan هاش رو در بیاره و تخمین بزنه (با فرضی اینکه قبلا یه سری آمار هایی داره و مشابهه این plan رو قبلا دیده) و بعد یکی رو انتخاب کنه و در نهایت اجرا کنه، آیا این بیشتر طول نمیکشه؟
دوست عزیز،
ببینید، این جمله که هر کوئری اول و آخر توسط Optimizer نقشش تولید میشه و این نقشه به Storage Engine برای اجرا ارسال میشه را که قبول دارین؟
پس اون کوئری که 5 ثانیه زمانش طول کشیده هم Optimize شده است.
فرض کنید اجرای کوئری با یک پلن نا مناسب (مثلا شاخص مطلوب missing/cross شده) 5 ثانیه طول کشیده و حالا Optimizer که از روی یکسری تخمین ها و جداول و داده های آماری توانسته پلنی تهیه کنه که شاخص مطلبوب در آن استفاده میشه و این پروسه با فرض اینکه 2000 میلی ثانیه زمان مصرف کرده کوئری که قبلا در 5 ثانیه اجرا میشده حالا در مدت 200 میلی ثانیه (یعنی قبل از اینکه پلک بعد از بسته شدن باز بشه کوئری اجرا شده!) اجرا میشه. و مجموعه Elapsted Time , CPU Time برابر با 2200 میلی ثانیه است. این عدد بازم از عدد 5000 میلی ثانیه خیلی کمتر است.

لطفا این سری اگر خواستین بحث را ادامه بدین با مطالب علمی جلو بیایین.
با تشکر

[pesar irooni]

با فرض اینکه 2000 میلی ثانیهچراااااااااااااااا؟
من فقط فرض کردم 5 ثانیه، شما هم فرض دارید میکنید. حالا چرا فرض شما کمتر از منه؟؟؟
حرفم هم اینه :
"Optimizer که از روی یکسری تخمین ها و جداول و داده های آماری توانسته پلنی تهیه کنه (از میون همه plan های ممکن) که شاخص مطلوب در آن استفاده میشه و این پروسه" از اجرای کوئری بدون هیچ optimization ای طولانی تر خواهد بود.

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

چراااااااااااااااا؟
من فقط فرض کردم 5 ثانیه، شما هم فرض دارید میکنید. حالا چرا فرض شما کمتر از منه؟؟؟
باید درست مطالبم را نخونده باشین!
گفته پروسه ی بهینه سازی 2 ثانیه طول میکشه. و با پلنی که بهینه ساز در این دو ثانیه تولید کرده کوئری در 200 میلی ثانیه اجرا میشه. پس مجموعه زمانی که برای بهینه سازی و اجرای کوئری صرف شده مجموعه ی 2200 میلی ثانیه است که از 5 ثانیه ای که کوئری بدون هیچ بهینه سازی اجرا شده کمتر است.

"Optimizer که از روی یکسری تخمین ها و جداول و داده های آماری توانسته پلنی تهیه کنه (از میون همه plan های ممکن) که شاخص مطلوب در آن استفاده میشه و این پروسه" از اجرای کوئری بدون هیچ optimization ای طولانی تر خواهد بود.
Documents Please