مایکروسافت و اپنسورس
کتاب رایگان Power BI Succinctly
Power BI is a cloud-based business analytics service that gives us a single view of the most critical business data. A tool for creating live dashboards and rich, interactive reports, Power BI allows access to data for monitoring the health of a business. Pierstefano Tucci will take readers through the Power BI interface, query creation, publishing your work, and more in Power BI Succinctly.
- What is Power BI?
- Service
- Desktop
- Mobile Apps
- Solution Template
- Devoloper
- Power BI Embedded
- Power BI Gateway: Data Security
8 ویژگی جذاب Angular
I've been doing some work the last couple of weeks with Angular2. I really like it. Not just because it uses typescript, but also because it feels really natural and straightforward while working with it. No more string based dependency injection, or strange digest cycle stuff, it just seems to work. This last week I've migrated our beta-13 Angular app to the latest rc-1, and used that to keep track of the fun and easy stuff Angular 2 provides. Note though, that the application we're developing is really that complex, so I can only assume we'll run into more complex Angular2 features in the near future. For now, though, let me share some general tips and tricks we've encountered thus far (in no particular order). Oh, all examples are in typescript, since after using that, I really don't want to go back to plain old javascript (POJS?).
کتابخانه Firebase گوگل مخصوص دات نت
- کلید خارجی ترکیبی (composite foreign key)
- خود ارجاعی (self referencing)
- اعمال تغییرات به صورت آبشاری (cascade)
- چندین مسیر برای اعمال (multiple cascading path)
- جدول اتصال (junction table)- ارتباط یک به یک
توسط دستور create table به دو شکل میتوانیم بر روی ستونها قید (کلید اولیه، check، کلید خارجی، کلید یونیک...) تعریف نمود:
- قید ستونی
- قید جدولی
syntax مربوط به قید کلید خارجی در مدل ستونی به صورت زیر است:
<column_constraint> ::= [ CONSTRAINT constraint_name ] { ... | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] ... }
کلید خارجی ترکیبی
زمانی که در جدول والد (parent) کلید اولیه ترکیبی باشد، هر جدولی که بخواهد به کلید جدول والد ارجاعی داشته باشد باید از ترکیب دو ستون برای ساخت کلید خارجی استفاده کند.
فرض کنید جدول parent به این صورت است (ترکیب دو ستون col1 و col2 کلید اولیه است)
create table parent ( col1 int not null, col2 int not null, col3 char(1) null, -- Composite Primary Key primary key(col1, col2) );
و جدول child که دارای قید کلید خارجی ترکیبی به نام fk_comp است و به جدول parent ارجاع داده است:
create table child ( col0 int primary key, col1 int null, col2 int null, -- Composite Foreing Key Constraint constraint fk_comp foreign key (col1, col2) references parent(col1, col2) );
در این DDL هم از قید جدولی برای تعریف کلید خارجی ترکیبی استفاده شده است.
نمودار این دو جدول:
پس به عنوان نتیجه گیری، هرگاه جدول اصلی دارای کلید ترکیبی بود در جداول child نیز باید از کلید خارجی ترکیبی برای ایجاد relationship استفاده نمود.
اما این دو جدول را به یک شیوه دیگر نیز میتوان طراحی نمود. در جدول parent ترکیب دو ستون col1 و col2 را منحصربفرد (unique) گرفته و ستونی دیگر (مثلا از نوع identity) را به عنوان کلید اولیه در نظر گرفت (یا یک ستون از نوع محاسباتی تعریف کرده و آن را کلید قرار داد)
create table parent ( col0 int not null primary key identity, col1 int not null, col2 int not null, col3 char(1) null, -- Composite Unique Key unique(col1, col2) ); create table child ( col0 int primary key, col1 int null references parent );
فرض کنید بخشهای مختلف یک سازمان که بصورت چارت است را توسط جدول پیاده سازی کردیم. ستونهای جدول به این شرح هستند:
- کد بخش
- نام بخش
- کد بخش بالایی
create table chart ( chart_nbr int not null primary key, parent_nbr int null references chart, chart_name varchar(5) null );
حالا فرض کنید میخواهیم اطلاعات نامه هایی که بین بخشها رد و بدل میشود را در یک جدول ذخیره کنیم. جدول دارای ستونهای زیر خواهد بود:
- شماره نامه
- کد بخش فرستنده
- کد بخش گیرنده
create table letters ( letter_nbr int primary key, sec_sender int not null references chart, sec_reciver int not null references chart );
نمودار جدول نامهها و چارت:
نکته ای که در اینجا وجود دارد این است که اگر کلید جدول chart بروز شود آنگاه SQL Server از دو راه میتواند جدول letters را بروز رسانی کند، به این علت پیغام خطایی با عنوان multiple cascading paths صادر میشود. برای رفع این مشکل باید از trigger کمک گرفت.
جدول اتصال (junction table)
برای پیاده سازی رابطه N-N از جدول واسط کمک گرفته میشود. برای این منظور رابطه N-N را باید به دو رابطه 1-N تجزیه کرد.
فرض کنید یک جدول مربوط به خلبانان و جدول دیگر مربوط به مسیرهای پروازی (مثل مسیر ایران-ترکیه، ایران-عربستان...) است. یک خلبان ممکن است در چند مسیر پروازی هواپیما را هدایت کرده باشد و یا بالعکس یک مسیر پروازی ممکن است توسط N خلبان طی شده باشد.
برای پیاده سازی اینگونه سیستم هایی باید یک جدول ایجاد نمود که دارای دو کلید خارجی باشد یکی آنها به جدول خلبانان و دیگری به مسیرهای پروازی مرتبط است.
میتوان ترکیب دو کلید خارجی جدول واسط را کلید اولیه در نظر گرفت.
پس خواهیم داشت:
create table pilot ( pilot_code int primary key, pilot_name varchar(20) ); create table paths ( path_code int primary key, path_name varchar(20) ); create table junction ( pilot_code int references pilot, path_code int references paths, primary key (pilot_code, path_code) );
و نمودار آن:
رابطه یک به یک
زمانی که نمونههای محدودی از یک موجودیت دارای مقدار برای یکسری خصیصه هستند بهتر است جدول به دو جدول تجزیه شود تا فضای اضافی صرف جدول نشود. مثلا در مدرسه تنها 10 درصد دانش آموزان جزء تیم فوتبال هستند حال اگر بخواهیم اطلاعات مربوط به تیم فوتبال مثل تعداد گل زده، تعداد بازی ... در جدول اصلی ذخیره کنیم برای 90 درصد دانش آموزان مقداری نخواهیم داشت. برای حل این مساله ارتباط یک به یک پیشنهاد میشود.
create table student ( std_code int primary key, std_name varchar(25) not null ); create table football ( std_code int primary key constraint one_to_one_fk references student, std_cnt_goal int not null default (0) );
توجه داشته باشید که ستون std_code هم کلید اولیه هست و هم کلید خارجی که به جدول student ارجاع داده شده است.
نتیجه گیری
یک ستون همزمان میتواند کلید اولیه باشد و هم کلید خارجی (مثلا در ارتباط یک به یک)
همانطور که کلید اولیه ترکیبی داریم به همان شکل هم کلید خارجی ترکیبی داریم.
یک جدول میتواند به خودش ارجاع دهد که به آن اصطلاحا self-referencing میگویند
relationship چیزی جز کلید خارجی نیست و کلید خارجی نیز چیزی جز یک قید برای جامعیت دادهها نیست
جامعیت داده ارجاعی را میتوان توسط trigger پیاده سازی کرد
اگر SQL Server بیش از یک مسیر برای تغییر جدول child داشته باشد با مشکل مواجه خواهید شد
NET 5.0 Preview 5. منتشر شد
قصد داریم الگوهای مختلف ایندکس گذاری و استراتژی Non-Clustered Indexes را در Sql Server، بررسی کنیم.
مزایای ایجاد ایندکسهای صحیح بر اساس نیازهای واقعی کاری:
- سریعتر شدن اجرای کوئریهای جستجو در تعداد رکوردهای بالا
- مرتب سازی سریعتر نتایج (sorting)
- کوئریهایی که بر اساس عبارت GROUP BY ایجاد شدهاند، سریعتر اجرا خواهند شد
Non-Clustered Indexes
تقریبا در تمام دیتابیسها به راههای دیگری برای دسترسی به دادههای جداول نیاز خواهد شد که لزوما این دادهها براساس ترتیب هنگام ذخیره سازی، مرتب نیستند. در چنین شرایطی ایندکسهای غیر خوشهای بر سر کار خواهند آمد.
در ادامه الگوهای مختلف ایندکس گذاری مرتبط با ایندکسهای غیر خوشهای را بررسی کرده و برای هر کدام از آنها مثالی را بررسی خواهیم کرد. خواهیم دید هر ایندکسی که از جانب ما ایجاد میشود، نمیتوان مطمئن شد که توسط Sql Server مورد استفاده قرار میگیرد!
این الگوها در تعیین زمان و مکان ساخت ایندکسهای غیر خوشهای، به ما کمک خواهند کرد که به شرح زیر میباشند:
- Search Columns
- Index Intersection
- Multiple Columns
- Covering Indexes
- Included Columns
- Filterd Indexes
- Foreign Keys
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;
دو نکتهای که باید به آنها توجه کرد:
- کوئریها بالا در مقایسه با الگوهای قبلی به چه شکلی اجرا خواهند شد؟
- توجه کردن به تعداد Pageهای خوانده شده
در جواب مورد دوم، با اینکه حدود 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 بر روی ایندکس جدید تغییر کرده است.
یکسری از مزایای نگهداری فقط زیر مجموعهای از رکوردهای جدول در ایندکس، به شرح زیر است:
- کم شدن تعداد رکوردهای ایندکسها موجب کاهش تعداد Pageهای مورد نیاز برای ذخیره سازی آنها و در نتیجه کاهش حجم مورد نیاز برای ذخیره سازی خواهد شد.
- با توجه به مورد اول، اگر تعداد Pageهای برای نگهداری ایندکس کم باشند، لذا فرصت Fragmentation برای ایندکس کم خواهد بود و در نتیجه، هزینه و تلاش کمی برای نگهداری آن لازم است.
- زمانیکه تعداد مقادیر نگهداری شدهی در ایندکس محدود هستند، تعداد Page هایی که برای پیمایش نیاز است، کم خواهند بود و اینجاست که حتی Index Scan هم بروری آن خیلی بهینهتر از Index Scan بر روی ایندکس خوشه دار میباشد.
- اگر لازم است بر روی یک ستون که بهصورت نالپذیر است، ایندکس ایجاد کنید(دلایل آن پیشتر گفته شد).
- اگر لازم است برروی Sparse Column، یک ایندکس یکتا ایجاد کنید.
- مورد بعدی همان بحث کاهش تعداد رکوردهایی میباشد که در ایندکس ذخیره میشوند.
- اعتبارسنجی بر روی جدول ParentTable
- اعتبارسنجی بر روی جدول ChildTable
در مورد نوع اول، هر وقت که رکوردهای جدول 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 میباشد.
EF Code First #15
EF Code first و بانکهای اطلاعاتی متفاوت
در آخرین قسمت از سری EF Code first بد نیست نحوه استفاده از بانکهای اطلاعاتی دیگری را بجز SQL Server نیز بررسی کنیم. در اینجا کلاسهای مدل و کدهای مورد استفاده نیز همانند قسمت 14 است و تنها به ذکر تفاوتها و نکات مرتبط اکتفاء خواهد شد.
حالت کلی پشتیبانی از بانکهای اطلاعاتی مختلف توسط EF Code first
EF Code first با کلیه پروایدرهای تهیه شده برای ADO.NET 3.5 که پشتیبانی از EF را لحاظ کرده باشند، به خوبی کار میکند. پروایدرهای مخصوص ADO.NET 4.0، تنها سه گزینه DeleteDatabase/CreateDatabase/DatabaseExists را نسبت به نگارش قبلی بیشتر دارند و EF Code first ویژگیهای بیشتری را طلب نمیکند.
بنابراین اگر حین استفاده از پروایدر ADO.NET مخصوص بانک اطلاعاتی خاصی با پیغام «CreateDatabase is not supported by the provider» مواجه شدید، به این معنا است که این پروایدر برای دات نت 4 به روز نشده است. اما به این معنا نیست که با EF Code first کار نمیکند. فقط باید یک دیتابیس خالی از پیش تهیه شده را به برنامه معرفی کنید تا مباحث Database Migrations به خوبی کار کنند؛ یا اینکه کلا میتوانید Database Migrations را خاموش کرده (متد Database.SetInitializer را با پارامتر نال فراخوانی کنید) و فیلدها و جداول را دستی ایجاد کنید.
استفاده از EF Code first با SQLite
برای استفاده از SQLite در دات نت ابتدا نیاز به پروایدر ADO.NET آن است: «مکان دریافت درایورهای جدید SQLite مخصوص دات نت»
ضمن اینکه به نکته «استفاده از اسمبلیهای دات نت 2 در یک پروژه دات نت 4» نیز باید دقت داشت.
و یکی از بهترین management studio هایی که برای آن تهیه شده: «SQLite Manager»
پس از دریافت پروایدر آن، ارجاعی را به اسمبلی System.Data.SQLite.dll به برنامه اضافه کنید.
سپس فایل کانفیگ برنامه را به نحو زیر تغییر دهید:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.1.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
<connectionStrings>
<clear/>
<add name="Sample09Context"
connectionString="Data Source=CodeFirst.db"
providerName="System.Data.SQLite"/>
</connectionStrings>
</configuration>
همانطور که ملاحظه میکنید، تفاوت آن با قبل، تغییر connectionString و providerName است.
اکنون اگر همان برنامه قسمت قبل را اجرا کنیم به خطای زیر برخواهیم خورد:
«The given key was not present in the dictionary»
در این مورد هم توضیح داده شد. سه گزینه DeleteDatabase/CreateDatabase/DatabaseExists در پروایدر جاری SQLite برای دات نت وجود ندارد. به همین جهت نیاز است فایل «CodeFirst.db» ذکر شده در کانکشن استرینگ را ابتدا دستی درست کرد.
برای مثال از افزونه SQLite Manager استفاده کنید. ابتدا یک بانک اطلاعاتی خالی را درست کرده و سپس دستورات زیر را بر روی بانک اطلاعاتی اجرا کنید تا دو جدول خالی را ایجاد کند (در برگه Execute sql افزونه SQLite Manager):
CREATE TABLE [Payees](
[Id] [integer] PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] [text] NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [text] NULL,
[ModifiedOn] [datetime] NOT NULL,
[ModifiedBy] [text] NULL
);
CREATE TABLE [Bills](
[Id] [integer] PRIMARY KEY AUTOINCREMENT NOT NULL,
[Amount] [float](18, 2) NOT NULL,
[Description] [text] NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [text] NULL,
[ModifiedOn] [datetime] NOT NULL,
[ModifiedBy] [text] NULL,
[Payee_Id] [integer] NULL
);
سپس سطر زیر را نیز به ابتدای برنامه اضافه کنید:
Database.SetInitializer<Sample09Context>(null);
به این ترتیب database migrations خاموش میشود و اکنون برنامه بدون مشکل کار خواهد کرد.
فقط باید به یک سری نکات مانند نوع دادهها در بانکهای اطلاعاتی مختلف دقت داشت. برای مثال integer در اینجا از نوع Int64 است؛ بنابراین در برنامه نیز باید به همین ترتیب تعریف شود تا نگاشتها به درستی انجام شوند.
در کل تنها مشکل پروایدر فعلی SQLite عدم پشتیبانی از مباحث database migrations است. این مورد را خاموش کرده و تغییرات ساختار بانک اطلاعاتی را به صورت دستی به بانک اطلاعاتی اعمال کنید. بدون مشکل کار خواهد کرد.
البته اگر به دنبال پروایدری تجاری با پشتیبانی از آخرین نگارش EF Code first هستید، گزینه زیر نیز مهیا است:
http://devart.com/dotconnect/sqlite/
برای مثال اگر علاقمند به استفاده از حالت تشکیل بانک اطلاعاتی SQLite در حافظه هستید (با رشته اتصالی ویژه Data Source=:memory:;Version=3;New=True;)، فعلا تنها گزینه مهیا استفاده از پروایدر تجاری فوق است؛ زیرا مبحث Database Migrations را به خوبی پشتیبانی میکند.
استفاده از EF Code first با SQL Server CE
قبلا در مورد «استفاده از SQL-CE به کمک NHibernate» مطلبی را در این سایت مطالعه کردهاید. سه مورد اول آن با EF Code first یکی است و تفاوتی نمیکند (یک سری بحث عمومی مشترک است). البته با یک تفاوت؛ در اینجا EF Code first قادر است یک بانک اطلاعاتی خالی SQL Server CE را به صورت خودکار ایجاد کند و نیازی نیست تا آنرا دستی ایجاد کرد. مباحث database migrations و به روز رسانی خودکار ساختار بانک اطلاعاتی نیز در اینجا پشتیبانی میشود.
برای استفاده از آن ابتدا ارجاعی را به اسمبلی System.Data.SqlServerCe.dll قرار گرفته در مسیر Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop اضافه کنید.
سپس رشته اتصالی به بانک اطلاعاتی و providerName را به نحو زیر تغییر دهید:
<connectionStrings>
<clear/>
<add name="Sample09Context"
connectionString="Data Source=mydb.sdf;Password=1234;Encrypt Database=True"
providerName="System.Data.SqlServerCE.4.0"/>
</connectionStrings>
بدون نیاز به هیچگونه تغییری در کدهای برنامه، همین مقدار تغییر در تنظیمات ابتدایی برنامه برای کار با SQL Server CE کافی است.
ضمنا مشکلی هم با فیلد Identity در آخرین نگارش EF Code first وجود ندارد؛ برخلاف حالت database first آن که پیشتر این اجازه را نمیداد و خطای «Server-generated keys and server-generated values are not supported by SQL Server Compact» را ظاهر میکرد.
استفاده از EF Code first با MySQL
برای استفاده از EF Code first با MySQL (نگارش 5 به بعد البته) ابتدا نیاز است پروایدر مخصوص ADO.NET آنرا دریافت کرد: (^)
که از EF نیز پشتیبانی میکند. پس از نصب آن، ارجاعی را به اسمبلی MySql.Data.dll قرار گرفته در مسیر Program Files\MySQL\MySQL Connector Net 6.5.4\Assemblies\v4.0 به پروژه اضافه نمائید.
سپس رشته اتصالی و providerName را به نحو زیر تغییر دهید:
<connectionStrings>
<clear/>
<add name="Sample09Context"
connectionString="Datasource=localhost; Database=testdb2; Uid=root; Pwd=123;"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient"/>
<add name="MySQL Data Provider"
invariant="MySql.Data.MySqlClient"
description=".Net Framework Data Provider for MySQL"
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
همانطور که مشاهده میکنید در اینجا شماره نگارش دقیق پروایدر مورد استفاده نیز ذکر شده است. برای مثال اگر چندین پروایدر روی سیستم نصب است، با مقدار دهی DbProviderFactories میتوان از نگارش مخصوصی استفاده کرد.
با این تغییرات پس از اجرای برنامه قسمت قبل، به خطای زیر برخواهیم خورد:
The given key was not present in the dictionary
توضیحات این مورد با قسمت SQLite یکی است؛ به عبارتی نیاز است بانک اطلاعاتی testdb را دستی درست کرد. همچنین جداول و فیلدها را نیز باید دستی ایجاد کرد و database migrations را نیز باید خاموش کرد (پارامتر Database.SetInitializer را به نال مقدار دهی کنید).
برای این منظور یک دیتابیس خالی را ایجاد کرده و سپس دو جدول زیر را به آن اضافه کنید:
CREATE TABLE IF NOT EXISTS `bills` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Amount` float DEFAULT NULL,
`Description` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
`CreatedOn` datetime NOT NULL,
`CreatedBy` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
`ModifiedOn` datetime NOT NULL,
`ModifiedBy` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
`Payee_Id` int(11) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `payees` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
`CreatedOn` datetime NOT NULL,
`CreatedBy` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
`ModifiedOn` datetime NOT NULL,
`ModifiedBy` varchar(400) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=1 ;
پس از این تغییرات، برنامه بدون مشکل اجرا خواهد شد (ایجاد بانک اطلاعاتی خالی به همراه ایجاد ساختار جداول و خاموش کردن database migrations که توسط این پروایدر پشتیبانی نمیشود).
به علاوه پروایدر تجاری دیگری هم در سایت devart.com برای MySQL و EF Code first مهیا است که مباحث database migrations را به خوبی مدیریت میکند.
مشکل!
اگر به همین نحو برنامه را اجرا کنیم، فیلدهای یونیکد فارسی ثبت شده در MySQL با «??????? ?? ????» مقدار دهی خواهند شد و تنظیم CHARACTER SET utf8 COLLATE utf8_persian_ci نیز کافی نبوده است (این مورد با SQLite یا نگارشهای مختلف SQL Server بدون مشکل کار میکند و نیاز به تنظیم اضافهتری ندارد):
ALTER TABLE `bills` DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci
برای رفع این مشکل توصیه شده است که CharSet=UTF8 را به رشته اتصالی به بانک اطلاعاتی اضافه کنیم. اما در این حالت خطای زیر ظاهر میشود:
The provider did not return a ProviderManifestToken string
این مورد فقط به اشتباه بودن تعاریف رشته اتصالی بر میگردد؛ یا عدم پشتیبانی از تنظیم اضافهای که در رشته اتصالی ذکر شده است.
مقدار صحیح آن دقیقا مساوی CHARSET=utf8 است (با همین نگارش و رعایت کوچکی و بزرگی حروف؛ مهم!):
<connectionStrings>
<clear/>
<add name="Sample09Context"
connectionString="Datasource=localhost; Database=testdb; Uid=root; Pwd=123;CHARSET=utf8"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
به این ترتیب، مشکل ثبت عبارات یونیکد فارسی برطرف میشود (البته جدول هم بهتر است به DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci تغییر پیدا کند؛ مطابق دستور Alter ایی که در بالا ذکر شد).