بهینه سازی SQL

تنظیم عملکرد SQL می تواند یک کار فوق العاده دشوار باشد، مخصوصا هنگام کار با داده های مقیاس بزرگ که حتی تغییرات جزئی ممکن است تاثیر قابل توجهی (مثبت یا منفی) بر عملکرد دیتابیس داشته باشد.
در میان شرکت های متوسط و بزرگ، اغلب تنظیمات عملکرد SQL توسط یک مدیر پایگاه داده  یا DBA  انجام می شود. اما من اعتقاد دارم بهینگی عملکرد دیتابیس را تمام توسعه دهندگان باید بدانند.
هنگام کار با داده های مقیاس بزرگ، حتی تغییرات جزئی ممکن است تاثیر قابل توجهی بر عملکرد داشته باشد.
در این مقاله می خواهم دو چیز را خواهیم کرد:

     آشنایی با برخی از تکنیک های تنظیم عملکرد SQL سمت توسعه دهنده.
     توضیح این که چگونه توسعه دهندگان و DBA ها می توانند به طور موثری کار کنند.

ایندکس گذاری - SQL Performance Tuning (in the Codebase): Indexes

اگر شما یک تازه وارد درکار با پایگاه داده هستید حتما از خودتان پرسیدید «تنظیم عملکرد SQL چیست؟».
 باید بدانید که ایندکس گذاری یک راه موثر برای تنظیم پایگاه داده SQL است که اغلب در طول توسعه نادیده گرفته می شود. بطور کلی ایندکس سرعت عملیات بازیابی داده ها را از یک جدول پایگاه داده بهبود میبخشد و با ارائه یک دسترسی کارآمدتر مرتب سازی را نیز سریعتر میکند. این به این معنی است که هنگامی که یک ایندکس ایجاد میکنید، می توانید ردیف های خود را سریعتر از قبل انتخاب کنید.
Index ها همچنین برای تعریف یک Primary Key یا یک شاخص منحصر به فرد استفاده می شوند که تضمین می کند که هیچ ستون دیگری دارای مقادیر مشابهی نیست. اگر شما جدیدا وارد دنیا ایندکس گذاری شده اید، من پیشنهاد می کنم هنگام ساختن کوئری های خود از این نمودار استفاده کنید

اساسا هدف از ایندکس گذاری برای جستجو در بین حجک زیادی از داده ها و مرتب سازی  در ستون ها است

توجه داشته باشید که اگر جداول شما به طور مداوم توسط INSERT، UPDATE و DELETE تغییر می کنند، در ایندکس گذاری باید مراقب باشید – چون این عملیات ها می توانید عملکرد ایندکس گذاری را کاهش دهد زیرا همه ایندکس ها بعد از این عملیات باید اصلاح شوند.
اغلب DBA ها برای انجام درج تعداد زیادی از رکوردها، مثلا چند میلیون رکورد بصورت یکجا، تمامی ایندکس ها را خدف میکنند. اینکار به این دلیل است که سرعت درج اطلاعات افزایش پیدا میکند و بعد از اتمام درج اطلاعات دوباره اقدام به ایندکس گذاری روی جداول مربوط می کنند. توجه داشته باشید که این نکات تاثیر زیادی در بهینه سازی دارند بنابراین شما هم این رویکرد را در هنگام درج تعداد زیادی از رکوردها داشته باشید

SQL Tuning: Execution Plans in SQL Server
به عنوان یک روش ساده دیگر استفاده از ابزار Planning Execution در SQL Server می تواند برای ایجاد ایندکس ها مفید باشد.
برای استفاده از execution plan  در SQL Server Management Studio قبل از اجرای کوئری خود، تنها کافی است گزینه ی  Include the Real Execution Plan را انتخاب نمایید و یا از کلید های ترکیبی (CTRL + M) استفاده نمایید. پس از این کار برگه سومی به نام Execution Plan اضافه میشود. سپس کوئری خود را اجرا نمایید. و اگر شما ایندکسی را فراموش کرده باشید برای ایجاد آن تنها کافیست روی Execution Plan  کلیک راست کنید و Missing Index Details… را انتخاب نمایید . به همین سادگی

اجتناب از حلقه تکرار - SQL Tuning: Avoid Coding Loops
تصور کنید سناریویی داریم که قرار است یک کوئری را هزار مرتبه پشت سرهم در دیتابیس اجرا کند چیزی مانند کوئری زیر

for (int i = 0; i < 1000; i++)

{

    SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES...");

    cmd.ExecuteNonQuery();

}

نکته ای که وجود دارد اینست که شما باید همیشه از لوپ در کد خودتون پرهیز کنید. برای مثال ما میتوانیم کد بالا را با یک دستور Insert  و Update  جایگزین کنیم

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008

INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005

UPDATE TableName SET A = CASE B
        WHEN 1 THEN 'NEW VALUE'
        WHEN 2 THEN 'NEW VALUE 2'
        WHEN 3 THEN 'NEW VALUE 3'
    END
WHERE B in (1,2,3)

 

البته میتوانیم دستور Update را به نحوی بهینه تر کنیم که تنها رکوردهایی آپدیت شوند که به تازگی درج شده اند. این کار ساده حتی میتواند باعث شود 1000 کوئری بجای دها هزار کوئری اجرا شود

UPDATE TableName
SET A = @VALUE
WHERE
      B = 'YOUR CONDITION'
            AND A <> @VALUE -- VALIDATION

 

اجنتاب از ساب کوئری های همبسته  - SQL Tuning: Avoid Correlated SQL Subqueries
ساب کوئری های همبسته یا correlated subquery به کوئری هایی گفته میشود که مقادیر خود را از کوئری والد دریافت میکنند. این نوع کوئری ها به ازای هر ردیف از نتیجه کوئری اصلی، یکبار اجرا میشوند که عملکرد SQL را بشدت کاهش میدهند. توسعه دهندگان تازه کار اغلب از همین روش برای بدست آوردن نتایج کوئری خود استفاده میکنند زیرا مسیر آسانی برای رسیدن به نتایج دلخواه است.
یک نمونه از correlated subquery را در زیر میبینید

SELECT c.Name,
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c

 

به طور خاص، مشکل این است که کوئری داخلی (SELECT CompanyName ...)  برای هر ردیفی که کوئری بیرونی بر میگرداند (SELECT c.Name ...)  اجرا می شود. اما چرا باید برای هر ردیف از کوئری بیرونی کوئری داخلی را اجرا کنیم وقتی میتوانیم اینکارو نکنیم؟!
یک تکنینک برای رحل مشکلاتی از این قبیل بازنویسی کوئری با دستور JOIN به جای correlated subquery هاست.

SELECT c.Name,
       c.City,
       co.CompanyName
FROM Customer c
    LEFT JOIN Company co
        ON c.CompanyID = co.CompanyID

در حقیقت  کوئری بالا که بهینه شده است ، جدول Company را تنها یکبار در ابتدا JOIN بازیابی میکند و سپس برای دفعات بعدی از آن استفاده میکند

اجنتاب از * در Select 
یکی از نکات بهینه سازی در SQL که بسیار مورد علاقه من نیز هست، این است که از SELECT *  اجتناب کنید! در عوض، شما باید به صورت جداگانه ستون های خاصی را که نیاز دارید را در کوئری قرار دهید. اگرچه، این نکته به نظر ساده است، اما من این خطا را در همه جا می بینم. یک جدول با صدها ستون و میلیون ها ردیف را در نظر بگیرید. اگر درخواست شما فقط نیاز به چند ستون داشته باشد، در جستجوی تمام داده ها هیچ معنی ندارد. این یک اتلاف منابع عظیم است.
اگر واقعا به ستونی نیاز دارید، به صراحت هر ستون را لیست کنید. این یک قاعده نیست، بلکه یک ابزار پیشگیری از خطاهای سیستم، آینده نگری و تنظیم عملکرد SQL است. به عنوان مثال، اگر از * در دستور INSERT ... SELECT ...  استفاده می کنید و جدول منبع با اضافه کردن یک ستون جدید تغییر کرده است، شما ممکن است با مسائل جدی روبرو شوید.
اما توجه داشته باشید که گاهی اوقات استفاده از Select *  میتواند مناسب باشد به عنوان مثال در کار با جداول temp. این مورد را در ادامه این مقاله بررسی میکنیم.

در استفاده از جداول موقت باهوش باشید - SQL Tuning: The Wise Use of Temporary Tables (#Temp)

جداول موقت معمولا پیچیدگی پرس و جو را افزایش می دهد. اگر کد شما می تواند به روش ساده نوشته شود، پیشنهاد می کنم از جداول temp اجتناب کنید.
اما اگر یک store Procedure  داشته باشید که با یک پرس و جو تنها قابل انجام نیست، می توانید جداول temp را به عنوان واسطه،  برای کمک به تولید یک نتیجه نهایی استفاده کنید.
هنگامی که شما مجبور هستید به یک جدول با اطلاعات حجیم JOIN  شوید که شرط کوئری نیز در جدول ذکر شده وجود دارد، می توانید عملکرد پایگاه داده را با انتقال اطلاعات خود در جدول temp و سپس JOIN زدن به آن، افزایش دهید. چراکه حالا جداول temp ردیف های کمتری از جدول اصلی (بزرگ) دارد، بنابراین JOIN سریعتر پایان خواهد یافت!
این کار همیشه درست نیست، اما این مثال به شما این حس را می دهد که در آن ممکن است بخواهید از جداول temp استفاده کنید:
تصور کنید  جدول Customer با میلیون ها رکورد وجود دارد. شما هم میخواهید که روی یک فیلد خاص JOIN  بزنید. شما می توانید با استفاده از دستور SELECT INTO رکوردهای ک نیاز دارید را در یک جدول temp  بریزید و سپس با جدول اصلی ، JOIN بزنید.

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

نکته: برخی از توسعه دهندگان SQL در استفاده از دستور Select INTo برای ایجاد جداول temp  پرهیز میکنند چرا که معتقدند این دستور باعث قفل شدن tempdb دیتابیس میشود و اجازه نمیدهد سایر کاربران جدول temp ایجاد کنند
به عنوان یک جایگزین برای جداول temp، شما میتوانید از یک ساب کوئری به عنوان یک جدول استفاده کنید:

SELECT r.RegionName, t.Name FROM Region r
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t
ON t.RegionID = r.RegionID

و نکته دیگر اینکه، زمانی که با جدول temp کارتان تمام شد، آن را پاک کنید تا منابع tempdb پاک شوند، و منتظر نمانید که به صورت خودکار حذف شود .

بررسی وجود یک رکورد 
میرسیم به تکینیک استقاده از Exists برای بررسی اینکه رکوردی وجود دارد یا خیر. گاهی دیده میشه که برای بررسی وجود یک رکورد از دستور Count نیز استقاده میشه به این صورت که از دستور Count با یک شرط استفاده میشود و اگر مقدار صفر برگردانده شد به معنی عدم وجود رکورد است. تفاوت دستور Exists و Count در بررسی وجود رکورد در این است که دستور Exists با پیدا کردن اولین رکورد از ادامه دادن صرف نظر میکند و بقیه رکوردها پیمایش نمیشوند درحالی که دستور Count  تمام رکوردهای جدول را بررسی میکند که از این بابت از استفاده از دستور Exists بسیار بهینه تر است.