چند نکته برای بهینه سازی رویه های ذخیره شده

در این پست نکات کوچیکی  لیست شده که با توجه به اونا میتونیم سرعت اجرا شدن SP هامون رو افزایش بدیم


NOCOUNT رو فعال کنیم


همونطور که میدونید اسکیوال سرور با هر دستور Select و دستورات DML تعداد رکوردهای رو که تحت تاثیر اون دستور قرار گرفتن رو برمیگردونه این تعداد رکورد تحت تاثیر قرار گرفته برای دیباگ کردن کد میتون کارآمد باشه ولی بعد اون بی استفاده میشه با توجه به اینکه SP میتونه شامل چندین دستور مختلف باشه غیر فعال کردن این ویژگی میتونه کارائى رو افزایش بده


CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--دستورات در این قسمت
SELECT column1 FROM dbo.TblTable1
-- فعال کردن ویژگی تعداد رکورد تحت تاثیر قرار گرفته
SET NOCOUNT OFF;
GO 

استفاده از اسکیما (schema) در کنار نام اشیاء


نام اسکیما باید با نام SP و تمام اشایی که داخل SP به آنها ارجاع شده استفاده بشه.این کار باعث مشیه اسکیوال مستقیما  پلن کامپایل شده(complied plan) رو پیدا و استفاده کنه بجای اینکه دنبال اون اشیاء تو اسکیما های دیگه بگرده و در صورت وجود از پلن کامپایل شده(complied plan) استفاده کنه.این جستجوها برا یافتن اشیاء در دیگر اسکیما ها و تصمیم گیری ها ممکنه منجر به COMPILE lock و  در نتیجه باعث افت کارائى .


SELECT * FROM dbo.MyTable -- روش صحیح
SELECT * FROM MyTable -- روش غلط

--نحوه فراخوانی

EXEC dbo.MyProc -- روش صحیح
EXEC MyProc -- روش غلط

استفاده نکردن از  “_SP” در اول نام SP ها !!!


اگر SP شما با این روش نام گذاری بشه اسکیوال ابتدا دیتابیس Master رو برای یافتن این SP جستجو میکنه و بعد دیتابیس جاری رو واین باعث افت کارائى  میشه و حتی اگه با همین نام SP ی در دیتابیس Master باشه ...


جالبه که اکثر SP های شرکت ما با این روش نامگذاری شدن !!!


استفاده بهینه از تابع EXISTS


برای چک کردن موجود بودن یک رکورد در دیگر جداول ما از  دستور IF EXISTS استفاده میکنیم .همونطور که میدونید این تابع یک دستور SQL رو بعنوان پارامتر میگیره و اگه اون دستور تنها 1 رکورد برگردونه خروجیش True میشه بنابراین برای بحداقل رسوندن پردازش دادها و درنتیجه افزایش کارائى این تابع رو به شکل زیر استفاده کنید


IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

استفاده از SP ی sp_executesql بجای دستور EXECUTE


پلن کامپایل شده(complied plan) هر Dynamic 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 بزاریم (در دستور 2وم)اسکیوال از پلن کامپایل شده دستور اول استفاده نمیکنه برای حل این مشکل از sp_executesql  استفاده میکنیم


DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
EXEC (@Query)

استفاده نکردن از کرسر ها (cursors)


کرسرها برای نگهداری وضعيت رکورد جاری از منابع زیادی استفاده میکنند.ما معمولا از کرسر برای پیمایش يکى يکى رکوردها استفاده میکنم در چنین مواقعی بهتره از دستور WHILE استفاده کنیم .تا جایی که ممکنه از روشهای SET-based بجای cursor-based استفاده کنید چون موتور اسکیوال برای اجرای سریع  دستورات SET-based بهینه شده


کوتاه کردن زمان تراکنش ها


همونطور که میدونید تراکنش ها باعث قفل شدن میشن تازمانی که تراکنش تموم بشه ازینرو تراکنش های طولانی باعث قفل شدن طولانی و قفل شدن طولانی تبدیل به انسداد (blocking) و حتی در بعضی مواقع انسداد (blocking) تبدیل به بن بست(deadlock) میشه بنابراین برای اجرای سریعتر و انسداد کمتر تراکنش ها رو تاحد ممکن کوتاه کنید


استفاده نکردن از جداول موقت(temporary) ودستورات  DDL در SP ها


استفاده از جداول موقت و همچنیا استفاده از دستورات DDL درون SP شانس استفاده از پلن کامپایل شده(complied plan) رو کم میکنه


استفاده از گزینه  WITH RECOMPILE هنگام ایجاد کردن SP های که شما میدونید کوئری داخل اون در هربار اجرا از SP فرق خواهد کرد


با این کار باعث میشم اسکیوال به سراق پلن کامپایل شده(complied plan) نره و SP رو در هر بار اجرا کامپایل کنه و با اجرا نکردن پلن کامپایل شده(complied plan) اشتباه میتونیم کارائى رو افزایش بدیم 


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


قبل از اسکیوال سرور 2005 برای هندل کردن خطاها باید کد زیادی نوشته میشد و در نتیجه منابع و وقت  زیادی رو صرف میکرد ولی از  اسکیوال سرور 2005 به بعد با اومدن روش TRY-Catch خیلی ساده میتونیم خطاها رو هندل  کنیم


BEGIN TRY
--دستورات اینجا
END TRY
BEGIN CATCH
--دستورات هندل کردن خطا اینجا
END CATCH

منابع + و +

نظرات

  1. خیلی خیلی مفید بود : اما همان طور که خودتون هم اشاره کردید متاسفانه در کارهای شرکت ها کمتر مورد توجه قرار میگیره و هنوز روش های سنتی و جا افتاده به غلط بیشتر استفاده میشود !

    پاسخحذف
  2. عالی بود. ممنون

    پاسخحذف

ارسال یک نظر

پست‌های معروف از این وبلاگ

مقدمه ای بر RavenDB – قسمت دوم

مقدمه ای بر RavenDB – قسمت سوم

lnav ابزاری بسیار کاربردی برای پیمایش لاگ ها در لینوکس و البته مک