چند نکته برای بهینه سازی رویه های ذخیره شده
در این پست نکات کوچیکی لیست شده که با توجه به اونا میتونیم سرعت اجرا شدن 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
خیلی خیلی مفید بود : اما همان طور که خودتون هم اشاره کردید متاسفانه در کارهای شرکت ها کمتر مورد توجه قرار میگیره و هنوز روش های سنتی و جا افتاده به غلط بیشتر استفاده میشود !
پاسخحذفعالی بود. ممنون
پاسخحذف