خانه 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 Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز ۰۳ مهر SQL Server روش استفاده از Policy-Based Management در امنیت SQL server تیم فنی نیک آموز ۲۶ شهریور SQL Server سرویس SQL Server Browser چیست؟ آشنایی با نحوه راه اندازی و کاربردها تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ ha_zarabi_vb6@outlook.com ۰۷ / ۰۵ / ۹۶ - ۰۵:۲۷ با سلام و خسته نباشید خدمت شما البته من در مورد ranking function ها خیلی مطلب خوندم اما این مقاله هم برای من خیلی جالب و خوب بود واقعا از بابت این مقاله بسیار ممنونم. با تشکر از شما پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۰۷ / ۰۵ / ۹۶ - ۰۵:۲۷ با سلام و خسته نباشید خدمت شما البته من در مورد ranking function ها خیلی مطلب خوندم اما این مقاله هم برای من خیلی جالب و خوب بود واقعا از بابت این مقاله بسیار ممنونم. با تشکر از شما پاسخ به دیدگاه m ۰۸ / ۰۶ / ۹۴ - ۰۲:۱۶ خوب بود ممنون از لطفتون پاسخ به دیدگاه علی عبدیان ۰۱ / ۰۶ / ۹۴ - ۰۲:۳۶ سلامبسیار عالی و کاربردی بودبا تشکر از شما پاسخ به دیدگاه عباس لایقی ۰۱ / ۰۶ / ۹۴ - ۱۰:۱۲ سلام واقعا ممنونم از مقالتونشاید اسم این مطلب رو بزاریم in memeory update هم معنی بده من که همه دستورات رو تست کردم و Execution plan رو هم به پیشنهاد آقای طاهری عزیز دیدم واقعا خیلی خیلی فرق میکنه پاسخ به دیدگاه فرشید علی اکبری ۲۸ / ۰۵ / ۹۴ - ۰۷:۵۲ سلام بسیار عالی بود و به نکته بسیار خوبی اشاره کرده اید. با تشکر از شما. پاسخ به دیدگاه مجتبی شهریور ۲۸ / ۰۵ / ۹۴ - ۰۱:۴۲ سلاممقاله شما خوب بود و واقعا برای افرادی در سطح بنده بسیار عالی هست واقعا نکات جالب و ریزی را مد نظر قرار دادین… پاسخ به دیدگاه محمدحسین عبدالهی ۲۷ / ۰۵ / ۹۴ - ۱۱:۳۴ جناب رحمتی به عقیده من بعد از اینکه چندین مجموعه رو از استاد طاهری دیدم در هر کوئری بایستی اولین موردی که بعد از ایندکس گذاری ها رعایت میشه دقت و توجه به همین مبحث Statistics بایستی باشه.ظاهراً کوچک به نظر می رسه اما واقعا تأثیرات بسزایی داره و کامل با صحبت شما موافقم در حالت عادی و داده های کم شاید خیلی توفیقی نکنه اما با عادت کردن به سنجش جدول آمار Statistics حتی در داده های کم در داده های زیاد کمتر با مشکل روبرو خواهیم شد. پاسخ به دیدگاه مهران رحمتی ۲۷ / ۰۵ / ۹۴ - ۰۴:۵۳ با سلام از مقاله شما سپاسگذارم . بنظر بنده اینطور میرسد که اینگونه موارد در سطحی مفید است که با حجم Updateهای زیادی در بانگ اطلاعاتی مواجه باشیم و الا در حالت عادی update کردن با این روش خیلی توفیقی ندارد. پاسخ به دیدگاه محمدحسین عبدالهی ۲۷ / ۰۵ / ۹۴ - ۰۲:۳۴ سلام حتماً در مقالات بعدی با اطلاعاتی که دارم (مخصوصاً فیلم های آموزشی شما) موارد Execution Plan رو هم مورد تحلیل قرار می دم. پاسخ به دیدگاه 1 2