افزایش سرعت جستجو در SQL

آموزش پیشرفته SQL Server (جلسه هفتم) - افزایش سرعت جستجو در SQL

افزایش سرعت جستجو در SQL، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی افزایش سرعت جستجو در SQL، قسمت های قبلی را مرور فرمایید.

تکنیک های افزایش سرعت جستجو در SQL Server

برای افزایش سرعت کوئری ها، لطفاً به موارد زیر دقت فرمایید:

لیست کردن نام فیلدها بجای استفاده از * Select سرعت جستجو را افزایش خواهد داد.
استفاده از عملگرهای شرطی مناسب (Sargable Condition) در کوئری ها سرعت جستجو را افزایش خواهد داد.
استفاده صحیح از عملگر Like سرعت جستجو را افزایش خواهد داد.
عدم استفاده از عبارت های محاسباتی در قسمت Where، سرعت جستجوها را افزایش خواهد داد.
عدم استفاده از توابع SQL Server در قسمت Where، باعث افزایش سرعت جستجوها در SQL خواهد داد.
یکسان بودن نوع مقدار تنظیم شده با فیلد از از تبدیل خودکار داده (Data Conversion) توسط SQL Server جلوگیری کرده و سرعت را افزایش خواهد داد.
عدم استفاده از Local Variable ها در کوئری، باعث افزایش سرعت جستجو خواهد شد.
مجبور نکردن Optimizer برای استفاده از یک ایندکس خاص، سرعت اجرای جستجو را افزایش خواهد داد.
عدم استفاده از Join Hint سرعت جستجو را افزایش خواهد داد.
عدم استفاده از Relation در جداول با حجم فوق العاده بالا، کارایی را افزایش خواهد داد.
استفاده از Union All بجای Union، سرعت جستجو در SQL را افزایش خواهد داد.

در ادامه به توضیح مختصر هر کدام از موارد بالا خواهیم پرداخت.

1- لیست کردن نام فیلدها بجای استفاده از * Select

یکی از تکنیک های افزایش سرعت جستجو، استفاده نکردن از * Select و لیست کردن نام فیلدها بجای * است.

در زیر به برخی از معایب * Select اشاره شده است:

با فرض اینکه ایندکس های مناسبی روی جداول ایجاد کرده باشید، * Select باعث می شود تا از ایندکس های مذکور استفاده مناسبی نشود و کوئری ها بسمت Clustered Index Scan و Table Scan خواهند رفت.
استفاده از * Select باعث افزایش ترافیک شبکه خواهد شد.
استفاده از * Select زمان اجرای کوئری ها را افزایش خواهد داد.
استفاده از * Select زمان عملیات I/O را افزایش خواهد داد.

مثال: در مثال زیر، از * Select استفاده شده است:

افزایش سرعت جستجو در SQL

SELECT * FROM Sales.SalesOrderDetail
    WHERE SalesOrderID>50000 AND OrderQty>1

کوئری دوم: در این مثال بجای استفاده از *، تنها فیلدهای مورد نیاز را ذکر کرده ایم:

افزایش سرعت جستجو در SQL

SELECT SalesOrderID , CarrierTrackingNumber , OrderQty ,

       ProductID , SpecialOfferID , UnitPrice 
FROM Sales.SalesOrderDetail
    WHERE SalesOrderID>50000 AND OrderQty>1

در SQL Server می توان میزان ارسال و دریافت داده ها برای کوئری های مختلف را مشاهده نمود. بنابراین از نوار افقی بالا یا همان Edit SQL روی آیکن Include Client Statistics کلیک نمایید:

حالا یکبار کوئری اول را اجرا نموده و سپس کوئری دوم را اجرا نمایید. سپس در پایین پنجره، روی تب Client Statistics کلیک نمایید. حالا می توانید حجم داده های ارسال شده از سرور را به ازای کوئری اول و دوم مشاهده کنید. همان طور که در تصویر اول مشاهده می کنید، میزان داده ی ارسال شده برای کوئری اول که شامل * Select بود در مقایسه با دیگری بسیار بیشتر است و این باعث افزایش ترافیک شبکه خواهد شد:

2- استفاده از عملگرهای شرطی مناسب (Sargable Condition)

Sargable سرنام واژگان Search Argument Able است. در واقع به تک تک شروطی که در قسمت Where بکار می بریم، آرگومانهای جستجو یا SARG گفته می شود. و اگر بصورت بهینه انتخاب شوند، Sargable خواهند بود.

استفاده از عملگرهای شرطی مناسب، به انتخاب ایندکس مناسب کمک خواهد کرد.

همچنین انتخاب مناسب عملگرهای شرطی، روی عملیات Scan Count تأثیر مثبت خواهد گذاشت.

عملگرهای شرطی Sargable شامل موارد زیر است:

=
>
=>
<
=<
BETWEEN
LIKE اگر با یک کاراکتر شروع شود مثل '%R'

عملگرهای شرطی که Sargable نیستند:

!=
>!
<!
<>
NOT EXISTS
IN
NOT IN
LIKE اگر با علامت % شروع شود مثل 'R%'
NOT LIKE
استفاده از FUNCTION ها

مثال: در مثال زیر، هم از * Select استفاده شده است و هم از عملگرهای شرطی نامناسب:

افزایش سرعت جستجو در SQL

SELECT * FROM Sales.SalesOrderHeader
    WHERE SalesOrderID IN (75000,75001,75002)

کوئری دوم: برای بهینه کردن کوئری بالا، باید بجای استفاده از *، تنها فیلدهای مورد نیاز را ذکر کرده و همچنین بجای عملگر IN از => و <= استفاده نمود:

افزایش سرعت جستجو در SQL

SELECT SalesOrderID , CarrierTrackingNumber , OrderQty ,

       ProductID , SpecialOfferID , UnitPrice 
FROM Sales.SalesOrderDetail
    WHERE SalesOrderID >=75000 AND SalesOrderID<=75002

همچنین می توان از BETWEEN استفاده نمود:

افزایش سرعت جستجو در SQL

SELECT SalesOrderID , CarrierTrackingNumber , OrderQty ,

       ProductID , SpecialOfferID , UnitPrice 
FROM Sales.SalesOrderDetail
   WHERE SalesOrderID BETWEEN 75000 AND 75002

برای مقایسه کوئری های بالا، لطفاً ابتدا دستور SET STATISTICS IO ON را اجرا کنید و سپس Scan Count هر کدام را مقایسه نمایید.

3- استفاده صحیح از عملگر Like

برای اینکه سرعت جستجو در SQL بیشتر شود، سعی کنید از علامت '%' در ابتدا استفاده نکنید، در غیر اینصورت از ایندکس ها بصورت مفید استفاده نخواهد شد.

برای مقایسه دو کوئری مختلف از این لحاظ، ابتدا با استفاده از دکمه های میانبر Ctr + M قابلیت Actual Execution Plan را فعال نمایید و سپس کوئری ها را اجرا نمایید. در تب Actual Execution Plan مشاهده خواهید کرد که کوئری ای که از '%' در ابتدای Like استفاده کرده، بسمت Index Scan رفته و کوئری دوم که از '%' در انتها استفاده کرده به سمت Index Seek رفته است.

4- عدم استفاده از عبارت های محاسباتی در قسمت Where

استفاده از عبارت های محاسباتی در قسمت Where باعث افزایش I/O و همچنین عدم انتخاب ایندکس مناسب خواهد شد.

مثال:

افزایش سرعت کوئری با استفادهنکردن از عبارت های محاسباتی در قسمت where

SELECT * FROM  Purchasing.PurchaseOrderHeader AS poh
    WHERE  poh.PurchaseOrderID * 2 = 3400 ;

پیشنهاد می شود بجای کوئری بالا از کوئری زیر استفاده نمایید:

افزایش سرعت کوئری با استفادهنکردن از عبارت های محاسباتی در قسمت where

SELECT * FROM  Purchasing.PurchaseOrderHeader AS poh
    WHERE  poh.PurchaseOrderID  = 3400/2 ;

اگر بعد از اجرای دو کوئری بالا به Actual Execution Plan توجه فرمایید، مشاهده خواهید کرد که کوئری اول بسمت Index Scan رفته در حالی که برای دومی Index Seek اتفتاق افتاده است.

5- عدم استفاده از توابع SQL Server در قسمت Where

استفاده از توابعی مثل Substring یا Trim و ... در قسمت where باعث افزایش I/O و همچنین عدم انتخاب ایندکس مناسب خواهد شد. البته مانند قبل Index Scan اتفاق خواهد افتاد.

6- یکسان بودن نوع مقدار تنظیم شده با فیلد

یکسان بودن نوع مقدار تنظیم شده با فیلد از از تبدیل خودکار داده (Data Conversion) توسط SQL Server جلوگیری کرده و سرعت جستجو در SQL را افزایش خواهد داد.

مثال: در مثال زیر، کد ملی یا همان NationalIDNumber از نوع رشته (CHAR) است، ولی در قسمت Where زمان مقداردهی بین علامت کوتیشن محصور نشده است.

تبدیل خودکار داده باعث کاهش سرعت اجرای کوئری خواهد شد

SELECT E.BusinessEntityID,E.NationalIDNumber,E.LoginID
     FROM HumanResources.Employee E
        WHERE E.NationalIDNumber=99331026

توجه: با اجرای کوئری بالا، با خطا مواجه نخواهید شد، و اگر به Execution Plan مراجعه کنید، خواهید دید که تنها یک Warning اتفاق افتاده است و SQL Server بصورت خودکار مقدار 993310268 را از نوع عددی به رشته ای تبدیل می کند.

بنابراین بهتر است بجای کوئری بالا از کوئری زیر استفاده نمایید:

تبدیل خودکار داده باعث کاهش سرعت اجرای کوئری خواهد شد

SELECT E.BusinessEntityID,E.NationalIDNumber,E.LoginID
     FROM HumanResources.Employee E
        WHERE E.NationalIDNumber='993310268'

7- عدم استفاده از Local Variable ها در کوئری

استفاده از Local Variableها در کوئری در واقع باعث خواهد شد تا Optimizer در محاسبات Statistics به مشکل برخورد.

مثال:

استفاده از Local Variable ها سرعت جستجو را کاهش خواهد داد

DECLARE @id INT = 1 ;
SELECT pod.*
FROM  Purchasing.PurchaseOrderDetail 
WHERE  poh.PurchaseOrderID >= @id ;

8- مجبور نکردن Optimizer برای استفاده از یک ایندکس خاص

با استفاده از دستور With می توان یک کوئری را برای استفاده از یک ایندکس خاص مجبور کرد، توجه داشته باشید که این روش تنها برای آزمایش استفاده می شود و خیلی بندرت اتفاق می افتد.

مثال: در کوئری زیر، با استفاده از دستور with بعد از نام جدول، Optimizer را مجبور کرده ایم که از ایندکس PK_PurchaseOrderHeader_PurchaseOrderID استفاده نماید.

استفاده از دستور With

SELECT * FROM  Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID))
    WHERE  poh.PurchaseOrderID * 2 = 3400

لطفاً کوئری بالا را یکبار بدون With اجرا کنید و با مراجعه به قسمت Execution Plan، هزینه یا Cost را با کوئری قبلی مقایسه نمایید.

9- عدم استفاده از Join Hint

برای افزایش سرعت جستجو در SQL، تا حد امکان از Jion Hint استفاده نکنید.

join Hint چیست؟

همان طور که می دانید، برای Join کردن جداول، 4 استراتژی مختلف وجود دارد:

LOOP
MERGE
HASH
REMOTE

در این مجال مختصر، امکان توضیح در مورد استراتژی های بالا وجود ندارد، اما نکته ی مهمی که وجود دارد، شما با استفاده از Join Hint می توانید، Optimizer را برای Join کردن دو جدول مجبور به استفاده از یکی از 4 استراتژی بالا نمایید.

مثال: در مثال زیر، برای Join کردن جداول، Optimizer را مجبور به استفاده از روش Hash کرده ایم:

عدم استفاده از Join Hint برای افزایش سرعت جستجو در SQL

SELECT p.Name, pr.ProductReviewID
FROM Production.Product AS p
LEFT OUTER HASH JOIN Production.ProductReview AS pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC;

10- عدم استفاده از Relation در جداول با حجم فوق العاده بالا

همان طور که می دانید با استفاده از Relation می توانیم جامعیت اطلاعات یا همان Data Integrity را تضمین کنیم، اما زمانی که حجم داده های جداول به شدت افزایش پیدا می کند، Relationهای مذکور باعث کند شدن عملیات های Insert / Update / Delete خواهد شد.

بنابراین پیشنهاد می شود در پایگاه داده های حجیم از Relation صرفنظر کرده و قوانین موجود را در سطح Application برقرار نمایید.

11- استفاده از Union All بجای Union

برای کسب اطلاعات بیشتر در مورد Union به لینک روبرو مراجعه فرمایید: آموزش SQL-عبارت Union

عملگر UNION برای ترکیب نتایج دو یا چند دستور SELECT استفاده می شود و البته داده های مشترک بین جداول فقط یکبار در خروجی نمایش داده می شود. اما نکته ای که وجود دارد این است که در عملیات Union برای حذف تکراری ها ابتدا باید داده های جداول مرتب شوند و سپس با هم ادغام می شوند. اما در Union All بدلیل اینکه تمام داده های دو جدول همراه لیست می شوند نیاز به مرتب سازی نیست.