وضعیت فناوریهای مرتبط با دات نت از دیدگاه مرگ و زندگی!
5 دلیل برای استفاده از یک ابزار ORM
امنیت در LINQ to SQL (برای مابقی ORMها هم به همین صورت است)
مروری بر کدهای کلاس SqlHelper
$("#report-grid").kendoGrid({ // ... pageable: { previousNext: true, // default true numeric: true, // default true buttonCount: 5, // default 10 refresh: true, // default false input: true, // default false pageSizes: true // default false },
<!--https://github.com/loudenvier/kendo-global/blob/master/lang/kendo.fa-IR.js--> <script src="js/messages/kendo.fa-IR.js" type="text/javascript"></script>
var productsDataSource = new kendo.data.DataSource({ // ... group: { field: "IsAvailable" }, // ... });
$("#report-grid").kendoGrid({ // ... groupable: true, // allows the user to alter what field the grid is grouped by // ...
var productsDataSource = new kendo.data.DataSource({ //... aggregate: [ { field: "Name", aggregate: "count" }, { field: "Price", aggregate: "sum" } ] //... });
$("#report-grid").kendoGrid({ // ... columns: [ { field: "Name", title: "نام محصول", footerTemplate: "تعداد: #=count#" }, { field: "Price", title: "قیمت", footerTemplate: "جمع: #=kendo.toString(sum,'c0')#" } ] // ... });
<script type="text/x-kendo-template" id="priceTemplate"> #if( Price > 2490 ) {# <span style="background:brown; color:yellow;">#=kendo.toString(Price,'c0')#</span> #} else {# #= kendo.toString(Price,'c0')# #}# </script>
$("#report-grid").kendoGrid({ //... columns: [ { field: "Price", title: "قیمت", template: kendo.template($("#priceTemplate").html()), footerTemplate: "جمع: #=kendo.toString(sum,'c0')#" } ] //... });
<!--https://github.com/moment/moment/--> <script src="js/cultures/moment.min.js" type="text/javascript"></script> <!--https://github.com/jalaali/moment-jalaali--> <script src="js/cultures/moment-jalaali.js" type="text/javascript"></script>
$("#report-grid").kendoGrid({ //... columns: [ { field: "AddDate", title: "تاریخ ثبت", template: "#=moment(AddDate).format('jYYYY/jMM/jDD')#" } ] //... });
$("#report-grid").kendoGrid({ // ... toolbar: [ { template: kendo.template($("#toolbarTemplate").html()) } ] // ... });
<script> // این اطلاعات برای تهیه خروجی سمت سرور مناسب هستند function getCurrentGridFilters() { var dataSource = $("#report-grid").data("kendoGrid").dataSource; var gridState = { page: dataSource.page(), pageSize: dataSource.pageSize(), sort: dataSource.sort(), group: dataSource.group(), filter: dataSource.filter() }; return kendo.stringify(gridState); } </script> <script id="toolbarTemplate" type="text/x-kendo-template"> <a class="k-button" href="\#" onclick="alert('gridState: ' + getCurrentGridFilters());">نوار ابزار سفارشی</a> </script>
using System.Collections.Generic;
namespace Refactoring.Day2.ExtractMethod.After
{
public class Receipt
{
private IList<decimal> _discounts;
private IList<decimal> _itemTotals;
public decimal CalculateGrandTotal()
{
_discounts = new List<decimal> { 0.1m };
_itemTotals = new List<decimal> { 100m, 200m };
decimal subTotal = CalculateSubTotal();
subTotal = CalculateDiscounts(subTotal);
subTotal = CalculateTax(subTotal);
return subTotal;
}
private decimal CalculateTax(decimal subTotal)
{
decimal tax = subTotal * 0.065m;
subTotal += tax;
return subTotal;
}
private decimal CalculateDiscounts(decimal subTotal)
{
if (_discounts.Count > 0)
{
foreach (decimal discount in _discounts)
subTotal -= discount;
}
return subTotal;
}
private decimal CalculateSubTotal()
{
decimal subTotal = 0m;
foreach (decimal itemTotal in _itemTotals)
subTotal += itemTotal;
return subTotal;
}
}
}
using System.Collections.Generic;
namespace Refactoring.Day3.ExtractMethodObject.After
{
public class Receipt
{
public IList<decimal> Discounts { get; set; }
public decimal Tax { get; set; }
public IList<decimal> ItemTotals { get; set; }
public decimal CalculateGrandTotal()
{
return new ReceiptCalculator(this).CalculateGrandTotal();
}
}
}
using System.Collections.Generic;
namespace Refactoring.Day3.ExtractMethodObject.After
{
public class ReceiptCalculator
{
Receipt _receipt;
public ReceiptCalculator(Receipt receipt)
{
_receipt = receipt;
}
public decimal CalculateGrandTotal()
{
decimal subTotal = CalculateSubTotal();
subTotal = CalculateDiscounts(subTotal);
subTotal = CalculateTax(subTotal);
return subTotal;
}
private decimal CalculateTax(decimal subTotal)
{
decimal tax = subTotal * _receipt.Tax;
subTotal += tax;
return subTotal;
}
private decimal CalculateDiscounts(decimal subTotal)
{
if (_receipt.Discounts.Count > 0)
{
foreach (decimal discount in _receipt.Discounts)
subTotal -= discount;
}
return subTotal;
}
private decimal CalculateSubTotal()
{
decimal subTotal = 0m;
foreach (decimal itemTotal in _receipt.ItemTotals)
subTotal += itemTotal;
return subTotal;
}
}
}
sum = sum == 10 ? 0 : sum; return sum == check;
Console.WriteLine(sizeof(int)); Console.WriteLine(sizeof(char)); Console.WriteLine(sizeof(bool)); Console.WriteLine(sizeof(decimal)); Console.WriteLine(sizeof(float));
4 2 1 16 4
int a = 23; float b = 3.14f; Console.WriteLine(a.GetType()); Console.WriteLine(b.GetType());
System.Int32 System.Single
long obj1 = 356; long obj2 = 54; float obj3 = 234; Console.WriteLine(object.ReferenceEquals(obj1.GetType(), obj2.GetType())); Console.WriteLine(object.ReferenceEquals(obj1.GetType(), obj3.GetType()));
True False
//<data type> <variable name>; Int a;
//<data type> <variable name>=value; Int a=23; Int a;//declare تعریف a=23;//مقدار دهی اولیه initializing Int a=23;//تعریف و مقدار دهی در یک خط Int a,b,c=23;//تعریف چند متغیر و مقدار دهی در یک خط
int abc; long _abcd; float @abcd; bool main_button; decimal piValue; string firstName; string first_name; bool button55_on;
long _a.5bc5d; float @ab cd; decimal pi@Value; //استفاده از کلمات کلیدی سی شارپ که کامپایلر آنها را مجاز نمیداند bool class; string namespace; string string; int static;
< data type >? < variable name >= null; //syntax
int? a = null; //assigning null int? b = 55; //assigning null and a value var? c = 55 //it will give error
int? a = null; int? b = 22; Console.WriteLine(a.HasValue); //------------ Console.WriteLine(b.HasValue); Console.WriteLine(b.Value);
False True 22
test obj; //allocating reference on stack obj= new test(55);//allocating object on heap
int a = 55;//declare a and initialize int copya = a;//copya contains the copy of value a
test obj; obj=new test(23); test objCopy; objCopy = obj;
Round(Decimal) Rounds a decimal value to the nearest integral value.
Round(Double) Rounds a double-precision floating-point value to the nearest integral value.
Round(Decimal, Int32) Rounds a decimal value to a specified number of fractional digits.
Math.Round(3.44, 1); //Returns 3.4. Math.Round(3.45, 1); //Returns 3.4. Math.Round(3.46, 1); //Returns 3.5. Math.Round(4.34, 1); // Returns 4.3 Math.Round(4.35, 1); // Returns 4.4 Math.Round(4.36, 1); // Returns 4.4
Round(Decimal, MidpointRounding) Rounds a decimal value to the nearest integer. A parameter specifies how to round the value if it is midway between two other numbers.
Round(Double, Int32) Rounds a double-precision floating-point value to a specified number of fractional digits.
Round(Double, MidpointRounding) Rounds a double-precision floating-point value to the nearest integer. A parameter specifies how to round the value if it is midway between two other numbers.
Round(Decimal, Int32, MidpointRounding) Rounds a decimal value to a specified number of fractional digits. A parameter specifies how to round the value if it is midway between two other numbers.
3.4 = Math.Round( 3.45, 1, MidpointRounding.ToEven) 3.5 = Math.Round( 3.45, 1, MidpointRounding.AwayFromZero) -3.4 = Math.Round(-3.45, 1, MidpointRounding.ToEven) -3.5 = Math.Round(-3.45, 1, MidpointRounding.AwayFromZero)
Round(Double, Int32, MidpointRounding) Rounds a double-precision floating-point value to the specified number of fractional digits. A parameter specifies how to round the value if it is midway between two other numbers.
// The example displays the following output: // 2.125 --> 2.13 // 2.135 --> 2.13 // 2.145 --> 2.15 // 3.125 --> 3.13 // 3.135 --> 3.14 // 3.145 --> 3.15 This code example produces the following results: 3.4 = Math.Round( 3.45, 1) -3.4 = Math.Round(-3.45, 1) 3.4 = Math.Round( 3.45, 1, MidpointRounding.ToEven) 3.5 = Math.Round( 3.45, 1, MidpointRounding.AwayFromZero) -3.4 = Math.Round(-3.45, 1, MidpointRounding.ToEven) -3.5 = Math.Round(-3.45, 1, MidpointRounding.AwayFromZero)
قصد داریم الگوهای مختلف ایندکس گذاری و استراتژی Non-Clustered Indexes را در Sql Server، بررسی کنیم.
مزایای ایجاد ایندکسهای صحیح بر اساس نیازهای واقعی کاری:
تقریبا در تمام دیتابیسها به راههای دیگری برای دسترسی به دادههای جداول نیاز خواهد شد که لزوما این دادهها براساس ترتیب هنگام ذخیره سازی، مرتب نیستند. در چنین شرایطی ایندکسهای غیر خوشهای بر سر کار خواهند آمد.
Search Columns
یکی از الگوهای اولیه، ساخت ایندکسهای غیر خوشهای براساس الگوهای جستجوی تعریف شده یا مورد انتظار میباشد. این الگو با اینکه خیلی شناخته شده است ولی گاهی اوقات به راحتی از کنار آن گذشته و از آن چشم پوشی میکنیم.
برای مثال اگر قرار است در جدول Contacts جستجویی براساس نام آنها داشته باشید، بهتر است یک ایندکس غیر خوشهای بر روی فیلد نام ایجاد کنید. هدف اصلی از این الگو، کاهش هزینهی Scan کردن دوبارهی ایندکس خوشه دار و انتقال این عملیات به ایندکس غیر خوشه داری که مسیر دسترسی مستقیم به دیتا را مهیا میکند. به مثال زیر توجه بفرمایید:
USE AdventureWorks2012; GO CREATE TABLE dbo.Contacts ( ContactID INT IDENTITY (1, 1), FirstName NVARCHAR (50), LastName NVARCHAR (50), IsActive BIT , EmailAddress NVARCHAR (50), CertificationDate DATETIME , FillerData CHAR (1000) , CONSTRAINT PK_Contacts PRIMARY KEY CLUSTERED (ContactID) ); INSERT INTO dbo.Contacts (FirstName, LastName, IsActive, EmailAddress, CertificationDate) SELECT pp.FirstName, pp.LastName, IIF (pp.BusinessEntityID / 10 = 1, 1, 0), pea.EmailAddress, IIF (pp.BusinessEntityID / 10 = 1, pp.ModifiedDate, NULL) FROM Person.Person AS pp INNER JOIN Person.EmailAddress AS pea ON pp.BusinessEntityID = pea.BusinessEntityID;
ابتدا قصد داریم از جدول Contacts بدون استفاده از هیچ ایندکس غیر خوشهای، کوئری بگیریم. نتیجههای نشان داده شدهی در کوئری حاصل از کد T-SQL زیر به شرح زیر است:
SET STATISTICS IO ON; SELECT ContactID, FirstName FROM dbo.Contacts WHERE FirstName = 'Catherine'; SET STATISTICS IO OFF;
22 رکورد را واکشی کرده است؛ ولی با خواندن 2866 page ! که این تعداد، تمام صفحات موجود در جدول میباشد. بنابراین واکشی این تعداد رکورد از کل رکوردهای موجود در جدول (19000) نیاز به چک کردن همهی صفحات را خواهد داشت که واقعا روش بهینهای نمیباشد.
همانطور که در تصویر پلن کوئری بالا هم مشخص است، کل ایندکس خوشه دار ما Scan شده است که هزینهی بالایی خواهد داشت.
حال با کد T-SQL زیر یک ایندکس غیر خوشه دار را بر روی فیلد FirstName ایجاد خواهیم کرد:
CREATE INDEX IX_Contacts_FirstName ON dbo.Contacts(FirstName);
اگر دوباره کوئری قبلی را اجرا کنیم، به نتایج خیلی بهتری خواهیم رسید و تعداد صفحات خوانده شده به 2 کاهش یافته است!
Sql Server این بار به جای اسکن دوبارهی ایندکس خوشه دار، با استفاده از Index Seek و بهره بردن از ایندکس ایجاد شدهی توسط ما، یک پلن قابل قبول را برای ما
ارائه داده است.
Index Intersection
در برخی از سناریوها لازم است یکسری ستون دیگر هم علاوه بر ستونی که ایندکس را بر روی آن تعریف کردهایم، در بخش شرط یا خروجی select استفاده شوند. یکی از راهحلها، ایجاد یک ایندکس غیر خوشهای که سایر ستونها را نیز Include میکند، میباشد. با وجود ایندکسهایی که هر کدام از آنها میتوانند برای ادا کردن بخشی از شروط، نقش ایفا کنند، Sql Server هم با به کار بردن آنها میتواند رکوردهایی که در فصل مشترک حاصل از جسجتوی این ایندکسها بدست آمده را به عنوان خروجی کوئری ما بازگشت دهد. این عملیات Index Intersection نام دارد. به مثال زیر توجه کنید:
SET STATISTICS IO ON; SELECT ContactID, FirstName, LastName FROM dbo.Contacts WHERE FirstName = 'Catherine' AND LastName = 'Cox'; SET STATISTICS IO OFF;
در کوئری بالا علاوه بر FirstName که یک ایندکس غیر خوشه دار را بر روی آن ایجاد کردهایم، فیلد LastName را هم در بخش Select و شرط، مطرح کردهایم. حالا اگر آن را اجرا کنیم، به آمار و پلن زیر دست خواهیم یافت:
بله تعداد Pageهای خوانده شده این بار به 68 افزایش یافته است که نسبت به حالت بدون LastName که 2 Page خوانده شده بود، زیاد است. همانطور که در پلن زیر مشخص است، به دلیل ایندکسی که برروی FirstName ایجاد کردهایم، نمیتواند تمام دادههای مورد نیاز کوئری را مهیا کند. عملیات Key Lookup و nested loop هم این بار اضافه شدهاند. Sql Server همچنان استفاده از ایندکس موجود را در کنار Key Lookup از ایندکس خوشه دار، ارزانتر از اسکن ایندکس خوشه دار، تشخیص داده است.
مشکل
زمانی گریبان گیر ما خواهد شد که به ازای هر مطابقتی در ایندکس غیر خوشه دار، یک
بار به ایندکس خوشه دار برای بررسی شرط بعدی و واکشی دیتا، رجوع خواهد شد. باید
دقت کرد که Key
Lookup همیشه به عنوان مشکل مطرح نمیشود. ولی باعث افزایش
غیرضروری هزینههای CPU و I/O برای کوئری خواهد شد.
برای
استفاده از الگوی Index Intersection، یک ایندکس غیر خوشه دار
برروی ستون LastName ایجاد خواهیم کرد:
CREATE INDEX IX_Contacts_LastName ON dbo.Contacts(LastName);
اگر این بار کوئری قبل
را اجرا کنیم، به آمار و پلن زیر خواهیم رسید:
بله تعداد Pageهای خوانده شده به 5 کاهش یافته و این بار به جای استفاده از Key Lookup، از دو index seek استفاده کرده است که هزینهای کمتر را نسبت به حالت قبل خواهد داشت. به دلیل اینکه این دو ایندکس تمام دیتای لازم را میتوانند مهیا کنند، دیگر نیازی به رجوع به ایندکس خوشه دار نخواهد بود. تصویر زیر در درک پلن بالا و این الگو میتواند مفید باشد:
Multiple Columns
در دو
الگوی قبل، بیشتر به ایجاد ایندکس، بر روی یک ستون متمرکز شده بودیم. اگر تعدادی از
ستونها در بخش شروط مربوط به کوئری مطرح شوند، بهتر است آنها
را در قالب یک ایندکس نگهداری کنیم. برای نشان دادن تأثیر این مورد، یک
ایندکس غیر خوشه دار را بر روی دو ستون ایجاد میکنیم:
CREATE INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts(FirstName, LastName); SET STATISTICS IO ON; SELECT ContactID, FirstName, LastName FROM dbo.Contacts WHERE FirstName = 'Catherine' AND LastName = 'Cox'; SET STATISTICS IO OFF;
با اجرای کوئری بالا به
آمار و پلن زیر خواهیم رسید:
باید توجه داشت هر زمان که نیاز است یکسری فیلد، در قسمت شرطی خیلی از کوئریها تکرار شوند، ایجاد کردن یک ایندکس برروی آنها به صورت یکجا، ایدهی خوبی خواهد بود.
الگوی Multiple Columns هم به مانند الگوی Search Columns باید هنگام ایندکس گذاری دیتابیس در نظر گرفته شود و از اهمیت بالایی برخوردار است. باید توجه داشت اگر فیلدهایی که در قسمت شرطی کوئری مطرح میشوند، متغییر باشد، استفاده از الگوی Index Intersection مفید خواهد. ولی برای مواقعی که نیاز است یکسری فیلد به صورت یکجا در بخش شرطی کوئری مطرح شوند، الگوی Multiple Columns کارآیی بهتری خواهد داشت. از این دو الگوی مطرح شده که در تناقض باهم قرار دارند، میتوان به نحوی استفاده برد تا هزینهی کلی را کاهش داد.
Covering Index
الگوی بعدی، ایندکس پوشش دهنده نام گرفته است. همانند نامی که دارد، هدف آن نگهداری یکسری ستون در ستونهای ایندکس تولیدی که اتفاقا این ستونها در قسمت شرطی کوئری قرار ندارند، ولی قرار است به عنوان خروجی Select برگردانده شوند، میباشد.
این الگو به عنوان یک روش استاندارد ایندکس گذاری در Sql Server مطرح بوده است. البته در ادامه و با بروز شدن روشهایی که میتوان ایندکسها را ایجاد کرد، این الگو نسبت به قبل کمتر مفید است! از آن جهت که یک روش شناخته شده میباشد، در این قسمت این مورد را هم مطرح کردیم. به مثال زیر توجه کنید:
SET STATISTICS IO ON; SELECT ContactID, FirstName, LastName, IsActive FROM dbo.Contacts WHERE FirstName = 'Catherine' AND LastName = 'Cox'; SET STATISTICS IO OFF;
در کوئری بالا این بار قصد داریم خصوصیت IsActive را که در ایندکس IX_Contacts_FirstNameLastName نگهداری نمیشود و همچنین در قسمت شرطی هم مطرح نشده و نیازی به آن نبوده، هم واکشی کنیم. با توجه به نتایج بدست آمده که در آمار و پلن زیر مشخص است، باز هم تعداد Pageهای خوانده شده به 5 افزایش یافته و بار دیگر، Key Lookup و Nested Loop را در کنار یک Index Seek، برروی ایندکسی که با الگوی Multiple Columns ایجاد کردهایم، خواهیم داشت.
الگوی index covering پیشنهاد میکند ستونی را هم که در قسمت شرطی مطرح نمیشود، به عنوان ستونی اصلی در ایندکس، نگهداری کنیم؛ به شکل زیر:
CREATE INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts(FirstName, LastName,IsActive)
ایندکس غیر خوشه دار بالا، 3 فیلدی را که قرار است در بخش شرطی مطرح شوند، یا به عنوان خروجی Select برگردانده شوند، در بر میگیرد. سپس کوئری قبلی را دوباره اجرا میکنیم. به نتایج زیر خواهیم رسید:
باز هم هزینهی Key Lookup حذف شده و این بار از ایندکس جدید ما استفاده شده و تعداد Pageهای خوانده شده هم به 2 کاهش یافته است.
این الگو در بیشتر سناریوها کاملا مفید بوده و پتانسیل افزایش کارآیی را در بیشتر سناریوها دارد. اما در سالهای اخیر از زمانیکه امکانات جدیدی در Sql Server 2005 به بعد ایجاد شد، از استفادهی آن کاسته شده است. با وجود این امکانات جدید که در الگوی بعد به آن خواهیم پرداخت، میتوان ستونهای اضافی را در ایندکسها، Include کنیم و نیازی نیست که جزء ستونهای اصلی ایندکس باشند.
Included Columns
الگوی Included Columns درواقعا پسر عموی الگوی Covering Index میباشد. در این الگو از عبارت INCLUDE در ایجاد یا تغییر ایندکس استفاده میشود و از این طریق امکان این را مهیا میکند تا یکسری ستون که جز ستونهای اصلی ایندکس نیستند هم در ایندکس غیر خوشه دار ما افزوده شوند و حتی در قسمت شرطی هم مطرح شوند. این عمل خیلی شبیه به نگهداری دیتاهای غیر کلیدی در یک ایندکس خوشه دار میباشد و این همان تفاوت اصلی بین دو الگو مطرح شده است.
اگر کوئری زیر را اجرا کنیم:
SET STATISTICS IO ON; SELECT ContactID, FirstName, LastName, EmailAddress FROM dbo.Contacts WHERE FirstName = 'Catherine'; SET STATISTICS IO OFF;
68 Page خوانده شده خواهیم داشت که حاصل یک Index Seek بر روی ایندکس IX_Contacts_FirstName میباشد و برای واکشی بقیه ستونها هم یک Key Lookup بر روی ایندکس خوشه دار در پلن مشخص خواهد بود.
علاوه بر ایندکسهای ایجاد شدهی در مراحل قبل، حال یک ایندکس غیر خوشهای را با استفاده از الگوی INC ایجاد میکنیم:
CREATE INDEX IX_Contacts_FirstNameINC ON dbo.Contacts(FirstName) INCLUDE (LastName, IsActive, EmailAddress);
دوباره کوئری قبلی را اگر اجرا کنیم، نتایج به دست آمده، به شرح زیر خواهد بود:
این بار از ایندکس جدید ایجاد شده استفاده شده و تعداد Pageهای خوانده شده، به 3 کاهش یافته است. با توجه به انعطاف پذیری این الگو میتوان از اندک افزایشی که در تعداد Pageهای خوانده شده نسبت به الگوی ایندکس پوشش دهنده وجود دارد، چشم پوشی کرد.
در مثالهای قبل چندین ایندکس بر روی جدول Contacts ایجاد کردهایم که 4 مورد از آنها به صورت اختصاصی بر روی فیلد FirstName بوده است. باید توجه کرد این ایندکسها نیاز به فضا و نگهداری در مواقع ویرایش رکوردهای جدول خواهند داشت. لذا این هزینهها اثر منفی برروی تمام عملیاتی خواهند داشت که روی جدول انجام میشود.
الگوی INC میتواند این مشکل را برطرف کند. برای مثال با استفاده از آن میتوان ایندکسهای تولید شدهی در مراحل قبل را بر روی FirstName، توسط یک ایندکس نیز پوشش داد. لذا ایندکسهای قبلی را حذف کرده و با یکسری کوئری، مشخص خواهیم کرد که گفتهی ما صحت دارد:
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts') AND name = 'IX_Contacts_FirstNameLastName') DROP INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts GO IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts') AND name = 'IX_Contacts_FirstNameLastNameIsActive') DROP INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts GO IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts') AND name = 'IX_Contacts_FirstName') DROP INDEX IX_Contacts_FirstName ON dbo.Contacts GO
با کدهای بالا ایندکسهایی را که بر روی FirstName ایجاد شده بودند، حذف کرده و این بار تمام کوئریهای مطرح شدهی در مراحل قبل را یکبار دیگر اجرا میکنیم:
SET STATISTICS IO ON; SELECT ContactID, FirstName FROM dbo.Contacts WHERE FirstName = 'Catherine'; SELECT ContactID, FirstName, LastName FROM dbo.Contacts WHERE FirstName = 'Catherine' AND LastName = 'Cox'; SELECT ContactID, FirstName, LastName, IsActive FROM dbo.Contacts WHERE FirstName = 'Catherine' AND LastName = 'Cox'; SET STATISTICS IO OFF;
دو نکتهای که باید به آنها توجه کرد:
در جواب مورد دوم، با اینکه حدود 50% افزایش در تعداد Pageهای خوانده شده نسبت به حالتی که به صورت جدا از هم برای هر کوئری خاص یک ایندکس در نظر گرفته بودیم، داشتهایم ولی این تغییر کارآیی نمیتواند ساخت 4 ایندکس را به جای 1 ایندکس که تمام آنها را پوشش میدهد، توجیه کند! در حالیکه ما به کارآیی مورد نظر خود دست یافتهایم.
در نتیجه الگوی INC هنگام ساخت ایندکسهای غیر خوشه دار خیلی مهم است و باید به آن توجه زیادی کرد. بیشتر در مواقعیکه نیاز است عملیات Lookup را حذف کنید و سرعت خواندن و کارآیی اجرای کوئری را افزایش دهید، این الگو مناسب خواهد بود. همچنین با کاهش تعداد ایندکسها برای پوشش دادن ایندکسهای لازم برای کوئریها مشابه، باید توجه کرد که باز هم نسبت به حالتی که هیچ ایندکس غیر خوشه داری ایجاد نشده، کارآیی افزایش مییابد.
Filtered Indexes
ممکن است در برخی از جداول دیتابیس، یکسری رکوردهایی با مقدارهایی که به ندرت یا هرگز از آنها در یک برنامهی کاربردی استفاده نخواهد شد، ذخیره شده باشند. در این مواقع، حذف آنها از نتیجهی خروجی کوئریها میتواند خیلی مفید باشد. یا در مواقعی میتوان از این مورد برای مشخص کردن یک زیر مجموعهی از دادههای جدول، برای ایجاد ایندکس استفاده کرد. همچنین میتوان به جای کوئری زدن بر روی میلیونها رکورد موجود در جدول، ایندکسها را طوری ایجاد کرد که پوشش دهندهی بخشی از دیتای چند میلیونی باشند.
SET STATISTICS IO ON; SELECT ContactID, FirstName, LastName, CertificationDate FROM dbo.Contacts WHERE CertificationDate IS NOT NULL ORDER BY CertificationDate; SELECT ContactID, FirstName, LastName, CertificationDate FROM dbo.Contacts WHERE CertificationDate BETWEEN '20050101' AND '20050201' ORDER BY CertificationDate; SET STATISTICS IO OFF;
زمانیکه مقدار آن نال باشد، استفاده نخواهد شد. آیا عقل سلیم قبول میکند که این مقادیر نال را در ایندکس نگهداری و رکوردهایی با مقادیر نال داشته باشیم؟ برای پیاده سازی این الگو باید از عبارت Where به هنگام ساخت ایندکسهای غیر خوشهای استفاده کنیم.
توجه کنید که امکان استفاده از مقادیر متغیر در بخش Where، وجود ندارد.
نکتهی بعدی این است که نمیتوان مقایسههای پیچیده را در این مورد استفاده کرد. برای مثال استفاده از LIKE و BETWEEN امکان پذیر نیست.
این بار با استفاده از الگوی Filtered Indexes یک ایندکس غیر خوشهای را بر روی ستون CertificationDate ایجاد میکنیم:
CREATE INDEX IX_Contacts_CertificationDate ON dbo.Contacts(CertificationDate) INCLUDE (FirstName, LastName) WHERE CertificationDate IS NOT NULL;
حال دوباره دو کوئری قبلی را اجرا میکنیم. آمار و پلن زیر نشان میدهند که این بار فقط 2 عدد Page خوانده شده است و عملیات به Index Seek بر روی ایندکس جدید تغییر کرده است.
یکسری از مزایای نگهداری فقط زیر مجموعهای از رکوردهای جدول در ایندکس، به شرح زیر است:
در مورد نوع اول، هر وقت که رکوردهای جدول ChildTable تغییر کند، در این صورت مقدار ParentID موجود جدول ChildTable با یک جستجو در جدول ParentTable اعتبارسنجی خواهد شد. از آنجایی که این کلید خارجی در جدول ParentTable یک کلید اصلی بوده، یک ایندکس خوشه دار بر روی آن ایجاد شده است و تأثیری در کاهش کارآیی نخواهد داشت.
در مورد نوع دوم، هروقت تغییراتی بر روی ParentID موجود در جدول ParentTable داشته باشیم، نیاز است اعتبار سنجی بر روی جدول ChildTable انجام شود. برای مثال با حذف یک رکورد در جدول پدر، لازم است که جدول فرزند بررسی کند که آیا این ParentID در رکوردها موجود استفاده شده است یا خیر؟ در این نوع از اعتبارسنجی، الگوی Foreign Key خود را نشان میدهد.
برای نشان دادن استفادهی از این الگو، لازم است جداول مطرح شدهی در تصویر بالا را ایجاد کنیم:
USE AdventureWorks2012; GO CREATE TABLE dbo.Customer ( CustomerID INT , FillterData CHAR (1000), CONSTRAINT PK_Customer_CustomerID PRIMARY KEY CLUSTERED (CustomerID) ); CREATE TABLE dbo.SalesOrderHeader ( SalesOrderID INT , OrderDate DATETIME , DueDate DATETIME , CustomerID INT , FillterData CHAR (1000), CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID), CONSTRAINT GK_SalesOrderHeader_CustomerID_FROM_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer (CustomerID) );
کد T-SQL بالا دو جدول مشتری و سفارش را ایجاد کرده و یک ارتباط یک به چند مابین آنها را از سمت مشتری به سفارش ایجاد میکند. برای انجام آزمایش خود، یکسری دیتای موجود را هم از جداول دیتابیس AdventureWorks2012 در جداول بالا درج میکنیم:
INSERT INTO dbo.Customer (CustomerID) SELECT CustomerID FROM Sales.Customer; INSERT INTO dbo.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID) SELECT SalesOrderID, OrderDate, DueDate, CustomerID FROM Sales.SalesOrderHeader;
در واقع میخواهیم نشان دهیم که در زمان تغییر یک رکورد از جدول Customers، چه اتفاقاتی میافتد. برای مثال این تغییر میتواند حذف یک رکورد باشد که به شکل زیر آن را انجام خواهیم داد:
SET STATISTICS IO ON; DELETE dbo.Customer WHERE CustomerID = 701; SET STATISTICS IO OFF;
آمار و پلن زیر نشان میدهد که برای حذف یک رکورد در جدول مشتری، چون از عملیات Index Seek برروی ایندکس خوشه دار موجود برروی ستون CustomerID استفاده شده است، تنها 3 Page خوانده شدهاست؛ ولی برای اعتبارسنجی برروی جدول سفارش، با خواندن 4513 page و انجام عملیات Index Scan برروی ایندکس خوشه دار باعث کاهش کارآیی شده است.
برای پیاده سازی الگوی کلیدخارجی یک ایندکس غیر خوشهای را بر روی CustomerID در جدول سفارشات ایجاد میکنیم:
CREATE INDEX IS_SalesOrderHeader_CustomerID ON dbo.SalesOrderHeader(CustomerID)
اگر دوباره کوئری بالا را با یک CustomerID دیگر انجام دهیم، به نتایج بهتری دست خواهیم یافت. تعداد Pageهای خوانده شدهی برای اعتبارسنجی جدول سفارشات، به عدد 2 کاهش یافته است! و از یک عملیات Index Seek بر روی ایندکس ایجاد شده، استفاده شده است.
اگر از EF استفاده میکنید، در حال حاضر به غیر از الگوهای Filtered Indexes و Include Indexes، پیاده سازی بقیه الگوهای ذکر شده به صورت توکار پشتیبانی میشود. برای دو الگوی مذکور هم میتوان از نوشتن T-SQL خام استفاده کرد. برای مثال:
public partial class AddIndexes : DbMigration { private const string IndexName = "IX_LogSamples"; public override void Up() { Sql(String.Format(@"CREATE NONCLUSTERED INDEX [{0}] ON [dbo].[Logs] ([SampleId],[Date]) INCLUDE ([Value])", IndexName)); } public override void Down() { DropIndex("dbo.Logs", IndexName); } }
یا حتی خیلی تمیزتر و با ایده گرفتن از این مطلب میتوان به یک کد Refactoring friendly نیز دست یافت.
پ.ن: این مطلب خلاصهای از فصل 8 کتاب Expert Performance Indexing for SQL Server 2012 میباشد.
var userName = "user 1"; var user = context.Users.FromSql($"select top 1 * from Users where name = {userName} ").FirstOrDefault(); if (user != null) { Console.WriteLine(user.Name); }
SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name] FROM ( select top 1 * from Users where name = @p0 ) AS [u]
var sql = $"select top 1 * from Users where name = {userName} "; user = context.Users.FromSql(sql).FirstOrDefault(); if (user != null) { Console.WriteLine(user.Name); }
.SqlException: Incorrect syntax near '1'
public static IQueryable<TEntity> FromSql<TEntity>(this IQueryable<TEntity> source, FormattableString sql) where TEntity : class;
var sql = $"select top 1 * from Users where name = '{userName}' ";
SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name] FROM ( select top 1 * from Users where name = 'user 1' ) AS [u]