خانه SQL Server مقایسه سرعت اجرای سه دستور in ،Exists و join [بخش اول] SQL Server دستورات SQL نوشته شده توسط: محمد رضا عدالت پیشه تاریخ انتشار: ۰۸ آبان ۱۳۹۵ آخرین بروزرسانی: ۱۴ مهر ۱۴۰۲ زمان مطالعه: 10 دقیقه ۳ (۲) مقدمه مقایسه سرعت اجرای سه دستور 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 ) در قسمت دوم این مقاله موارد جذابتری را بررسی خواهیم کرد. چه رتبه ای میدهید؟ میانگین ۳ / ۵. از مجموع ۲ اولین نفر باش معرفی نویسنده معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۱۹ مهر زبان های برنامه نویسی Collection در جاوا؛ انواع، ویژگی و نحوه بهینهسازی تیم فنی نیک آموز ۱۸ مهر اکسل مسیر استادی با فرمول نویسی در اکسل تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۵ مهر زبان های برنامه نویسی معرفی برترین نرم افزار برنامه نویسی اندروید به همراه معرفی ابزارهای مهم تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ تورج اسکویی لر ۱۵ / ۰۸ / ۹۵ - ۰۸:۵۴ سپاس جالب بود بی صبرانه منتظر قسمت دوم این مقاله می باشیم پاسخ به دیدگاه تورج اسکویی لر ۱۵ / ۰۸ / ۹۵ - ۰۸:۵۴ سپاس جالب بود بی صبرانه منتظر قسمت دوم این مقاله می باشیم پاسخ به دیدگاه