تغییرات کوچک = تأثیرات بزرگ [ویرایش و توابع رتبه بندی]

تغییرات کوچک = تأثیرات بزرگ [ویرایش و توابع رتبه بندی]

نوشته شده توسط: محمد حسین عبدالهی
۲۶ مرداد ۱۳۹۴
زمان مطالعه: 19 دقیقه
۰
(۰)

مقدمه

کمی پیشرفته تر / تغییرات کوچک = تأثیرات بزرگ  قسمت اول : ویرایش و توابع رتبه بندی
جدول زیر را در نظر بگیرید :

 CREATE TABLE [dbo].[TblPersonel2](
[CodeMeli] [char](10) NOT NULL Primary Key ,
[Name] [nvarchar](20) NULL,
[Family] [nvarchar](20) NULL
)

برای این جدول چند رکورد را به این صورت وارد می کنیم :

INSERT [dbo].[TblPersonel] ([CodeMeli], [Name], [Family], [RowNumber]) VALUES (N'046       ', N'الیاس', N'نظری', ۱)
GO
INSERT [dbo].[TblPersonel] ([CodeMeli], [Name], [Family], [RowNumber]) VALUES (N'052       ', N'نغمه', N'جلالی', ۲)
GO
INSERT [dbo].[TblPersonel] ([CodeMeli], [Name], [Family], [RowNumber]) VALUES (N'092       ', N'محمدحسین', N'عبدالهی', ۳)
GO
INSERT [dbo].[TblPersonel] ([CodeMeli], [Name], [Family], [RowNumber]) VALUES (N'095       ', N'نگین', N'امینی', ۴)
GO

و با دستور Select نتیجه به این صورت قابل مشاهده می باشد :

Select CodeMeli , Name , Family
From TblPersonel

فرض کنید می خواهیم ستونی دیگر به نام RowNumber در خروجی به ما نمایش داده شود تا بتوانیم با شماره ای که به هر ردیف اختصاص داده می شود به لیست مراجعه کنیم. ( برای یاد آوری خودم شماره ردیف فعلی مربوط به SQL Server هست)
طبیعتاً بهترین ایده در این حالت استفاده از توابع رتبه بندی است ، در اینجا توضیحاتی رو درباره توابع رتبه بندی قرار دادم.

برای ایجادستونی که شماره ردیف ها راتولید کنیم،ازتابع Row_Number() به این صورت استفاده می کنیم :

Select CodeMeli , Name , Family ,
        Row_Number() over (order by CodeMeli) as 'Row'
From TblPersonel

خروجی

بنابراین درهنگام اجرا ستونی ایجاد و شماره ردیف ها درآن نمایش داده می شود ، حال می خواهیم یک ستون مجزا (فقط برای شماره گذاری ردیفها) در ساختار جدول اضافه کنیم :

 Alter Table TblPersonel Add RowNumber int

در این مثال هدف این است تا شماره مربوطبه هر ردیف در RowNumber قرار بگیرد.
تذکر : این صرفاً یک مثال می باشد واحتمال اینکه تعداد زیادی از دوستان با چنین ایده ای مخالف باشند طبیعی است.

کمی پیشرفته تر

برای اینکه بتوانیم به RowNumberمقداری بدهیم بایستی ازدستور Update استفاده کنیم. Syntax اصلی دستور Update البته خلاصه  به این صورت می باشد :

[ with ]
Update
Set
[output]
[from]
[where]
[option]

 برای ویرایش این جدول به کمک توابع رتبهبندی می توانیم از ساختار With که در Update ارائه شده است به این صورت استفاده می کنیم :

 With prsl as
(
Select CodeMeli
, Row_Number() over (order by CodeMeli) as 'Row'
From TblPersonel
)
Update TblPersonel
Set RowNumber = prsl.Row
From prsl
Where prsl.CodeMeli = TblPersonel.CodeMeli

 

که خروجی مثل قبل هست با این تفاوت که شماره ردیف در داخل جدول (ویرایش) ثبت شده است.

 Select CodeMeli , Name , Family , RowNumber
from TblPersonel
 

به کمک دستور Statistics IO  کمی پیشرفته به خروجی دستور نوشته شده می پردازیم. ابتدا Statistics IO را فعال می کنیم :

 Set Statistics IO ON

 به کمک این دستور می توانیم آمار مناسب و مفیدی را از روند اجرای کوئری به دست بیاوریم تا علت کُند بودنِ احتمالی یک کوئری به دست بیاید.
سپس مجدد دستور قبل را اجرا می کنیم :

With prsl as
(
Select CodeMeli
, Row_Number() over (order by CodeMeli) as 'Row'
From TblPersonel
)
Update TblPersonel
Set RowNumber = prsl.Row
From prsl
Where prsl.CodeMeli = TblPersonel.CodeMeli

 حال آمار و اطلاعات موجود در زبانه Messageرا مشاهده و بررسی می کنیم :

Table ‘TblPersonel’. Scan count 1, logical reads 18, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

سوال :WorkTable چیست؟

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

نکته اول

هر چه قدر آمار این قسمت بالاتر باشد به ضررمان و ضرر طراح بانک اطلاعاتی و کاربران تمام می شود ، از سرعت کاسته می شود و کوئری های اجرا شده کُندتر جواب میدهند!

نکته دوم

با توجه به اینکه در جدول ایجاد شده از انواع LargeObject همانند Nvarchar(max) , NText , VarBinary(max) استفاده نشده است ردیف های ۶-۴ بی تأثیر و ۰ هستند و می توانیم در تحلیل و بررسی کوئری در نظر نگیریم.

نکته سوم

logical read  که در حقیقت خواندن Pageهای از Buffer Pool (فقط جداول DiskBase).

نکته چهارم

تعداد pageهای خوانده شده از روی Disk (داده های واکشی شده از  NTFS) را در قسمت physical read می توانیم مشاهده کنیم که در اینجا ۰ است.

نکته پنجم 

read-ahead reads باعث می شود مکانیزم کار سریع تر شود و اطلاعاتی که در حین خواندن هد هنوز نوبت به واکشی آن ها نشده را Detect می کند و این نوبت را نگه می دارد تا به مرحله و زمان استفاده برسد. به این ترتیب عملیات IO سریع تر انجام می شود.
همانطور که در نکته اول به این موضوع اشاره داشتیم بالا بودن آمار این جدول به ضررمان تمام می شود ، حال چگونه میتوانیم آمار این جدول را کاهش دهیم ؟

 تغییرات کوچک = تأثیرات بزرگ

حال با اعمال تغییرِ بسیار کوچکی تأثیر بسزایی در روند اجرای کوئری پدید می آوریم.
به کوئری قبل بر می گردیم :

 With prsl as
(
Select CodeMeli
, Row_Number() over (order by CodeMeli) as 'Row'
From TblPersonel
)
Update TblPersonel
Set RowNumber = prsl.Row
From prsl
Where prsl.CodeMeli = TblPersonel.CodeMeli

و فقط به جای ویرایش جدول TblPersonel، یعنی دستور :

Update TblPersonel

 همان CTEایجاد شده را ویرایش می کنیم ، یعنی prsl :

Update prsl

و همچنین ماده Where را حذف می کنیم !

بنابراین دستور به این صورت تبدیل می شود :

 With prsl as
(
Select CodeMeli
, Row_Number() over (order by CodeMeli) as 'Row'
, RowNumber
From TblPersonel
)
Update prsl
Set RowNumber = prsl.Row
From prsl

حال با توجه به فعال بودن Statistics در مراحل قبلی ( اگر off نشده باشد! ) آمار را مجدد بررسی می کنیم :

Table 'TblPersonel'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

سوال :چرا WorkTable حذف شده است ؟ در این دستور اگر با دقت نگاه کنید فقط یک جدول یعنی TblPersonel که در بدنه CTE نگاشته شده است مورد استفاده قرار می گیرد ، که عنوان آن توسط CTE برای SQL با نام prsl معرفی شده ، اما در کوئری قبل علاوه بر prsl که برای عنوان CTE استفاده شده بود همچنین ویرایش روی جدول TblPersonel انجام شده بود ، بنابراین SQL هنگام کارعنوان WorkTable را به آن اختصاص می دهد و آماری برای آن در نظر می گیرد همانند سایر جداول!

نکته

مقدار logical reads از ۱۸ به ۲ کاهش یافت که باعث تسریع سرعت میشود. همچنین خبری هم از جدول WorkTable که خود شامل ۱۱عدد page خوانده شده (logical reads) می باشد دیگر نیست!

نتیجه 

با تغییری کوچک تنها با تغییر نام جدول و حتی حذف Where از دستور Update شاهد تأثیر بسزایی در کوئری شدید که قطعاً در تعداد رکوردهای بالاتر به خوبی خود را نشان می دهد. یادگیری اصول و نکاتی به ظاهر کوچک به این شکل است که شما را در بین دوستان ، مجموعه ،شرکت و … متمایز می کند.

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
معرفی نویسنده
محمد حسین عبدالهی
مقالات
1 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
محمد حسین عبدالهی
پروفایل نویسنده
title sign
دیدگاه کاربران

    • با سلام و خسته نباشید خدمت شما
      البته من در مورد ranking function ها خیلی مطلب خوندم اما این مقاله هم برای من خیلی جالب و خوب بود
      واقعا از بابت این مقاله بسیار ممنونم.
      با تشکر از شما

    • با سلام و خسته نباشید خدمت شما
      البته من در مورد ranking function ها خیلی مطلب خوندم اما این مقاله هم برای من خیلی جالب و خوب بود
      واقعا از بابت این مقاله بسیار ممنونم.
      با تشکر از شما

    •  خوب بود ممنون از لطفتون

    •  سلام
      بسیار عالی و کاربردی بود
      با تشکر از شما

    • سلام
      واقعا ممنونم از مقالتون
      شاید اسم این مطلب رو بزاریم  in memeory update هم معنی بده
      من که همه دستورات رو تست کردم و Execution plan رو هم به پیشنهاد آقای طاهری عزیز دیدم
      واقعا خیلی خیلی فرق میکنه

    • سلام

      بسیار عالی بود و به نکته بسیار خوبی اشاره کرده اید.
      با تشکر از شما.
    •  سلام
      مقاله شما خوب بود و واقعا برای افرادی در سطح بنده بسیار عالی هست واقعا نکات جالب و ریزی را مد نظر قرار دادین…

    • جناب رحمتی به عقیده من بعد از اینکه چندین مجموعه رو از استاد طاهری دیدم در هر کوئری بایستی اولین موردی که بعد از ایندکس گذاری ها رعایت میشه دقت و توجه به همین مبحث Statistics بایستی باشه.
      ظاهراً کوچک به نظر می رسه اما واقعا تأثیرات بسزایی داره و کامل با صحبت شما موافقم در حالت عادی و داده های کم شاید خیلی توفیقی نکنه اما با عادت کردن به سنجش جدول آمار Statistics حتی در داده های کم در داده های زیاد کمتر با مشکل روبرو خواهیم شد.

    •  با سلام 

      از مقاله شما سپاسگذارم . بنظر بنده اینطور میرسد که اینگونه موارد در سطحی مفید است که با حجم Updateهای  زیادی در بانگ اطلاعاتی مواجه باشیم و الا در حالت عادی  update کردن  با این روش  خیلی توفیقی ندارد. 
    • سلام
       حتماً در مقالات بعدی با اطلاعاتی که دارم (مخصوصاً فیلم های آموزشی شما) موارد Execution Plan رو هم مورد تحلیل قرار می دم.

  • 1
  • 2
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
وبینار رایگان SQL Server؛ مسیری به سوی فرصت‌های شغلی بی‌شمار       پنج‌شنبه 30 فرودین ساعت 15
ثبت نام رایگان
close-image