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

مقدمه مقایسه سرعت اجرای سه دستور 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

دو جدول اول شامل صدهزار رکورد و جدول سوم صد رکورد اخر جدول 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 )

در قسمت دوم این مقاله موارد جذابتری را بررسی خواهیم کرد.