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

در این مقاله می خواهیم سه دستور in، exists و join را با هم مقایسه کنیم و ببینیم آیا تفاوتی بین سرعت اجرای این سه دستور هست یا خیر پس این مقاله را با دقت بخوانید. برای اینکه آزمایش خودمون رو روی سه دستور انجام بدهیم سه جدول داریم.

 CREATE TABLE dbo.OuterTable ( Id INT NOT NULL PRIMARY KEY ,IndexColumn INT NOT NULL ,NonIndexColumn INT NOT NULL ) CREATE TABLE dbo.InnerTable ( Id INT NOT NULL PRIMARY KEY ,IndexColumn INT NOT NULL ,NonIndexColumn INT NOT NULL ) CREATE TABLE dbo.SmallInnerTable ( Id INT NOT NULL PRIMARY KEY ,IndexColumn INT NOT NULL ,NonIndexColumn INT NOT NULL ) CREATE INDEX IX_OuterTable ON dbo.OuterTable (IndexColumn) CREATE INDEX IX_InnerTable ON dbo.InnerTable (IndexColumn) CREATE INDEX IX_SmallInnerTable ON dbo.SmallInnerTable (IndexColumn) BEGIN TRANSACTION DECLARE @count INT = 1 WHILE @count <= 100000 BEGIN INSERT INTO dbo.InnerTable VALUES (@count, RAND() * 100000000, RAND() * 100000000) INSERT INTO dbo.OuterTable VALUES (@count, RAND() * 100000000, RAND() * 100000000) SET @count = @count + 1 END INSERT INTO dbo.SmallInnerTable SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY Id DESC) ,IndexColumn ,NonIndexClumn FROM dbo.OuterTable ORDER BY Id DESC COMMIT GO

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

دو جدول اول شامل صدهزار رکورد و جدول سوم صد رکورد اخر جدول OuterTable است. دو جدول اول PK های یکسان دارند و داده های ستون اول و دوم آنها تصادفی ایجاد شده است. هر سه جدول OuterTable و InnerTable جدول SmallInnerTable روی ستون دوم Index دارد و روی ستون سوم Index ندارد. ابتدا سه دستور را روی ستونی که Index داریم بررسی می کنیم و بعد همان سه دستور را روی ستونی که Index نداریم.

دستور In روی ستونی که Index داریم

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

چون هر دو ستون طرفین دستور IN دارای Index هستند Query Optimizer از عملگر Stream Aggregate برای حذف مقادیر تکراری جدول InnerTable استفاده می کند. چون ورودی این عملگر یک ستون Index دار یا مرتب شده است اینکار خیلی سریع و با هزینه کمی انجام میشود. اکنون ما دو مجموعه رکورد مرتب شده از جدول های بیرونی و داخلی داریم که Query Optimizer از عملگر merge join برای مقایسه این دو استفاده کرده است.
merge join همانند یک متغیر cursor عمل می‌کند که مقدار اولیه این متغیر کوچک‌ترین مقدار از هردو ستون قرار دارد و باهم join می‌شوند، است.

merge join سپس هردو مقدار از دو مجموعه که باهم برابر بودند را برمی‌گرداند و سپس مقدار متغیر cursor را افزایش می‌دهد و مقایسه بعدی را انجام می‌دهد و همین روند را برای مقدار بعدی انجام می‌دهد تا همه مقادیر از دو ستون باهم مقایسه شوند. این روش برای دو مجموعه مرتب‌شده سریع‌ترین روش ممکن است.

اما همان کوئری با Join

 SELECT OuterTable.IndexColumn FROM OuterTable JOIN ( SELECT DISTINCT IndexColumn FROM InnerTable ) InnerTable ON OutsrTable.IndexColumn = InnerTable.IndexColumn

چه حدسی می‌زنید؟

دقیقاً همان plan و دقیقاً همان آمار را مشاهده می‌کنید.

اما دستور Exists

 SELECT IndexColumn FROM OuterTable WHERE EXISTS ( SELECT 1 FROM InnerTable WHERE OuterTable.IndexColumn= InnerTable.IndexColumn )

باور داشته باشید یا نداشته باشید بازهم نتیجه همانند دو دستور قبل است. برخلاف تصور بسیاری، کسانی که sql server را توسعه داده‌اند، با توجه plan و آماری که از اجرای این سه کوئری اجراشده است به ما خواسته‌اند بگویند که این سه دستور مشابه هم هستند و مثل همدیگر کارآمد هستند و رجحانی نسبت به یکدیگر ندارند.

اما اگر ما روی ستون‌هایی که Index ندارند join بزنیم چه اتفاقی می‌افتد

 SELECT NonIndexColumn FROM OuterTable WHERE NonIndexColumn IN ( SELECT NonIndexColumn FROM InnerTable )

همان‌طور که در plan مشخص است از عملگر hash match استفاده‌شده است. یک جدول hash برای جدول بیرونی ایجادشده است و بعد به ازای هرکدام از مقادیر جدول داخلی مقایسه با جدول hash صورت می‌گیرد. چون یک left semi join صورت می‌گیرد اگر مقدار در جدول hash پیدا شود، به نتیجه اضافه می‌شود و بلافاصله از جدول hash حذف می‌شود. بنابراین این جدول به‌تدریج که کوئری اجرا می‌شود کوچک‌تر می‌شود.

همان‌طور که پیش‌بینی می‌شود exists نیز همانند In عمل می‌کند

 SELECT NonIndexColumn FROM OuterTable WHERE EXISTS ( SELECT 1 FROM InnerTable WHERE OuterTable.NonIndexColumn = InnerTable.NonIndexColumn )

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

اما 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 زیر بسیار نا کار امد هست

سخن پایانی

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

ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.