کارایی Stored procedure را در SQL Server افزایش دهید

1397/05/10
  1. از SET NOCOUNT ON استفاده کنید

    پس از اجرای Query یا عملیات مختلف تغییر محتوا، SQL Server جهت اطلاع رسانی پیام هایی را ارسال می کند. در چنین حالتی در یک Procedure ممکن است دارای عملیات این چنین بوده و یا یک حلقه SQL Server منجر نمایش چنین پیام هایی بشود که این موضوع منجر به افزایش ترافیک شبکه خواهد شد. از چنین پیام هایی به سادگی و با استفاده از دستور SET NOCOUNT ON می توان جلوگیری کرد و نتیجتاً با کاهش ترافیک شبکه، کارایی را افزایش داد.

  1. از اسامی به شکل کامل استفاده کنید

    نام کلیه اشیا را به شکل کامل database.schema.objectname به کار ببرید. زمانی که Stored procedure به این شکل فراخوانی می شود، اگر چه به عنوان یک عامل بسیار بزرگ در افزایش کارایی محسوب نمی شود اما به عنوان بهترین روش فراخوانی عنوان شیء است که از جستجوی عنوان Stored procedure در میان تمامی Schemaها جلوگیری خواهد کرد. کلیه اشیای داخل Procedure نیز می بایست به شکل schemaname.objectname ارجاع شوند.

استفاده از sp_executesql نه تنها امکان استفاده چند باره از حافظه نهان را می دهد بلکه از SQL Injection نیز جلوگیری می نماید.
 
DBCC FREEPROCCACHE
GO
Declare
              @dynamic_sql varchar(max),
              @salesorderid int
SET @salesorderid=43660
 
SET @dynamic_sql=' SELECT * FROM Sales.SalesOrderDetail where SalesOrderID='
              + CAST(@salesorderid AS VARCHAR(100))
EXECUTE(@dynamic_sql)
 
کوئری بالا، یک کوئری پویا را با استفاده از EXECUTE اجرا می کند. نتیجه آنالیز حافظه نهان را ملاحظه فرمایید:


همینطور که در تصویر بالا قابل مشاهده است، 2 طرح برای 2 SalesOrderID وجود دارد. حال بیاییم همین موضوع را با استفاده از sp_execute استفاده کنیم.


DECLARE @dynamic_sql NVARCHAR(100)
SET @dynamic_sql = N'SELECT * FROM Sales.SalesOrderDetail where SalesOrderID=@salesorderid'
EXECUTE sp_executesql @dynamic_sql, N'@salesorderid int', @salesorderid = 43661
 
کوئری بالا از sp_executesql برای اجرای یک کوئری پویا برای 2 مقدار مختلف SalesOrderID استفاده می کند. نحوه استفاده از حافظه نهان را با هم ببینیم.

همین طور که قابل مشاهده است، تنها یک طرح برای حافظه نهان برای 2 مقدار مختلف SalesOrderID استفاده شده است.
  1. از sp_executesql به جای اجرای کوئری های پویا استفاده کنید
  1. استفاده از IF EXISTS و SELECT

    شرط IF EXISTS برای بررسی وجود یک رکورد، شیء و ... استفاده می شود، بنابراین وقتی تنها وجود یا عدم وجود مطرح است می توانید به جای IF EXISTS(SELECT 1 from mytable) از IF EXISTS(Select * from mytable) استفاده نمایید. این موضوع به سادگی سرعت اجرای کوئری را ارتقا خواهد داد.

  1. از نام گذاری Stored procedureها به شکل sp_precedurename بپرهیزید

    زمانی که نام Stored procedure با sp_ شروع می شود، ابتدا SQL Server به جستجو در پایگاه داده Master خواهد پرداخت. این موضوع کمی به کارایی آسیب زده و در مواردی که اسامی در Master و پایگاه داده کاربر یکی باشد سیستم را دچار مشکل خواهد کرد.

  1. تا حد ممکن از کوئری های Set based استفاده نمایید

    دید Set based نقطه مقابل دید Procedural است، اما ماهیتاً T-SQL زبانی Set based است و نمی توان از حلقه ها به شکل مورد نظر به راحتی استفاده کرد. نشانگر ها و حلقه های While فقط زمانی توصیه می شوند که طراحی Set based بسیار زمانبر و مشکل  باشد.

  1. تراکنش ها را کوتاه کنید

    هر چقدر زمان تراکنش بیشتر شود، حالت قفل اطلاعات طولانی تر می شود. این موضوع ممکن است امور دیگر را به مشکل انداخته و با Deadlock مواجه شوید.
     

User Avatar
نویسنده : علی هریسچیان