یکی از مواردی که بصورت مکرر در SQL بوجود می آید، زمانی‌ست که می خواهیم یک تعداد رکورد در یک جدول را در جدول دیگری جستجو کنیم، خوب برای این مهم می توان از سه عملگر متفاوت استفاده کرد، اما اینکه کدام را استفاده کنیم که کمترین هزینه را برای DBMS داشته باشد، بسیار مورد اهمیت بوده و از مسائل مربوط به حوزه Performance Tuning در msSQL است.
با یک مثال بحث را شروع می کنیم، فرض کنید جدولی داریم حاوی اطلاعات دانشجو با 1000 رکورد (tb_student) و در کنار آن جدولی داریم شامل شماره دانشجویی دانشجویان برتر (tb_Top)در هر سال، حال از ما خواسته اند، اطلاعات دانشجویانی رویت شود بصورت کامل که جزو برتر های باشند، به سه روش زیر می توان اقدام به نوشتن query مربوطه شود:
Select * from tb_student where id in (select id from tb_top)
Select * from tb_student where id exists( select id fro, tb_top)
Select * from tb_studnet s left join tb_top t on s.id = t.id where t.id is not null
شما تصور می‌کنید کدام روش بهر است؟ پاسخ به این سوال نیازمند بررسی‌های زیاد هست، اما بسته به اینکه در چه سالی و در چه ورژن SQL در حال بررسی هستیم ممکن است شرایط متفاوت باشد، لذا ما بستر را mssqlserver13 یعنی نسخه 2017 تصور کرده و با این فرضیه اقدام به بررسی میکنیم، درپاسخ به سوال فوق در دید اول اکثرا مورد اول را اولویت آخر خود قرار داده و مورد سوم را به دوم ترجیح می‌دهند، اما قطعا بعد از بررسی‌هایی که در ادامه خواهیم داشت تصورات شما متفاوت خواهد شد.
برای بررسی بهتر اینکه استفاده از کدام عملکرد بهتر است، سه جدول می‌سازیم با تعدادی رکورد و تست‌های خود را در چهار حالت پایه آغاز می کنیم، منظور از حالات پایه استفاده از IN و Exists و Join است، بعدا در مورد left join نیز بحث خواهیم کرد.
جداول ما به ترتیب بصورت زیر خواهد بود:
- جدول tb_Outer که قرار است رکوردهای آن بازیابی شود حاوی 10000 رکورد
- جدول tb_Inner که قرار است دنیال رکوردهای مورد نظر در آن بگردیم حاوی 10000 رکورد
- جدول tb_SmallInner که یک جدول کوچک بوده مثلا حاوی 100 رکورد از جدول InnerTable بر اساس فیلد مرتب
- جدول tb_SmallInner که یک جدول کوچک بوده مثلا حاوی 100 رکورد از جدول InnerTableبر اساس فیلد نامرتب
حال علاوه بر PK برای این دو جدول دو ستون دیگر با داده های تصادفی در نظر گرفته که یکی index بوده و دیگری index نیست و منظور از ایندکس مشخصا noncluster می‌باشد.
مشاهده دوره‌های آموزش امنیت شبکه و آموزش برنامه نویسی از سایت کندو.
ساخت جداول
پرکردن جداول
CREATE TABLE dbo.tb_Outer (
Id INT NOT NULL PRIMARY KEY
,IndexColumn INT NOT NULL
,NonIndexColumn INT NOT NULL
)
CREATE TABLE dbo.tb_Inner (
Id INT NOT NULL PRIMARY KEY
,IndexColumn INT NOT NULL
,NonIndexColumn INT NOT NULL
)
CREATE TABLE dbo.tb_SmallInner (
Id INT NOT NULL PRIMARY KEY
,IndexColumn INT NOT NULL
,NonIndexColumn INT NOT NULL
)
CREATE INDEX IX_tb_Outer ON dbo.tb_Outer (IndexColumn)
CREATE INDEX IX_tb_Inner ON dbo.tb_Inner (IndexColumn)
CREATE INDEX IX_tb_SmallInner ON dbo.tb_SmallInner (IndexColumn)
پر کردن جداول
BEGIN TRANSACTION
DECLARE @count INT = 1
WHILE @count <= 100000
BEGIN
INSERT
INTO dbo.tb_Inner
VALUES (@count, RAND() * 100000000, RAND() * 100000000)
INSERT
INTO dbo.tb_Outer
VALUES (@count, RAND() * 100000000, RAND() * 100000000)
SET @count = @count + 1
END
INSERT
INTO dbo.tb_SmallInner
SELECT TOP 100
ROW_NUMBER() OVER (ORDER BY Id DESC)
,IndexColumn
,NonIndexColumn
FROM dbo.tb_Outer
ORDER
BY Id DESC
Commit
در ادامه اقدام به برسی چهار حالت را خواهیم کرد:
- دو جدول اول یعنی جداول بزرگ را با در نظر گرفتن ستون های index شده برای استفاده از عملگر های مورد بحث انتخاب میکنیم.
SELECT IndexColumn
FROM dbo.tb_Outer
WHERE IndexColumn IN
(
SELECT IndexColumn
FROM dbo.tb_Inner
)
چون هر دو ستون طرفین دستور IN دارای Index هستند Query Optimizer از عملگر Stream Aggregate برای حذف مقادیر تکراری جدول InnerTable استفاده می کند. چون ورودی این عملگر یک ستون Index دار یا مرتب شده است اینکار خیلی سریع و با هزینه کمی انجام میشود.
اکنون ما دو مجموعه رکورد مرتب شده از جدول های بیرونی و داخلی داریم که QueryOptimizer از عملگر merge join برای مقایسه این دو استفاده کرده است merge join همانند یک متغیر cursor عمل می‌کند که مقدار اولیه این متغیر کوچک‌ترین مقدار از هردو ستون قرار دارد و باهم join می‌شوند، است.
در آخر merge join سپس هردو مقدار از دو مجموعه که باهم برابر بودند را برمی‌گرداند و سپس مقدار متغیر cursor را افزایش می‌دهد و مقایسه بعدی را انجام می‌دهد و همین روند را برای مقدار بعدی انجام می‌دهد تا همه مقادیر از دو ستون باهم مقایسه شوند.
SELECT IndexColumn
FROM tb_Outer
WHERE EXISTS (
SELECT 1
FROM tb_Inner
WHERE tb_Outer.IndexColumn= tb_Inner.IndexColumn
همانطور که مشخص است Execution Plane هر دو کویری یکسان می باشد یعنی بر خلاف تصور اولیه در SQL2017 کارایی exists و in برای جداول بزرگ اما جستجو بر اساس index یکسان است.
SELECT tb_Outer.IndexColumn
FROM tb_Outer
JOIN (
SELECT DISTINCT IndexColumn
FROM tb_Inner
) tb_Inner
ON tb_Outer.IndexColumn = tb_Inner.IndexColumn
و باز هم همان Execution plane !!!
نتیجه حالت اول: برای جداول بزرگی که جستجو بر اساس فیلد ایندکس است، هر سه عملگر فوق از یک plane استفاده می کنند
- دو جدول اول یعنی جداول بزرگ را با در نظر گرفتن ستون های غیر index شده برای استفاده از عملگر های مورد بحث انتخاب میکنیم.
SELECT NonIndexColumn
FROM tb_Outer
WHERE NonIndexColumn IN
(
SELECT NonIndexColumn
FROM tb_Inner
)
همانطور که قابل مشاهده است در این حالت نیز از روش hash match استفاده شده است، یعنی مرتب بودن و یا مرتب نبودن در عملگر in تاثیر گذار نیست.
SELECT tb_Outer.NonIndexColumn
FROM tb_Outer
JOIN (
SELECT DISTINCT NonIndexColumn
FROM tb_Inner
) tb_Inner
ON tb_Outer.NonIndexColumn = tb_Inner.NonIndexColumn
همانطور که در plan مشخص است از عملگر hash match استفاده‌شده است.یک جدول hash برای جدول بیرونی ایجادشده است و بعد به ازای هرکدام از مقادیر جدول داخلی مقایسه با جدول hash صورت می‌گیرد.در آخر چون یک left semi join صورت می‌گیرد اگر مقدار در جدول hash پیدا شود، به نتیجه اضافه میشود و بلافاصله از جدول hash حذف میشود بنابراین این جدول به تدریج که کوئری اجرا میشود کوچک‌تر میشود.
SELECT NonIndexColumn
FROM tb_Outer
WHERE EXISTS (
SELECT 1
FROM tb_Inner
WHERE tb_Outer.NonIndexColumn = tb_Inner.NonIndexColumn
SELECT tb_Outer.NonIndexColumn
FROM tb_Outer
JOIN (
SELECT DISTINCT NonIndexColumn
FROM tb_SmallInner
) tb_SmallInner
ON tb_Outer.NonIndexColumn = tb_SmallInner.NonIndexColumn
این دستور کارایی کمتری دارند چون از عملگر distinct sort برای خارج کردن مقادیر تکراری استفاده می کند و چون این عملگر یک جدول hash ایجاد نمی کند بایستی دوباره جدول hash ساخته شود.
SELECT NonIndexColumn
FROM tb_Outer
WHERE EXISTS (
SELECT 1
FROM tb_SmallInner
WHERE tb_Outer.NonIndexColumn = tb_SmallInner.NonIndexColumn
)
نتیجه حالت سوم: در این حالت In و Exists مشابه هم بوده و از join سبک تر می باشد.
- یک جدول بزرگ و یک جدول کوچک را در نظر گرفته بر اساس جستجو روی فیلد مرتب اقدام به بررسی خوایم کرد.
SELECT IndexColumn
FROM dbo.tb_Outer
WHERE IndexColumn IN
(
SELECT IndexColumn
FROM dbo.tb_SmallInner
)
روش merge join یک روش بی ارزش است چون دراینجا بایستی کل ستون Index برای مقایسه پیماش شود. به جای این روش ابتدا مقادیر تکراری از جدول داخلی با روش stream aggregatحذف می شود سپس با استفاده ایندکسی که روی جدول بیرونی قرار دارد و با استفاده از روش nested loopهرکدام از مقادیر جدول بیرونی را با ۱۰۰ رکورد جدول داخلی مقایسه می کند.
SELECT NonIndexColumn
FROM tb_Outer
WHERE EXISTS (
SELECT 1
FROM tb_SmallInner
WHERE tb_Outer.IndexColumn = tb_SmallInner.IndexColumn
)
SELECT tb_Outer.NonIndexColumn
FROM tb_Outer
JOIN (
SELECT DISTINCT IndexColumn
FROM tb_SmallInner
) tb_SmallInner
ON tb_Outer.IndexColumn = tb_SmallInner.IndexColumn
نتیجه حالت چهارم : در این حالت join و exists مشابه هم بوده ولی in یک lookup کمتر داشته و بهتر است.
- در حالت آخر به جای استفاده از شرط مساوی از عملگر های ریاضی استفاده خوایم کرد.
SELECT tb_Outer.NonIndexColumn
FROM tb_Outer
WHERE EXISTS (
SELECT 1
FROM tb_SmallInner
WHERE tb_SmallInner.NonIndexColumn – tb_Outer.NonIndexColumn = 0
)
SELECT tb_Outer.NonIndexColumn
FROM tb_Outer
JOIN (
SELECT DISTINCT NonIndexColumn
FROM tb_SmallInner
) tb_SmallInner
ON tb_SmallInner.NonIndexColumn – tb_Outer.NonIndexColumn = 0
نتیجه: در عبارت محاسباتی Join بهتر است
