مقایسه سه عملگر IN و Exists و Join

آنچه در این مطلب می‌خوانید:

یکی از مواردی که بصورت مکرر در 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  نیز بحث خواهیم کرد.

جداول ما به ترتیب بصورت زیر خواهد بود:

  1. جدول tb_Outer که قرار است رکورد‌های آن بازیابی شود حاوی 10000 رکورد
  2. جدول tb_Inner که قرار است دنیال رکوردهای مورد نظر در آن بگردیم حاوی 10000 رکورد
  3. جدول tb_SmallInner که یک جدول کوچک بوده مثلا حاوی 100 رکورد از جدول InnerTable بر اساس فیلد مرتب
  4. جدول 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

در ادامه اقدام به برسی چهار حالت را خواهیم کرد:

  1. دو جدول اول یعنی جداول بزرگ را با در نظر گرفتن ستون های index شده برای استفاده از عملگر های مورد بحث انتخاب می‌کنیم.

  SELECT  IndexColumn

FROM    dbo.tb_Outer

WHERE   IndexColumn IN

        (

        SELECT  IndexColumn

        FROM    dbo.tb_Inner

             )

مقایسه سه عملگر IN و Exists و Join 1

چون هر دو ستون طرفین دستور 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

مقایسه سه عملگر IN و Exists و Join 3

همانطور که مشخص است 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

مقایسه سه عملگر IN و Exists و Join 5

و باز هم همان Execution plane !!!

نتیجه حالت اول: برای جداول بزرگی که جستجو بر اساس فیلد ایندکس است، هر سه عملگر فوق از یک plane استفاده می کنند

  1. دو جدول اول یعنی جداول بزرگ را با در نظر گرفتن ستون های غیر index شده برای استفاده از عملگر های مورد بحث انتخاب میکنیم.

SELECT  NonIndexColumn

FROM    tb_Outer

WHERE   NonIndexColumn IN

        (

        SELECT  NonIndexColumn

        FROM    tb_Inner

        )

مقایسه سه عملگر IN و Exists و Join 7

همانطور که قابل مشاهده است در این حالت نیز از روش 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

مقایسه سه عملگر IN و Exists و Join 9

همان‌طور که در 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

مقایسه سه عملگر IN و Exists و Join 11

SELECT  tb_Outer.NonIndexColumn

FROM    tb_Outer

JOIN    (

        SELECT  DISTINCT NonIndexColumn

        FROM    tb_SmallInner

        ) tb_SmallInner

ON      tb_Outer.NonIndexColumn = tb_SmallInner.NonIndexColumn

مقایسه سه عملگر IN و Exists و Join 13

این دستور کارایی کمتری دارند چون از عملگر 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 15

نتیجه حالت سوم: در این حالت In  و Exists مشابه هم بوده و از join سبک تر می باشد.

  1. یک جدول بزرگ و یک  جدول کوچک را در نظر گرفته بر اساس جستجو روی فیلد مرتب اقدام به بررسی خوایم کرد.

SELECT  IndexColumn

FROM    dbo.tb_Outer

WHERE   IndexColumn IN

        (

        SELECT  IndexColumn

        FROM    dbo.tb_SmallInner

        )

مقایسه سه عملگر IN و Exists و Join 17

روش 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

           )

مقایسه سه عملگر IN و Exists و Join 19

           SELECT  tb_Outer.NonIndexColumn

FROM    tb_Outer

JOIN    (

        SELECT  DISTINCT IndexColumn

        FROM    tb_SmallInner

        ) tb_SmallInner

ON      tb_Outer.IndexColumn = tb_SmallInner.IndexColumn

مقایسه سه عملگر IN و Exists و Join 21

نتیجه حالت چهارم : در این حالت join و exists مشابه هم بوده ولی in یک lookup کمتر داشته و بهتر است.

  1. در حالت آخر به جای استفاده از شرط مساوی از عملگر های ریاضی استفاده خوایم کرد.

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

مقایسه سه عملگر IN و Exists و Join 23

نتیجه: در عبارت محاسباتی Join بهتر است

 

اشتراک گذاری

0 0 رای ها
امتیازدهی به این محتوا
اشتراک در
اطلاع از
guest
0 نظرات
بازخورد (Feedback) های اینلاین
مشاهده همه دیدگاه ها
0
افکار شما را دوست داریم، لطفا نظر دهید.x