خانه SQL Server تغییرات کوچک = تأثیرات بزرگ [ویرایش و توابع رتبه بندی] SQL Server دستورات SQL نوشته شده توسط: محمد حسین عبدالهی تاریخ انتشار: ۲۶ مرداد ۱۳۹۴ آخرین بروزرسانی: ۲۶ اردیبهشت ۱۴۰۲ زمان مطالعه: 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 شاهد تأثیر بسزایی در کوئری شدید که قطعاً در تعداد رکوردهای بالاتر به خوبی خود را نشان می دهد. یادگیری اصول و نکاتی به ظاهر کوچک به این شکل است که شما را در بین دوستان ، مجموعه ،شرکت و … متمایز می کند. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 1 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده محمد حسین عبدالهی معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مسعود طاهری ۲۷ / ۰۵ / ۹۴ - ۰۱:۳۳ جناب عبدالهی مقاله شما خوب بود. یک پیشنهاد در سایر مقالاتی که ان شاا… قرار است قرار بدهید ترجیحا Execution Plan کوئری را هم مورد تحلیل قرار دهید به امید مقالات بیشتر از شما دوست عزیز متشکرم پاسخ به دیدگاه حمید قلیپور ۲۷ / ۰۵ / ۹۴ - ۱۲:۳۲ با سلام و احترام جناب عبدالهی باید بگم مقالتون بسیار حساب شده بود حتی به نکات مقاله نیز اشاره کرده بودید؛ با این که از سطح اطلاعات بنده مطالب بالاتر بود اما بسیار روشن مطالب بیان کردید ممنونم. با تشکر پاسخ به دیدگاه 1 2