مقایسه سرعت اجرای سه دستور in ،Exists و join [بخش دوم]

مقدمه مقایسه سرعت اجرای سه دستور in ،Exists و join

همانطور که در قسمت اول این مقاله مشاهده کردید ما دستورات Join ، in، Exist را در SQL Server مقایسه کردیم. حال در ادامه می خواهیم سایر مباحث را برای شما بیشتر باز کنیم.

اما plan برای دستور join یک مقدار متفاوت است

SELECT OuterTable.NonIndexColumn FROM OuterTable JOIN ( SELECT DISTINCT NonIndexColumn FROM InnerTable ) InnerTable ON OuterTable.NonIndexColumn = InnerTable.NonIndexColumn

در این حالت sql server ازروش left semi join نمی تواند استفاده کند.

در عوض ابتدا تمام مقادیر تکراری را از ستون سوم جدول داخلی با عملگر Hash Match (Aggregate) حذف می کند. که در نتیجه یک جدول hash که مقادیر یکتا دارند تشکیل می شود. سپس موتور sql به ازای هرکدام از مقادیر جدول خارجی در داخل این جدول hashجستجو می کند و در صورت برابر بودن شرط برابر آن مقدار را به نتیجه کوئری اضافه می کند.

به خاطر بعضی دلایل این دستور کمی کندتر از دو دستور قبل است و مشکل هست که علت آن را بتوان متوجه شد. اما یکی از دلایل بسیار محتمل این هست که موتور به ازای هر مقایسه یک جدول hash کوچکتر و جدید بسازد.

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

 SELECT IndexColumn FROM dbo.OuterTable WHERE IndexColumn IN ( SELECT IndexColumn FROM dbo.SmallInnerTable )

Planدر این قسمت متفاوت هست و روش merge join یک روش بی ارزش است چون دراینجا بایستی کل ستون Index برای مقایسه پیماش شود. به جای این روش ابتدا مقادیر تکراری از جدول داخلی با روش  stream aggregation حذف می شود سپس با استفاده ایندکسی که روی جدول بیرونی قرار دارد و با استفاده از روش  nested loop هرکدام از مقادیر جدول بیرونی را با ۱۰۰ رکورد جدول داخلی مقایسه می کند. 

Plan  برای عملگر های exists و join یکی است.

اما اگر روی ستون های بدون ایندکسی جستجو کنیم

Plan بسیار شبیه به plan برای جدول داخلی بزرگ هست با این تفاوت که به جای انکه جدول hash برای جدول بیرونی ساخته شود برای جدول داخلی ساخته شده است. جدول hash حین اجرا کوچکتر نمی شود اما مقادیر تکراری آن هنگام ایجاد حذف شده است.
It’s in fact a Hash Match (Aggregate) combined with Hash Match (Inner Join), but in more efficient way which reuses the hash table.

دستور join

SELECT OuterTable.NonIndexColumn FROM OuterTable JOIN ( SELECT DISTINCT NonIndexColumn FROM SmallInnerTable ) SmallInnerTable ON OuterTable.NonIndexColumn = SmallInnerTable.NonIndexColumn

این دستور کارایی کمتری دارند چون از عملگر distinct sort برای خارج کردن مقادیر تکراری استفاده می کند و چون این عملگر یک جدول hash ایجاد نمی کند بایستی دوباره جدول hash ساخته شود.

نتیجه گیری مقایسه سرعت اجرای سه دستور in ،Exists و join

• در این مقاله دیدیم که برخلاف تصور بسیاری، عملگر های in و exist کارایی کمتر از join در sql server ندارند.
• در حقیقت دستور join وقتی که روی جداول بدون index اجرا میشود کارایی کمتری دارد. چون روش های semi join امکان انجام aggrigation و تطبیق را در یک گام به ما می دهند اما روش join نیاز هست که این دو عمل را در دو گام انجام دهد.

در آخر می خواهم یک حرف رایج دیگر رو نقد کنم

“بسیاری بر این تصورند که دستور exists کارایی بیشتری نسبت به in دارد چون exists تنها یک ردیف بر می گرداند. “
این تصور غلط هست همانطور که دیدیم sql server یک plan دقایقا یکسان برای هردو دستور ایجاد میکند.
دستور exists انعطاف بیشتری نسبت به in دارد. هر دستور In را می توان با یک دستور exists نوشت ( با نوشتن شرط برابری دو ستون از جدول بیرونی و داخلی در قسمت where) اما بلعکس آن امکان ندارد.
در حقیقت می توان گفت که یک کوئری که با دستور exists بدون توجه نوشته شده است می تواند منجر به این شود که کوئری به ازای هر ردیف دوباره ارزیابی شود. این دلیلی هست که بسیاری از برنامه نویس ها نسبت به استفاده از دستور in بی میل هستند و از قضا دستور exist هست که نسبت به ارزیابی دوباره مستعد هست.

برای مثال دستور exists زیر بسیار نا کار امد هست

چون شرطی که در این دستور قرار دارد Sargable یا Index پذیر نیست و حتی نمیتوان برای آن توابع درهم ساز را برای ایجاد جدول hash برای آن اعمال کرد، در نهایت این دستور مجبور هست که از روش nested loop برای پیمایش تمام جدول استفاده کند که حدود ۱.۵ ثانیه طول می کشد. 

منبع

https://explainextended.com/2009/06/16/in-vs-join-vs-exists