www.dntips.ir
مطالب ۳۶۹۴
نویسندگان ۲۷۶
گروه‌های مطالب ۱۰۲۴
نقشه‌های راه ۱۱۹
دوره‌ها ۱۴
اشتراک‌ها ۱۷۹۱۴
عنوان:

‫پروسیجرها و شنود پارامترها در SQL Server - قسمت سوم


نویسنده: امیر هاشم زاده
تاریخ: ۱۳۹۵/۰۹/۰۲ ۲۳:۵۰
آدرس: www.dntips.ir
در مطلب قبلی راجع به اثرات منفی شنود پارامترها، در صورت عدم توجه به آن‌ها بیان شد و در این مطلب قصد داریم به راه‌های کاهش اثرات منفی و مقابله با آن‌ها بپردازیم:
نکته: راه‌های اشاره شده برای مقابله با شنود پارامترها برای تمام شرایط قابل استفاده نیستند.


راه حل اول: استفاده از دستور With Recompile

مشکل شنود پارامتر این است که در اولین اجرای پروسیجر، پلن اجرایی را بر اساس پارامترهای ارسالی اولیه ایجاد می‌کند. راه حل غلبه بر این مشکل، کامپایل مجدد پروسیجر، بعد از هر اجرای آن است. بهمین جهت از دستور WITH RECOMPILE هنگامیکه قصد ایجاد پروسیجر را دارید استفاده نمایید. مانند کد زیر: 

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
WITH RECOMPILE
AS 
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @BeginDate AND @EndDate;
مجددا 2 آزمایش اشاره شده در مطلب قبلی (اشاره شده در زیر) را تکرار می‌کنیم.
DBCC FREEPROCCACHE;
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '2005-01-01',  
  @EndDate  = '2005-01-03';
  
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '1999-01-01',  
  @EndDate  = '1999-12-31';
هنگامیکه کد بالا را اجرا نمایید، فراخوانی اول، عملیات Index Seek و فراخوانی دوم، Index Scan را موجب خواهد شد. نقص این روش، کامپایل مجدد با هر بار اجرای پروسیجر است که باعث تحمیل سربار اضافه‌ای می‌شود.


راه حل دوم: غیر فعال نمودن شنود پارامتر

روش دیگر برطرف کردن مشکلات مرتبط با شنود پارامتر، غیر فعال کردن آن است. البته منظور از غیر فعال کردن، غیر فعال نمودن گزینه‌ای در بانک اطلاعاتی نیست؛ بلکه با تغییر متن و نحوه‌ی اجرا، می‌توان شنود را غیر فعال نمود. در کد زیر با تغییر نحوه اجرای پروسیجر، قابلیت شنود پارامتر غیر فعال شده است:

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
WITH RECOMPILE
AS 
DECLARE @StartDate DATETIME;
DECLARE @StopDate DATETIME;
SET @StartDate = @BeginDate;
SET @StopDate = @EndDate;
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate;
برای غیرفعال نمودن، تمام کاری که انجام شده، نحوه استفاده از پارامترهای ارسالی تغییر داده شده است. در کد بالا دو متغیر محلی با نامهای StartDate@ و EndDate@ ایجاد شده‌است. پارامترهای ارسالی درون متغیرهای محلی ذخیره می‌شوند و سپس از متغیرهای محلی در شرط between استفاده خواهد شد. بدین صورت شنود غیر فعال می‌شود. دلیل غیر فعال شدن شنود این است که بهبود دهنده (optimizer) قادر به شناسایی مقادیر پارامترهای ورودی در بدنه دستور Select نمی‌باشد. بدلیل عدم رهگیری محل مصرف مقادیر پارامترهای ارسالی توسط اس کیو ال سرور، بهبود دهنده یک پلن جنریک را براساس اطلاعات آماری ایجاد خواهد کرد.


راه حل سوم: ایجاد چند نوع پروسیجر

راه دیگر، ایجاد پروسیجرهای متفاوت برای پارامترهایی با کاردینالیتی متفاوت است. به‌عبارت دیگر، دسته بندی پارامترهای ارسالی و ایجاد پروسیجرهایی خاص همان دسته. در مثال‌های این سری از مطالب، دو دسته پارامتر 1) بازه زمانی کوتاه، مثلا چند روز و 2) بازه زمانی بلند، مثلا ماهیانه وجود داشت که می‌توانید 2 دسته پروسیجر را یکی برای بازه‌های روزانه و دیگری برای بازه‌های زمانی ماهیانه ایجاد نمایید. 

CREATE PROC [dbo].[DisplayBillingInfoNarrow]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS 
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @BeginDate AND @EndDate;  
GO
CREATE PROC [dbo].[DisplayBillingInfoWide]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS 
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @BeginDate AND @EndDate;  
GO  
DROP PROCEDURE [dbo].[DisplayBillingInfo];
GO  
CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS 
IF DATEDIFF(DD,@BeginDate, @EndDate) < 4
  EXECUTE DisplayBillingInfoNarrow @BeginDate, @EndDate
ELSE
  EXECUTE DisplayBillingInfoWide @BeginDate, @EndDate
GO
در کد بالا، دو گروه پروسیجر (برای بازه زمانی کوتاه و بلند) به‌همراه یک پروسیجر تصمیم گیرنده جهت تشخیص استفاده از پروسیجر مناسب، بر اساس پارامترهای ورودی ایجاد شده است. یکی از مزایای این روش استفاده پروسیجر از پلن اجرایی مناسب، فارغ از پارامترهای ارسالی خواهد بود. البته نگهداری کد در این روش به مرور زمان، کمی دشوار و سخت خواهد شد.
مطالب مشابه