نکاتی برای بهبود عملکرد Stored Procedure

ما می توانیم با ایجاد تغییرات ساده در کد، پروسیجر خود را بهینه کنیم. در زیر چند نکته وجود دارد که می تواند پروسیجرها را بهینه کند.

• از “* SELECT” در کوئری درون پروسیجر استفاده نکنید
• سعی کنید از استفاده از جداول موقت در پروسیجر خود اجتناب کنید
• از ایندکس های مناسب استفاده کنید
• پروسیجر را با استفاده از نام کاملاً واجد شرایط آنها فراخوانی کنید
• از پیشوند “_sp” در نام پروسیجر استفاده نکنید. (بجایش usp)
• از استفاده از Cursorها خودداری کنید
• از DISTINT و ORDER BY خودداری کنید
• CAST بجای CONVERT
• سعی کنید از استفاده از عبارات DDL (زبان تعریف داده) در پروسیجر خود اجتناب کنید
• سعی کنید از IN اجتناب کنید
• از TRY-Catch برای رسیدگی به خطاها استفاده کنید
• اجتناب از عبارت Where زیاد درون پروسیجر
• از متغیرهای غیر ضروری اجتناب کنید
• سعی کنید از SQL Dynamic اجتناب کنید
• از استفاده از ()COUNT در یک کوئری خودداری کنید
• NOCOUNT را فعال کنید
• به جای دستور EXECUTE از پروسیجر sp_executesql استفاده کنید
• SELECT در مقابل SET
• فهرست ستونها را در عبارت Insert قرار دهید
• از فراخوانی تابع اسکالر در لیست ستون و عبارت Where خودداری کنید
• تراکنش را تا حد امکان کوتاه نگه دارید
• سعی کنید از deadlock دوری کنید
• سعی کنید از UNION برای اجرای عملیات “OR” استفاده کنید
• یک پروسیجر بسیار بزرگ را به چندین پروسیجر فرعی تقسیم کنید

جزئیات بیشتر را در زیر بخوانید.

از “* SELECT” در کوئری SQL استفاده نکنید

سعی کنید به جای * از تعداد ستون مورد نیاز در select استفاده کنید. استفاده از * تمام ستون‌ها را برمی‌گرداند و یک RecordSet غیرضروری حجیم ایجاد می‌کنند. مثلا بجای کد اولی از دومی استفاده کنید:

SELECT * FROM AdventureWorksDW2019.dbo.DimCustomer

SELECT CustomerKey, GeographyKey, Title, FirstName, MiddleName, LastName, Birthdate FROM AdventureWorksDW2019.dbo.DimCustomer

سعی کنید از استفاده از جداول موقت در پروسیجر خود اجتناب کنید

استفاده از جداول موقت در پروسیجر شانس استفاده مجدد از پلن اجرایی را کاهش می دهد. سعی کنید به جای جدول موقت از متغیر جدولی استفاده کنید. جداول Temp می توانند باعث کامپایل مجدد SP شوند. اگر از تغیرهای جدولی استفاده کنیم SP دوباره کامپایل نمی شود.

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

از ایندکس های مناسب استفاده کنید

Index scan و index seek بسیار سریعتر از table scan هستند. بنابراین scan table را از پلن اجرایی شناسایی کنید و کاری کنید پلن اجرایی به آن سمت نرود. اما زمانی که جدول تعداد ردیف های کمتری را برمی گرداند، بهتر است از scan table استفاده کنید.

پروسیجرها را با استفاده از نام کاملاً واجد شرایط ( fully qualified names)  آنها فراخوانی کنید

نام کامل یک شی از چهار شناسه تشکیل شده است: نام سرور، نام پایگاه داده، نام مالک و نام آبجکت. نام آبجکتی که هر چهار قسمت را مشخص می کند به عنوان یک نام کاملا واجد شرایط شناخته می شود.

استفاده از نام‌های کاملاً واجد شرایط، هرگونه سردرگمی در مورد اینکه کدام پروسیجر را می‌خواهیم اجرا کنیم را از بین می‌برد و می‌تواند عملکرد را افزایش دهد، زیرا SQL Server شانس بیشتری برای استفاده مجدد از پلن های اجرایی پروسیجرها در صورت اجرای نام‌های کاملاً واجد شرایط دارد. مثال را ببینید (بجای اولی از دومی استفاده کنید):

USE AdventureWorksDW2019

EXEC GetEmployeeDetails

EXEC MY-Server.AdventureWorksDW2019.dbo.GetEmployeeDetails

از پیشوند “_sp” در نام پروسیجر استفاده نکنید

پیشوند “_sp” در نام پروسیجرهای سیستمی استفاده می شود. مایکروسافت استفاده از پیشوند “_sp” را در نام‌های پروسیجرهای ایجاد شده توسط کاربر توصیه نمی‌کند زیرا SQL Server همیشه به دنبال پروسیجری است که با “_sp” شروع می‌شود با ترتیب زیر: پایگاه داده master، پروسیجر بر اساس نام کاملاً واجد شرایط ارائه شده ، به دنبال آن، پروسیجری که dbo به عنوان مالک آن است(اگر یکی مشخص نشده باشد).

وقتی پروسیجر با پیشوند “_sp” را در پایگاه داده ای غیر از master داریم، همیشه ابتدا پایگاه داده master بررسی می شود. اگر پروسیجر ایجاد شده توسط کاربر هم نام پروسیجر سیستم باشد، پروسیجر توسط کاربر هرگز اجرا نخواهد شد. (میتوان بجای sp مثلا از usp استفاده کرد)

EXEC MY-Server.AdventureWorksDW2019.dbo.usp_GetEmployeeDetails

از DISTINT و ORDER BY اجتناب کنید

اگر به عبارات DISTINCT/ORDER by  نیازی ندارید، سعی کنید از آن اجتناب کنید. DISTINCT یا Order by  غیر ضروری باعث کار اضافی برای موتور پایگاه داده می شود. از این رو اینها عملکرد را کندتر می کنند.

CAST بجای CONVERT

سعی کنید از CAST به جای CONVERT استفاده کنید. CAST استاندارد ANSI-92 است اما CONVERT فقط در MS SQL کار می کند. بهتر است از CONVERT فقط زمانی استفاده کنیم که باید نوع داده DATETIME را با گزینه style قالب بندی کنیم. CAST نمی تواند این کار را انجام دهد.

  سعی کنید از استفاده از عبارات DDL (زبان تعریف داده) در پروسیجر خود اجتناب کنید

استفاده از دستورات DDL در داخل پروسیجرها نیز شانس استفاده مجدد از پلن اجرایی را کاهش می دهد.

سعی کنید از IN اجتناب کنید

در حالی که وجود برخی از مقادیر را بررسی می کنید، سپس به جای IN از EXISTS استفاده کنید. IN مقادیر NULL را نیز می شمارد، اما Exists خیر. EXISTS (بله/خیر) را برمی‌گرداند و بصورت Boolean است، اما IN همه مقادیر را برمی‌گرداند، بنابراین result set برای IN سنگین‌تر از EXISTS است.

برای رسیدگی به خطا از TRY-Catch استفاده کنید.

BEGIN TRY
–Your t-sql code goes here
END TRY
BEGIN CATCH
–Your error handling code goes here
END CATCH

از عبارت  Where مکرر اجتناب کنید

از شرایط غیر ضروری در آن  عبارت اجتناب کنید. همچنین سعی کنید از استفاده از تابع درون این عبارت اجتناب کنید زیرا موتور server Sql را برای index seek ارائه می دهد. حتی آنرا به سمت پلن full index scan SQL یا حتی table scan مجبور می کند.

از متغیرهای غیر ضروری خودداری کنید

تا حد امکان از متغیرهای کمتری استفاده کنید. فضاهای کش را آزاد می کند.

سعی کنید از SQL Dynamic اجتناب کنید

مگر اینکه واقعاً مورد نیاز باشد، اما سعی کنید از استفاده از SQL Dynamic اجتناب کنید زیرا  اشکال زدایی و عیب یابی SQL Dynamic دشوار است. اگر کاربر امکان پاراتر ورودی SQL Dynamic را فراهم کند، احتمال حملات Injection SQL وجود دارد.

از استفاده از ()COUNT  در یک کوئری درون پروسیجر خودداری کنید

وقتی از ()COUNT استفاده می کنیم، SQL Server نمی داند که ما در حال انجام بررسی موجودیت هستیم. همه مقادیر منطبق را، یا با انجام table scan یا با اسکن کوچکترین non-clustered index، شمارش می کند.

وقتی از EXISTS استفاده می کنیم، SQL Server می داند که در حال انجام بررسی موجودیت هستیم. وقتی اولین مقدار منطبق را پیدا کرد، TRUE را برمی گرداند و دیگر جستجو را متوقف می کند. همین امر در مورد استفاده از ()COUNT به جای IN یا ANY صدق می کند. مثال: (کوئری دوم بهینه است)

SELECT Column_List FROM Table WHERE 0 < (SELECT Column_Name FROM Table2 WHERE …)

SELECT column_List FROM Table WHERE EXISTS (SELECT Column_Name FROM Table2 WHERE …)

NOCOUNT را فعال کنید

با هر دستور SELECT و DML، سرور SQL پیامی را برمی‌گرداند که تعداد ردیف‌های تحت تأثیر آن عبارت را نشان می‌دهد. این اطلاعات بیشتر در رفع اشکال کد مفید است، اما بغیر از آن بی فایده است. با تنظیم SET NOCOUNT ON، می توانیم قابلیت بازگرداندن این اطلاعات اضافی را غیرفعال کنیم.

بنابراین توصیه می کنیم از SET NOCOUNT ON به خاطر عملکرد استفاده کنید مگر اینکه دلیل بسیار خوبی برای استفاده از آن وجود داشته باشد.

CREATE PROC GetEmployeeDetail

AS

;SET NOCOUNT ON

–Procedure code here

SELECT EmployeeKey,FirstName,LastName FROM DimEmployee

— Reset SET NOCOUNT to OFF

;SET NOCOUNT OFF

GO

به جای دستور EXECUTE از پروسیجر sp_executesql استفاده کنید

پروسیجر sp_executesql از پارامترها پشتیبانی می کند. بنابراین، استفاده از پروسیجر sp_executesql به جای دستور EXECUTE، خوانایی کد ما را در هنگام استفاده از پارامترهای زیاد، بهبود می بخشد.

هنگامی که از پروسیجر sp_executesql برای اجرای یک دستور Transact-SQL استفاده می کنیم که بارها مورد استفاده مجدد قرار می گیرد. زمانی که تغییر در مقادیر پارامتر تنها تغییر در عبارت باشد ، بهینه ساز پرس و جوی SQL Server از پلن اجرایی که برای اولین بار ایجاد می کند باز استفاده می کند.

SELECT در مقابل SET

یک دستور Select می تواند مقادیری را به متغیرهای مختلف اختصاص دهد.

DECLARE @EKey varchar(10)

,Fname varchar(50)@,

;Lname varchar(50)@

‘select @EKey =’1,

‘Fname=’Arash@,

‘Lname=’Mehrabi@

در Set باید بابت هر مقدارجداگانه اختصاص دهیم.

DECLARE @EKey varchar(10)

Fname varchar(50)@,

;Lname varchar(50)@,

‘SET @EKey =’1

‘SET @Fname=’Arash

‘SET @Lname=’Mehrabi

Select سریعتر از SET است.

فهرست ستون را در عبارت Insert قرار دهید

بهترین روش : استفاده از لیست ستون ها در عبارت درج ما.

INSERT INTO TestSSIS.dbo.emp Values(10,’Arash’)

INSERT INTO TestSSIS.dbo.emP(ID, Name) Values(10,’Arash’)

این به ما اجازه می دهد تا قالب جدول خود را با اضافه کردن یا حذف ستون ها بدون شکستن عبارت Insert خود تغییر دهیم.

از فراخوانی تابع اسکالر در لیست ستون و عبارت where اجتناب کنید

اگر تابع اسکالر داده‌ها را برمی‌گرداند، تابع اسکالر را در لیست ستون‌ها در دستور Select یا عبارت Where اجرا نکنید.

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

• استفاده از Join
• InlineTable Value Function
• متغیر جدولی
• View
• پروسیجر

تراکنش را تا حد امکان کوتاه نگه دارید

از تراکنشهای تو در تو خودداری کنید.

• فقط داده های ذحیره شده را از خارجی ترین تراکنش های خارجی commit کنید.
• rollback تمام تراکنشها، هم درونی ترین و هم بیرونی ترین تراکنش هسته ای.
• طول تراکنش بر مسدود شدن و بن بست تاثیر می گذارد.
• Exclusive Lock تا پایان تراکنش آزاد نمی شود.

تراکنش باید تا حد امکان کوتاه باشد.

سعی کنید از Deadlock جلوگیری کنید

• همیشه در همه پروسیجرها و تریگرها به طور مداوم به جداول به یک ترتیب دسترسی داشته باشید.
• تراکنشهای خود را تا حد امکان کوتاه نگه دارید. در حین تراکنش تا حد امکان اطلاعات کمتری را طلب کنید.
• هرگز، هرگز منتظر ورودی کاربر در میانه تراکنش نباشید.

از استفاده از DDL خودداری کنید

استفاده از دستورات DDL در داخل پروسیجرها نیز شانس استفاده مجدد از پلن اجرایی را کاهش می دهد. جدول temp را در حلقه ایجاد نکنید. cursorهایی را که به جدول موقت اشاره می کنند، تعریف نکنید.

از استفاده از  Cusors SQL Server خودداری کنید

Cursor از منابع زیادی برای پردازش سربار استفاده می کند تا موقعیت رکورد فعلی را در یک RecordSet حفظ کند و این کارایی را کاهش می دهد. اگر لازم است رکوردها را یک به یک در یک حلقه پردازش کنیم، باید از عبارت WHILE استفاده کنیم. در صورت امکان، باید رویکرد مبتنی بر Cursor را با رویکرد مبتنی بر SET جایگزین کنیم. زیرا موتور SQL Server برای انجام عملیات مبتنی بر SET بسیار سریع طراحی و بهینه شده است. مجدداً، لطفاً توجه داشته باشید که Cursor نیز نوعی حلقه WHILE است. ما می توانیم Cursor را با استفاده از متغیر جدولی یا جدول موقت جایگزین کنیم.

استفاده از cursorها، برنامه را کندتر می کند زیرا در مقابل sql مبتنی بر SET کار می کند. سعی کنید از جدول موقت یا متغیر جدولی با ستون identity استفاده کنید و ده بار تمام جداول را با استفاده از حلقه‌های while و یک شمارنده حلقه‌ای که با ستون identity نگاشت می‌شود، تکرار کنید.

سعی کنید از UNION برای اجرای عملیات “OR” استفاده کنید

سعی کنید به جای OR از Union استفاده کنید. UNION برای ترکیب مجموعه نتیجه دو پرس و جو متمایز. اگر به نتیجه متمایز نیاز نیست بهتر است از UNION ALL استفاده کنید. UNION ALL سریعتر از UNION است زیرا برای یافتن مقادیر متمایز مجبور نیست مجموعه نتایج را مرتب کند.

یک پروسیجر بسیار بزرگ را به چندین پروسیجر جزئی تقسیم کنید

پروسیجر زمانی دوباره کامپایل می شود که هر گونه تغییر ساختاری در جدول یا ویووی مورد اشاره پروسیجر ایجاد شود (برای مثال عبارت ALTER TABLE)، یا زمانی که تعداد زیادی درج، به روز رسانی یا حذف به جدولی که توسط آن ارجاع داده شده است، انجام شود. بنابراین، اگر یک پروسیجر بسیار بزرگ را به چندین زیرپروسیجر تقسیم کنیم، این احتمال وجود دارد که تنها یک فرآیند فرعی مجدداً کامپایل شود، در حالی که برای سایر زیرپروسیجرها این رخ نمیدهد.