8 نکته برای بهینه سازی دستورات SQL Server

1- از SET NOCOUNT ON استفاده کنید:

SQL Server همراه دستورات Select و DML که اجرا میکند، تعداد ردیف هایی که تحت تاثر دستور قرار گرفته اند را نمایش میدهد. مثلا اگر یک درستور Select اجرا کنید که نتیجه آن شامل 10 رکورد باشد، با رفتن به قسمت Message مشاهده میکنید که پیغام "(10 row(s) affected)" چاپ شده است. اما اگر از دستور Set Nocount ON استفاده کنید، پیغام "Command(s) completed successfully." چاپ خواهد شد. این اطلاعات هنگامی که در حال دیباگ کردن برنامه هستیم مفید است، اما بعد از آن، در بسیاری از موارد، این اطلاعات بی فایده است. این درحالی است که اگر در یک حلقه از دستورات استفاده کنیم، بدست آوردن این اطلاعات سربار زیادی روی سیستم خواهد داشت و با خاموش کردن آن، میتوان به کارایی بهتری دست یافت. برای استفاده از این امکان به صورت زیر عمل کنید:

CREATE PROC dbo.ProcName

AS

SET NOCOUNT ON;

--کدهای اجرایی دلخواه اینجا میآید مانند کد زیر

SELECT column1 FROM dbo.TblTable1

-- و مجددا برای استفاده های بعدی این امکان را فعال کنید.

SET NOCOUNT OFF;

GO 

2-از نام schemaها همراه با نام اشیا استفاده کنید.
همراه نام SPها و سایر اشایی که در یک SP استفاده میکنید، حتما از نام Schema استفاده کنید. با این کار، هنگام اجرا به جای اینکه در تمام جاهایی که امکان وجود شی وجود دارد،SQL Server جستجو شود، مستقیما به سراغ شی مورد نظر میرود. به مثال زیر دقت کنید:

SELECT * FROM dbo.MyTable -- از این روش استفاده کنید

-- بجای

SELECT * FROM MyTable -- از این روش استفاده نکنید.

--و در نهایت برای اجرای رویه ذخیره شده از روش زیر استفاده کنید

EXEC dbo.MyProc -- از این روش استفاده کنید

--بجای

EXEC MyProc -- از این روش استفاده نکنید.

3-به هیچ وجه از پیشوند sp_ برای نام گذاری رویه های ذخیره شده استفاده نکنید.

در صورتی که نام یک رویه با پیشوند SP_ شروع شود، SQL Server برای اجرای رویه ابتدا سراغ دیتابیس Master می رود، و درصورتی که رویه را آنجا پیدا نکند به سراغ دیتابیس جاری میرود. این جستجوی اضافه 2 اشکال دارد، اولی صرف زمان بیشتر برای پیدا کردن رویه در پایگاه داده است و دومی که بسیار مهم است، اگر رویه در دیتابیس Master نیز وجود داشته باشد، نتیجه اشتباهی دریافت خواهیم کرد.

4-از IF EXISTS (SELECT 1) به جای (SELECT *) استفاده کنید:

زمانی که از IF EXISTS استفاده میکنیم، میخواهیم بررسی کنیم که به ازای شرایط خاصی در جدولی دیگری رکوردی وجود دارد یا خیر، نتیجه این که دستور Select داخلی به ما میدهد اهمیتی ندارد( این نتیجه میتواند یک ستون باشد، یک عدد ثابت یا لیستی از تمامی ستونی های جدول مورد نظر)، زیرا IF EXISTS تنها وجود نتیجه را بررسی میکند، و در صورتی که نتیجه ای وجود داشته باشد، True برمیگرداند. پس در این شرایط برای دستیابی به عملکرد بهتر میتوان از روش زیر استفاده کرد:

IF EXISTS (SELECT 1 FROM sysobjects

WHERE name = 'MyTable' AND type = 'U')

5-برای اجرا دستورات به جای Execute از sp_executesql استفاده کنید:

sp_executesql پارامتر پذیر است.بنا بر این استفاده از sp_executesql قابلیت استفاده مجدد را برای ما فراهم میکند. execution plan برای دستورات پویای SQL تنها در صورتی قابلیت استفاده مجدد دارد که تمامی کاراکتر های موجود در کد مشابه همدیگر باشند. برای مثال به قطعه کد زیر دقت کنید:

DECLARE @Query VARCHAR(100)

DECLARE @Age INT

SET @Age = 25

SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)

EXEC (@Query)

حال اگر ما مجددا کوئری را اجرا کنیم و برای @ Age به جای 25 مقدار 20 را ارسال کنیم، اجرا دستور باعث ایجاد یک execution plan جدید میشود و از execution plan قبلی استفاده نمیشود. اگر قطعه کد بالا را به صورت زیر بنویسیم:

DECLARE @Query NVARCHAR(100)

SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'

EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

اولین باری که دستور اجرا میشود execution plan ایجاد میشود و از این به بعداز این execution plan استفاده میشود حتی اگر مقدار پارامتر عوض شود.استفاده از execution plan به جای ایجاد آن باعث بهبود کاراریی میشود.
6-در صورت امکان از cursorها استفاده نکنید:
کرسر های برای نگهداری موقعیت رکورد جاری در مجموعه رکورد هایی که در حال حاضر مشغول کار کردن روی آنها هستیم، از منابع زیادی استفاده میکنند. و باعث کاهش کارایی میشوند. در صورتی که نیاز به پردازش تک تک رکورد ها در یک حلقه باشد میتوان از دستور While استفاده کرد. هرجا که ممکن باشد باید به جای استفاده از دستورات cursor-based از روش های SET-based استفاده کنیم زیرا SQL Server طوری طراحی شده است که بتواند دستورات SET-based را بهینه کند.
7-تراکنش ها را در حد امکان کوتاه نگه دارید:
با بزرگ شدن اندازه تراکنش ها، انسداد و بن بست بیشتر میشود. تا زمانی که یک تراکنش باز باشد، منابع را برای خود قفل شده نگه میدارد و در بعضی موارد ممکن است منابعی که در حال حاضر در دست یک تراکنش است، مورد نیاز تراکنشی دیگر باشد، و این مطلب باعث به وجود آمدن انسداد میشود. وقتی تعداد انسداد ها زیاد میشود، ممکن است بن بست بوجود آید و تراکنش 1 منتظر منابعی شوند که در اختیار تراکنش 2 است و تراکنش 2 نیز منتظر منبع مورد استفاده در تراکنش 1 شود.
8-از TRY-Catch برای مدیریت خطا ها استفاده کنید:

تا قبل از SQL Server 2005 مکانیزم خوبی برای مدیریت خطا ها در SQL Server وجود نداشت و مجبور بودیم بعد از هر عبارتی وضعیت خطا ها را به طور دستی بررسی کنیم. این کار با افزایش حجم کدها باعث استفاده از منابع و از دست رفتن زمان میشد. از زمان SQL Server 2005 به بعد برای مدیریت خطا ها روش جدیدی ارائه شد که استفاده از آن به روش زیر میباشد.

BEGIN TRY

--Your t-sql code goes here

END TRY

BEGIN CATCH

--Your error handling code goes here

END CATCH
پ ن: مطلب فوق ترجمه مقاله ای بود که مطلب اصلی رو میتوانید از اینجا مطالعه کنید.