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

مقدمه

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

 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 شاهد تأثیر بسزایی در کوئری شدید که قطعاً در تعداد رکوردهای بالاتر به خوبی خود را نشان می دهد. یادگیری اصول و نکاتی به ظاهر کوچک به این شکل است که شما را در بین دوستان ، مجموعه ،شرکت و … متمایز می کند.