یافتن مقادیر نال در Entity framework
ممنون از مطلب مفیدتون راستش من به این نکته ای که فرمودین توجه نکرده بودم. من دستوراتی را روی دیتابیس northwind اجرا کردم که اولین دستور به این صورت بود:
rg = ent.regions.where(x => x.regionid != null).tolist();
این دستور در sql server به اینصورت تبدیل میشود :
select [extent1].[regionid] as [regionid], [extent1].[regiondescription] as [regiondescription] from [dbo].[region] as [extent1]
آیا اینکه اصلا در دستور sql ما چک کردن برای null نداریم به این خاطر است که ef بطور اتوماتیک
چک میکند که فیلد regionid از نوع nullable هست و چنانچه نباشه اصلا شرط رو دخالت نمیده؟
من در گام بعدی این دستور را اجرا کردم :
rg = ent.regions.where(x => x.regiondescription == null).tolist();
که خروجی آن به این صورت بود:
select cast(null as int) as [c1], cast(null as varchar(1)) as [c2] from ( select 1 as x ) as [singlerowtable1] where 1 = 0
میخواستم اگر ممکنه کمی راجع به این دستور توضیح بفرمایید آیا این دستور همون کار is null رو انجام میده یا خیر.
باز هم سپاسگزارم
قصد داریم الگوهای مختلف ایندکس گذاری و استراتژی 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 میباشد.
مستند سازی Database
dbdescis a powerful tool to help you document your databases. It can produce detailed documents describing your databases
Currently dbdesc supports the following databases:
SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014
Microsoft Desktop Engine 2000 (MSDE) and SQL Server Express editions
MySQL 5.0
Oracle 9 and above
Microsoft Access 97 and above
Firebird
با استفاده از کوئری زیر نیز میتوانید نام و Description ستونها و دیتاتایپ و ... آنها را بدست آورید.
SELECT p.name ,p.value ,t.name AS TableName ,c.name AS ColumnName ,c.is_nullable ,c.max_length ,TYPE_NAME(c.system_type_id) FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id AND p.minor_id = c.column_id ORDER BY t.name DESC,c.name DESC
جهت بررسی بیشتر و آشنایی با Extended Properties in SQL Server به لینک Towards the Self-Documenting SQL Server Database مراجعه کنید.
EF Code First #4
آشنایی با Code first migrations
ویژگی Code first migrations برای اولین بار در EF 4.3 ارائه شد و هدف آن سهولت هماهنگ سازی کلاسهای مدل برنامه با بانک اطلاعاتی است؛ به صورت خودکار یا با تنظیمات دقیق دستی.
همانطور که در قسمتهای قبل نیز به آن اشاره شد، تا پیش از EF 4.3، پنج روال جهت آغاز به کار با بانک اطلاعاتی در EF code first وجود داشت و دارد:
1) در اولین بار اجرای برنامه، در صورتیکه بانک اطلاعاتی اشاره شده در رشته اتصالی وجود خارجی نداشته باشد، نسبت به ایجاد خودکار آن اقدام میگردد. اینکار پس از وهله سازی اولین DbContext و همچنین صدور یک کوئری به بانک اطلاعاتی انجام خواهد شد.
2) DropCreateDatabaseAlways : همواره پس از شروع برنامه، ابتدا بانک اطلاعاتی را drop کرده و سپس نمونه جدیدی را ایجاد میکند.
3) DropCreateDatabaseIfModelChanges : اگر EF Code first تشخیص دهد که تعاریف مدلهای شما با بانک اطلاعاتی مشخص شده توسط رشته اتصالی، هماهنگ نیست، آنرا drop کرده و نمونه جدیدی را تولید میکند.
4) با مقدار دهی پارامتر متد System.Data.Entity.Database.SetInitializer به نال، میتوان فرآیند آغاز خودکار بانک اطلاعاتی را غیرفعال کرد. در این حالت شخص میتواند تغییرات انجام شده در کلاسهای مدل برنامه را به صورت دستی به بانک اطلاعاتی اعمال کند.
5) میتوان با پیاده سازی اینترفیس IDatabaseInitializer، یک آغاز کننده بانک اطلاعاتی سفارشی را نیز تولید کرد.
اکثر این روشها در حین توسعه یک برنامه یا خصوصا جهت سهولت انجام آزمونهای خودکار بسیار مناسب هستند، اما به درد محیط کاری نمیخورند؛ زیرا drop یک بانک اطلاعاتی به معنای از دست دادن تمام اطلاعات ثبت شده در آن است. برای رفع این مشکل مهم، مفهومی به نام «Migrations» در EF 4.3 ارائه شده است تا بتوان بانک اطلاعاتی را بدون تخریب آن، بر اساس اطلاعات تغییر کردهی کلاسهای مدل برنامه، تغییر داد. البته بدیهی است زمانیکه توسط NuGet نسبت به دریافت و نصب EF اقدام میشود، همواره آخرین نگارش پایدار که حاوی اطلاعات و فایلهای مورد نیاز جهت کار با «Migrations» است را نیز دریافت خواهیم کرد.
تنظیمات ابتدایی Code first migrations
در اینجا قصد داریم همان مثال قسمت قبل را ادامه دهیم. در آن مثال از یک نمونه سفارشی سازی شده DropCreateDatabaseAlways استفاده شد.
نیاز است از منوی Tools در ویژوال استودیو، گزینه Library package manager آن، گزینه package manager console را انتخاب کرد تا کنسول پاورشل NuGet ظاهر شود.
اطلاعات مرتبط با پاورشل EF، به صورت خودکار توسط NuGet نصب میشود. برای مثال جهت مشاهده آنها به مسیر packages\EntityFramework.4.3.1\tools در کنار پوشه پروژه خود مراجعه نمائید.
در ادامه در پایین صفحه، زمانیکه کنسول پاورشل NuGet ظاهر میشود، ابتدا باید دقت داشت که قرار است فرامین را بر روی چه پروژهای اجرا کنیم. برای مثال اگر تعاریف DbContext را به یک اسمبلی و پروژه class library مجزا انتقال دادهاید، گزینه Default project را در این قسمت باید به این پروژه مجزا، تغییر دهید.
سپس در خط فرمان پاور شل، دستور enable-migrations را وارد کرده و دکمه enter را فشار دهید.
پس از اجرای این دستور، یک سری اتفاقات رخ خواهد داد:
الف) پوشهای به نام Migrations به پروژه پیش فرض مشخص شده در کنسول پاورشل، اضافه میشود.
ب) دو کلاس جدید نیز در آن پوشه تعریف خواهند شد به نامهای Configuration.cs و یک نام خودکار مانند number_InitialCreate.cs
ج) در کنسول پاور شل، پیغام زیر ظاهر میگردد:
Detected database created with a database initializer. Scaffolded migration '201205050805256_InitialCreate'
corresponding to current database schema. To use an automatic migration instead, delete the Migrations
folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.
با توجه به اینکه در مثال قسمت سوم، از آغاز کننده سفارشی سازی شده DropCreateDatabaseAlways استفاده شده بود، اطلاعات آن در جدول سیستمی dbo.__MigrationHistory در بانک اطلاعاتی برنامه موجود است (تصویری از آنرا در قسمت اول این سری مشاهده کردید). سپس با توجه به ساختار بانک اطلاعاتی جاری، دو کلاس خودکار زیر را ایجاد کرده است:
namespace EF_Sample02.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<EF_Sample02.Sample2Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(EF_Sample02.Sample2Context context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data. E.g.
//
// context.People.AddOrUpdate(
// p => p.FullName,
// new Person { FullName = "Andrew Peters" },
// new Person { FullName = "Brice Lambson" },
// new Person { FullName = "Rowan Miller" }
// );
//
}
}
}
namespace EF_Sample02.Migrations
{
using System.Data.Entity.Migrations;
public partial class InitialCreate : DbMigration
{
public override void Up()
{
CreateTable(
"Users",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(),
LastName = c.String(),
Email = c.String(),
Description = c.String(),
Photo = c.Binary(),
RowVersion = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
Interests_Interest1 = c.String(maxLength: 450),
Interests_Interest2 = c.String(maxLength: 450),
AddDate = c.DateTime(nullable: false),
})
.PrimaryKey(t => t.Id);
CreateTable(
"Projects",
c => new
{
Id = c.Int(nullable: false, identity: true),
Title = c.String(maxLength: 50),
Description = c.String(),
RowVesrion = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
AddDate = c.DateTime(nullable: false),
AdminUser_Id = c.Int(),
})
.PrimaryKey(t => t.Id)
.ForeignKey("Users", t => t.AdminUser_Id)
.Index(t => t.AdminUser_Id);
}
public override void Down()
{
DropIndex("Projects", new[] { "AdminUser_Id" });
DropForeignKey("Projects", "AdminUser_Id", "Users");
DropTable("Projects");
DropTable("Users");
}
}
}
در این کلاس خودکار، نحوه ایجاد جداول بانک اطلاعاتی تعریف شدهاند. در متد تحریف شده Up، کار ایجاد بانک اطلاعاتی و در متد تحریف شده Down، دستورات حذف جداول و قیود ذکر شدهاند.
به علاوه اینبار متد Seed را در کلاس مشتق شده از DbMigrationsConfiguration، میتوان تحریف و مقدار دهی کرد.
علاوه بر اینها جدول سیستمی dbo.__MigrationHistory نیز با اطلاعات جاری مقدار دهی میگردد.
فعال سازی گزینههای مهاجرت خودکار
برای استفاده از این کلاسها، ابتدا به فایل Configuration.cs مراجعه کرده و خاصیت AutomaticMigrationsEnabled را true کنید:
internal sealed class Configuration : DbMigrationsConfiguration<EF_Sample02.Sample2Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
}
پس از آن EF به صورت خودکار کار استفاده و مدیریت «Migrations» را عهدهدار خواهد شد. البته برای این منظور باید نوع آغاز کننده بانک اطلاعاتی را از DropCreateDatabaseAlways قبلی به نمونه جدید MigrateDatabaseToLatestVersion نیز تغییر دهیم:
//Database.SetInitializer(new Sample2DbInitializer());
Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample2Context, Migrations.Configuration>());
یک نکته:
کلاس Migrations.Configuration که باید در حین وهله سازی از MigrateDatabaseToLatestVersion قید شود (همانند کدهای فوق)، از نوع internal sealed معرفی شده است. بنابراین اگر این کلاس را در یک اسمبلی جداگانه قرار دادهاید، نیاز است فایل را ویرایش کرده و internal sealed آنرا به public تغییر دهید.
روش دیگر معرفی کلاسهای Context و Migrations.Configuration، حذف متد Database.SetInitializer و استفاده از فایل app.config یا web.config است به نحو زیر ( در اینجا حرف ` اصطلاحا back tick نام دارد. فشردن دکمه ~ در حین تایپ انگلیسی):
<entityFramework>
<contexts>
<context type="EF_Sample02.Sample2Context, EF_Sample02">
<databaseInitializer
type="System.Data.Entity.MigrateDatabaseToLatestVersion`2[[EF_Sample02.Sample2Context, EF_Sample02],
[EF_Sample02.Migrations.Configuration, EF_Sample02]], EntityFramework"
/>
</context>
</contexts>
</entityFramework>
آزمودن ویژگی مهاجرت خودکار
اکنون برای آزمایش این موارد، یک خاصیت دلخواه را به کلاس Project به نام public string SomeProp اضافه کنید. سپس برنامه را اجرا نمائید.
در ادامه به بانک اطلاعاتی مراجعه کرده و فیلدهای جدول Projects را بررسی کنید:
CREATE TABLE [dbo].[Projects](
---...
[SomeProp] [nvarchar](max) NULL,
---...
بله. اینبار فیلد SomeProp بدون از دست رفتن اطلاعات و drop بانک اطلاعاتی، به جدول پروژهها اضافه شده است.
عکس العمل ویژگی مهاجرت خودکار در مقابل از دست رفتن اطلاعات
در ادامه، خاصیت public string SomeProp را که در قسمت قبل به کلاس پروژه اضافه کردیم، حذف کنید. اکنون مجددا برنامه را اجرا نمائید. برنامه بلافاصله با استثنای زیر متوقف خواهد شد:
Automatic migration was not applied because it would result in data loss.
از آنجائیکه حذف یک خاصیت مساوی است با حذف یک ستون در جدول بانک اطلاعاتی، امکان از دست رفتن اطلاعات در این بین بسیار زیاد است. بنابراین ویژگی مهاجرت خودکار دیگر اعمال نخواهد شد و این مورد به نوعی یک محافظت خودکار است که درنظر گرفته شده است.
البته در EF Code first این مساله را نیز میتوان کنترل نمود. به کلاس Configuration اضافه شده توسط پاورشل مراجعه کرده و خاصیت AutomaticMigrationDataLossAllowed را به true تنظیم کنید:
internal sealed class Configuration : DbMigrationsConfiguration<EF_Sample02.Sample2Context>
{
public Configuration()
{
this.AutomaticMigrationsEnabled = true;
this.AutomaticMigrationDataLossAllowed = true;
}
این تغییر به این معنا است که خودمان صریحا مجوز حذف یک ستون و اطلاعات مرتبط به آنرا صادر کردهایم.
پس از این تغییر، مجددا برنامه را اجرا کنید. ستون SomeProp به صورت خودکار حذف خواهد شد، اما اطلاعات رکوردهای موجود تغییری نخواهند کرد.
استفاده از Code first migrations بر روی یک بانک اطلاعاتی موجود
تفاوت یک دیتابیس موجود با بانک اطلاعاتی تولید شده توسط EF Code first در نبود جدول سیستمی dbo.__MigrationHistory است.
به این ترتیب زمانیکه فرمان enable-migrations را در یک پروژه EF code first متصل به بانک اطلاعاتی قدیمی موجود اجرا میکنیم، پوشه Migration در آن ایجاد خواهد شد اما تنها حاوی فایل Configuration.cs است و نه فایلی شبیه به number_InitialCreate.cs .
بنابراین نیاز است به صورت صریح به EF اعلام کنیم که نیاز است تا جدول سیستمی dbo.__MigrationHistory و فایل number_InitialCreate.cs را نیز تولید کند. برای این منظور کافی است دستور زیر را در خط فرمان پاورشل NuGet پس از فراخوانی enable-migrations اولیه، اجرا کنیم:
add-migration Initial -IgnoreChanges
با بکارگیری پارامتر IgnoreChanges، متد Up در فایل number_InitialCreate.cs تولید نخواهد شد. به این ترتیب نگران نخواهیم بود که در اولین بار اجرای برنامه، تعاریف دیتابیس موجود ممکن است اندکی تغییر کند.
سپس دستور زیر را جهت به روز رسانی جدول سیستمی dbo.__MigrationHistory اجرا کنید:
update-database
پس از آن جهت سوئیچ به مهاجرت خودکار، خاصیت AutomaticMigrationsEnabled = true را در فایل Configuration.cs همانند قبل مقدار دهی کنید.
مشاهده دستوارت SQL به روز رسانی بانک اطلاعاتی
اگر علاقمند هستید که دستورات T-SQL به روز رسانی بانک اطلاعاتی را نیز مشاهده کنید، دستور Update-Database را با پارامتر Verbose آغاز نمائید:
Update-Database -Verbose
و اگر تنها نیاز به مشاهده اسکریپت تولیدی بدون اجرای آنها بر روی بانک اطلاعاتی مدنظر است، از پارامتر Script باید استفاده کرد:
update-database -Script
نکتهای در مورد جدول سیستمی dbo.__MigrationHistory
تنها دلیلی که این جدول در SQL Server البته (ونه برای مثال در SQL Server CE) به صورت سیستمی معرفی میشود این است که «جلوی چشم نباشد»! به این ترتیب در SQL Server management studio در بین سایر جداول معمولی بانک اطلاعاتی قرار نمیگیرد. اما برای EF تفاوتی نمیکند که این جدول سیستمی است یا خیر.
همین سیستمی بودن آن ممکن است بر اساس سطح دسترسی کاربر اتصالی به بانک اطلاعاتی مساله ساز شود. برای نمونه ممکن است schema کاربر متصل dbo نباشد. همینجا است که کار به روز رسانی این جدول متوقف خواهد شد.
بنابراین اگر قصد داشتید خواص سیستمی آنرا لغو کنید، تنها کافی است دستورات T-SQL زیر را در SQL Server اجرا نمائید:
SELECT * INTO [TempMigrationHistory]
FROM [__MigrationHistory]
DROP TABLE [__MigrationHistory]
EXEC sp_rename [TempMigrationHistory], [__MigrationHistory]
ساده سازی پروسه مهاجرت خودکار
کل پروسهای را که در این قسمت مشاهده کردید، به صورت ذیل نیز میتوان خلاصه کرد:
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;
using System.IO;
namespace EF_Sample02
{
public class Configuration<T> : DbMigrationsConfiguration<T> where T : DbContext
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
}
}
public class SimpleDbMigrations
{
public static void UpdateDatabaseSchema<T>(string SQLScriptPath = "script.sql") where T : DbContext
{
var configuration = new Configuration<T>();
var dbMigrator = new DbMigrator(configuration);
saveToFile(SQLScriptPath, dbMigrator);
dbMigrator.Update();
}
private static void saveToFile(string SQLScriptPath, DbMigrator dbMigrator)
{
if (string.IsNullOrWhiteSpace(SQLScriptPath)) return;
var scriptor = new MigratorScriptingDecorator(dbMigrator);
var script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);
File.WriteAllText(SQLScriptPath, script);
Console.WriteLine(script);
}
}
}
سپس برای استفاده از آن خواهیم داشت:
SimpleDbMigrations.UpdateDatabaseSchema<Sample2Context>();
در این کلاس ذخیره سازی اسکریپت تولیدی جهت به روز رسانی بانک اطلاعاتی جاری در یک فایل نیز درنظر گرفته شده است.
تا اینجا مهاجرت خودکار را بررسی کردیم. در قسمت بعدی Code-Based Migrations را ادامه خواهیم داد.
پشتیبانی از XML Schema در SQL Server
XML Schema معرف ساختار، نوع دادهها و المانهای یک سند XML است. البته باید درنظر داشت که تعریف XML Schema کاملا اختیاری است و اگر تعریف شود مزیت اعتبارسنجی دادههای در حال ذخیره سازی در بانک اطلاعاتی را به صورت خودکار به همراه خواهد داشت. در این حالت به نوع دادهای XML دارای اسکیما، typed XML و به نوع بدون اسکیما، untyped XML گفته میشود.
به یک نوع XML، چندین اسکیمای مختلف را میتوان نسبت داد و به آن XML schema collection نیز میگویند.
XML schema collections پیش فرض و سیستمی
تعدادی XML Schema پیش فرض در SQL Server تعریف شدهاند که به آنها sys schema collections گفته میشود.
Prefix - Namespace xml = http://www.w3.org/XML/1998/namespace xs = http://www.w3.org/2001/XMLSchema xsi = http://www.w3.org/2001/XMLSchema-instance fn = http://www.w3.org/2004/07/xpath-functions sqltypes = http://schemas.microsoft.com/sqlserver/2004/sqltypes xdt = http://www.w3.org/2004/07/xpath-datatypes (no prefix) = urn:schemas-microsoft-com:xml-sql (no prefix) = http://schemas.microsoft.com/sqlserver/2004/SOAP
اگر علاقمند باشید تا این تعاریف را مشاهده کنید به مسیر Program Files\Microsoft SQL Server\version\Tools\Binn\schemas\sqlserver در جایی که SQL Server نصب شدهاست مراجعه نمائید. برای مثال در مسیر Tools\Binn\schemas\sqlserver\2006\11\events فایل events.xsd قابل مشاهده است و یا در مسیر Tools\Binn\schemas\sqlserver\2004\07 اسکیمای ابزارهای query processor و show plan قابل بررسی میباشد.
مهمترین آنها را در پوشه Tools\Binn\schemas\sqlserver\2004\sqltypes در فایل sqltypes.xsd میتوانید ملاحظه کنید. اگر به محتوای آن دقت کنید، قسمتی از آن به شرح ذیل است:
<xsd:simpleType name="char"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="nchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="varchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="nvarchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="text"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="ntext"> <xsd:restriction base="xsd:string"/> </xsd:simpleType>
<xsd:simpleType name="datetime"> <xsd:restriction base="xsd:dateTime"> <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]{2}[037])?"/> <xsd:maxInclusive value="9999-12-31T23:59:59.997"/> <xsd:minInclusive value="1753-01-01T00:00:00.000"/> </xsd:restriction> </xsd:simpleType>
تعریف XML Schema و استفاده از آن جهت تعریف یک strongly typed XML
XML Schema مورد استفاده در SQL Server حتما باید در بانک اطلاعاتی ذخیره شود و برای خواندن آن، برای مثال از فایل سیستم استفاده نخواهد شد.
CREATE XML SCHEMA COLLECTION invcol AS '<xs:schema ... targetNamespace="urn:invoices"> ... </xs:schema> ' CREATE TABLE Invoices( id int IDENTITY PRIMARY KEY, invoice XML(invcol) )
در ادامه نحوهی تعریف یک اسکیمای نمونه قابل مشاهده است:
CREATE XML SCHEMA COLLECTION geocol AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:geo" elementFormDefault="qualified" xmlns:tns="urn:geo"> <xs:simpleType name="dim"> <xs:restriction base="xs:int" /> </xs:simpleType> <xs:complexType name="Point"> <xs:sequence> <xs:element name="X" type="tns:dim" minOccurs="0" maxOccurs="unbounded" /> <xs:element name="Y" type="tns:dim" minOccurs="0" maxOccurs="unbounded" /> </xs:sequence> </xs:complexType> <xs:element name="Point" type="tns:Point" /> </xs:schema>'
اکنون برای آزمایش اسکیمای تعریف شده، جدول geo_tab را به نحو ذیل تعریف میکنیم و سپس سعی در insert دو رکورد در آن خواهیم کرد:
declare @geo_tab table( id int identity primary key, point xml(content geocol) ) insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>20</Y></Point>') insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>test</Y></Point>')
در این مثال، insert اول با موفقیت انجام خواهد شد؛ اما insert دوم با خطای ذیل متوقف میشود:
XML Validation: Invalid simple type value: 'test'. Location: /*:Point[1]/*:Y[1]
یافتن محل ذخیره سازی اطلاعات اسکیما در SQL Server
اگر علاقمند باشید تا با محل ذخیره سازی اطلاعات اسکیما، نوعهای تعریف شده و حتی محل استفاده از آنها در بانکهای اطلاعاتی مختلف موجود آشنا شوید و گزارشی از آنها تهیه کنید، میتوانید از کوئریهای ذیل استفاده نمائید:
select * from sys.xml_schema_collections select * from sys.xml_schema_namespaces select * from sys.xml_schema_elements select * from sys.xml_schema_attributes select * from sys.xml_schema_types select * from sys.column_xml_schema_collection_usages select * from sys.parameter_xml_schema_collection_usages
محتوای اسکیمای ذخیره شده به شکل xsd تعریف شده، ذخیره سازی نمیشود. بلکه اطلاعات آن تجزیه شده و سپس در جداول سیستمی SQL Server ذخیره میگردند. هدف از اینکار، بالا بردن سرعت اعتبارسنجی typed XMLها است.
بنابراین بدیهی است در این حالت اطلاعاتی مانند commnets موجود در xsd تهیه شده در بانک اطلاعاتی ذخیره نمیگردند.
برای بازیابی اطلاعات اسکیمای ذخیره شده میتوان از متد xml_schema_namespace استفاده کرد:
declare @x xml select @x = xml_schema_namespace(N'dbo', N'geocol') print convert(varchar(max), @x)
نحوهی ویرایش یک schema collection موجود
چند نکته:
- امکان alter یک schema collection وجود دارد.
- میتوان یک schema جدید را به collection موجود افزود.
- امکان افزودن (و نه تغییر) نوعهای یک schema موجود، میسر است.
- امکان drop یک اسکیما از collection موجودی وجود ندارد. باید کل collection را drop کرد و سپس آنرا تعریف نمود.
- جداولی با فیلدهای nvarchar را میتوان به فیلدهای XML تبدیل کرد و برعکس.
- امکان تغییر یک فیلد XML به حالت untyped و برعکس وجود دارد.
فرض کنید که میخواهیم اسکیمای متناظر با یک ستون XML را تغییر دهیم. ابتدا باید آن ستون XML ایی را Alter کرده و قید اسکیمای آنرا برداریم. سپس باید اسکیمای موجود را drop و مجددا ایجاد کرد. همانطور که پیشتر ذکر شد، اگر اسکیمایی در حال استفاده باشد، قابل drop نیست. در ادامه مجددا باید ستون XML ایی را تغییر داده و اسکیمای آنرا معرفی کرد.
روش دوم مدیریت این مساله، اجازه دادن به حضور بیش از یک اسکیما در مجموعه است. به عبارتی نگارشبندی اسکیما که به نحو ذیل قابل انجام است:
alter XML SCHEMA COLLECTION geocol add @x
نحوهی import یک فایل xsd و ذخیره آن به صورت اسکیما
اگر بخواهیم یک فایل xsd موجود را به عنوان xsd معرفی کنیم میتوان از دستورات ذیل کمک گرفت:
declare @x xml set @x = (select * from openrowset(bulk 'c:\path\file.xsd', single_blob) as x) CREATE XML SCHEMA COLLECTION geocol2 AS @x
از openrowset برای خواندن یک فایل xml موجود، جهت insert محتوای آن در بانک اطلاعاتی نیز میتوان استفاده کرد.
محدودیتهای XML Schema در SQL Server
تمام استاندارد XML Schema در SQL Server پشتیبانی نمیشود و همچنین این مورد از نگارشی به نگارشی دیگر نیز ممکن است تغییر یافته و بهبود یابد. برای مثال در SQL Server 2005 از xs:any پشتیبانی نمیشود اما در SQL Server 2008 این محدودیت برطرف شدهاست. همچنین مواردی مانند xs:include، xs:redefine، xs:notation، xs:key، xs:keyref و xs:unique در SQL Server پشتیبانی نمیشوند.
یک نکتهی تکمیلی
برنامهای به نام xsd.exe به همراه Visual Studio ارائه میشود که قادر است به صورت خودکار از یک فایل XML موجود، XML Schema تولید کند. اطلاعات بیشتر
در قسمت پیشین نشان داده شد که چگونه کاراکترهای خارج از رنج حروف الفبای انگلیسی از عبارات موجود در یک جدول حذف شدند.
اکنون شرایط کمی تغییر کرده است کاراکترهای ناخواسته در قالب یک مجموعه (جدول) به ما ارائه داده میشوند. ما بایستی تمام کاراکترهای داده شده را از عبارات (موجود در جدول) در صورت تطابق حذف کنیم.
جدول کاراکترهای ناخواسته Unwanted و جدول دادهها Data نامگذاری شده اند.
CREATE TABLE Data ( id INTEGER NOT NULL PRIMARY KEY IDENTITY, data VARCHAR(50) NOT NULL ); INSERT INTO Data VALUES ('~!hasan @#$%^&*(reza)[ali]^^^^^^^^'), ('(Ja[][][]va~!@#$*d-mohammad)'), ('Mohammad'), ('Maryam'); CREATE TABLE Unwanted ( id INT NOT NULL PRIMARY KEY IDENTITY, chars CHAR(1) NOT NULL UNIQUE ); INSERT Unwanted VALUES ('~'),('!'),('@'),('#'),('$'),('%'), ('['),(']'),('^'),('&'),('*');
قبل از هر چیزی فرض میگیریم که دادهها ستون Id جدول Unwanted کاملا متوالی نیستند و gap بین مقادیر id وجود دارد. پس برای داشتن مقادیر متوالی از تابع row_number استفاده شده است.
بعد از آن باید عباراتی که حداقل با یک کاراکتر ناخواسته تطابق پیدا میکنند انتخاب شوند. و عملیات پاکسازی روی این عبارات به تعداد کاراکترهای موجود در جدول unwanted انجام میشود. یعنی در مرحله اول شمارنده برابر است با تعداد کاراکترهای ناخواسته سپس در هر فراخوانی یک مقدار از این شمارنده کم شده تا اینکه به 0 برسد در اینجا کار به اتمام میرسد.
بعد از پایان یافتن فراخوانیهای query بازگشتی باید سطرهایی که شمارنده آنها برابر با 0 است انتخاب شده و عباراتی که در مرحله اول به دلیل عدم تطابق انتخاب نشدن بایستی به نتیجه اضافه شوند. این کار را توسط Union all انجام شده است.
WITH pre AS ( SELECT ROW_NUMBER() OVER(ORDER BY id) AS id, chars FROM Unwanted ), cte AS ( SELECT data.id, nbr, CAST(Data AS VARCHAR(50)) AS Data FROM Data CROSS JOIN (SELECT COUNT(*) AS nbr FROM Unwanted)t WHERE EXISTS (SELECT * FROM Unwanted WHERE Data LIKE '%#' + chars + '%' ESCAPE '#') UNION ALL SELECT C.id, nbr - 1, CAST(REPLACE(C.data, U.chars, '') AS VARCHAR(50)) FROM cte AS C INNER JOIN pre U ON C.nbr = U.id WHERE nbr > 0 ) SELECT data FROM cte WHERE nbr = 0 UNION ALL SELECT data FROM Data WHERE NOT EXISTS (SELECT id FROM cte WHERE cte.id = data.id);
public static void CreateUniqueIndex(this DbContext context, string tableName, string fieldName) { context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX [IX_Unique_" + tableName + "_" + fieldName + "] ON [" + tableName + "]([" + fieldName + "] ASC);"); }
public class MyDbMigrationsConfiguration : DbMigrationsConfiguration<MyContext> { public BlogDbMigrationsConfiguration() { AutomaticMigrationsEnabled = true; AutomaticMigrationDataLossAllowed = true; } protected override void Seed(MyContext context) { CreateUniqueIndex(context, "table1", "field1"); base.Seed(context); } }
برای حل این مشکل و تبدیل کدهای فوق به کدهایی Refactoring friendly، نیاز است نام جدول به صورت خودکار از DbContext دریافت شود. همچنین نام خاصیت یا فیلد نیز به صورت strongly typed قابل تعریف باشد.
کدهای کامل نمونه بهبود یافته را در ذیل مشاهده میکنید:
using System; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Objects; using System.Linq; using System.Linq.Expressions; using System.Text.RegularExpressions; namespace General { public static class ContextExtensions { public static string GetTableName<T>(this DbContext context) where T : class { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; return objectContext.GetTableName<T>(); } public static string GetTableName<T>(this ObjectContext context) where T : class { var sql = context.CreateObjectSet<T>().ToTraceString(); var regex = new Regex("FROM (?<table>.*) AS"); var match = regex.Match(sql); string table = match.Groups["table"].Value; return table .Replace("`", string.Empty) .Replace("[", string.Empty) .Replace("]", string.Empty) .Replace("dbo.", string.Empty) .Trim(); } private static bool hasUniqueIndex(this DbContext context, string tableName, string indexName) { var sql = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = '" + tableName + "' and CONSTRAINT_NAME = '" + indexName + "'"; var result = context.Database.SqlQuery<int>(sql).FirstOrDefault(); return result > 0; } private static void createUniqueIndex(this DbContext context, string tableName, string fieldName) { var indexName = "IX_Unique_" + tableName + "_" + fieldName; if (hasUniqueIndex(context, tableName, indexName)) return; var sql = "ALTER TABLE [" + tableName + "] ADD CONSTRAINT [" + indexName + "] UNIQUE ([" + fieldName + "])"; context.Database.ExecuteSqlCommand(sql); } public static void CreateUniqueIndex<TEntity>(this DbContext context, Expression<Func<TEntity, object>> fieldName) where TEntity : class { var field = ((MemberExpression)fieldName.Body).Member.Name; createUniqueIndex(context, context.GetTableName<TEntity>(), field); } } }
توضیحات
متد GetTableName ، به کمک SQL تولیدی حین تعریف جدول متناظر با کلاس جاری، نام جدول را با استفاده از عبارات باقاعده جدا کرده و باز میگرداند. به این ترتیب به دقیقترین نامی که واقعا جهت تولید جدول مورد استفاده قرار گرفته است خواهیم رسید.
در مرحله بعد آن، همان متد createUniqueIndex ابتدای بحث را ملاحظه میکنید. در اینجا جهت حفظ سازگاری بین SQL Server کامل و SQL CE از UNIQUE CONSTRAINT استفاده شده است که همان کار ایجاد ایندکس منحصربفرد را نیز انجام میدهد. به علاوه مزیت دیگر آن امکان دسترسی به تعاریف قید اضافه شده توسط view ایی به نام INFORMATION_SCHEMA.TABLE_CONSTRAINTS است که در نگارشهای مختلف SQL Server به یک نحو تعریف گردیده و قابل دسترسی است. از این view در متد hasUniqueIndex جهت بررسی تکراری نبودن UNIQUE CONSTRAINT در حال تعریف، استفاده میشود. اگر این قید پیشتر تعریف شده باشد، دیگر سعی در تعریف مجدد آن نخواهد شد.
متد CreateUniqueIndex تعریف شده در انتهای کلاس فوق، امکان دریافت نام خاصیتی از TEntity را به صورت strongly typed میسر میسازد.
اینبار برای تعریف یک قید و یا ایندکس منحصربفرد بر روی خاصیتی مشخص در متد Seed، تنها کافی است بنویسیم:
context.CreateUniqueIndex<User>(x=>x.Name);
خوب، این فیلد کمتر بحث شده XML، فقط در اس کیوال سرور و نگارشهای اخیر آن وجود دارد. اگر نیاز به کار با بانکهای اطلاعاتی سبکتری وجود داشت چطور؟ یک راه حل عمومی برای این مساله مراجعه به روشهای NoSQL است. یعنی بطور کلی بانکهای اطلاعاتی رابطهای کنار گذاشته شده و به یک سکوی کاری دیگر سوئیچ کرد. در این بین، SisoDb راه حل میانهای را ارائه داده است. با کمک SisoDb میتوان اطلاعات را به صورت schema less و بدون نیاز به تعریف فیلدهای متناظر آنها، در انواع و اقسام بانکهای اطلاعاتی SQL Server با فرمت JSON ذخیره و بازیابی کرد. این انواع و اقسام، شامل SQL Server CE نیز میشود.
دریافت و نصب SisoDb
دریافت و نصب SisoDb بسیار ساده است. به کمک package manager و امکانات NuGet، کلمه Sisodb را جستجو کنید. در بین مداخل ظاهر شده، پروایدر مورد علاقه خود را انتخاب و نصب نمائید. برای مثال اگر قصد دارید با SQL Server CE کار کنید، SisoDb.SqlCe4 را انتخاب و یا اگر SQL Server 2008 مدنظر شما است، SisoDb.Sql2008 را انتخاب و نصب نمائید.
ثبت و بازیابی اطلاعات به کمک SisoDb
کار با SisoDb بسیار روان است. نیازی به تعاریف نگاشتها و ORM خاصی نیست. یک مثال مقدماتی آنرا در ادامه ملاحظه میکنید:
using SisoDb.Sql2008; namespace SisoDbTests { public class Customer { public int Id { get; set; } public int CustomerNo { get; set; } public string Name { get; set; } } class Program { static void Main(string[] args) { /*var cnInfo = new SqlCe4ConnectionInfo(@"Data source=sisodb2013.sdf;"); var db = new SqlCe4DbFactory().CreateDatabase(cnInfo); db.EnsureNewDatabase();*/ var cnInfo = new Sql2008ConnectionInfo(@"Data Source=(local);Initial Catalog=sisodb2013;Integrated Security = true"); var db = new Sql2008DbFactory().CreateDatabase(cnInfo); db.EnsureNewDatabase(); var customer = new Customer { CustomerNo = 20, Name = "Vahid" }; db.UseOnceTo().Insert(customer); using (var session = db.BeginSession()) { var info = session.Query<Customer>().Where(c => c.CustomerNo == 20).FirstOrDefault(); var info2 = session.Query<Customer>().Where(c => c.CustomerNo == 20 && c.Name=="Vahid").FirstOrDefault(); } } } }
ساختار داخلی SisoDb
SisoDb به ازای هر کلاس، حداقل 9 جدول را ایجاد میکند. در ادامه نحوه ذخیره سازی شیء مشتری ایجاد شده و مقادیر خواص آنرا نیز مشاهده مینمائید:
همانطور که ملاحظه میکنید، یک جدول کلی SisoDbIdentities ایجاد شده است که اطلاعات نام اشیاء را در خود نگهداری میکند. سپس اطلاعات خواص اشیاء یکبار به صورت JSON ذخیره میشوند؛ با تمام اطلاعات تو در توی ذخیره شده در آنها و همچنین یکبار هم هر خاصیت را به صورت یک رکورد جداگانه، بر اساس نوع کلی آنها، در جداول رشتهای، عددی و امثال آن ذخیره میکند.
شاید بپرسید که چرا به همان فیلد رشتهای JSON اکتفاء نشده است؟ از این جهت که پردازشگر سمت بانک اطلاعاتی آن همانند فیلدهای XML در SQL Server و نگارشهای مختلف آن وجود ندارد (برای مثال به کمک زبان T-SQL میتوان از زبان XQuery در خود بانک اطلاعاتی، بدون نیاز به واکشی کل اطلاعات در سمت کلاینت، به صورت یکپارچه استفاده کرد). به همین جهت برای کوئری گرفتن و یا تهیه ایندکس، نیاز است این موارد جداگانه ذخیره شوند.
به این ترتیب زمانیکه کوئری تهیه میشود، برای مثال:
var info = session.Query<Customer>().Where(c => c.CustomerNo == 20).FirstOrDefault();
SELECT DISTINCT TOP(1) (s.[StructureId]), s.[Json] FROM [CustomerStructure] s LEFT JOIN [CustomerIntegers] mem0 ON mem0.[StructureId] = s.[StructureId] AND mem0.[MemberPath] = 'CustomerNo' WHERE (mem0.[Value] = 20);
var info2 = session.Query<Customer>().Where(c => c.CustomerNo == 20 && c.Name=="Vahid").FirstOrDefault();
SELECT DISTINCT TOP(1) (s.[StructureId]), s.[Json] FROM [CustomerStructure] s LEFT JOIN [CustomerIntegers] mem0 ON mem0.[StructureId] = s.[StructureId] AND mem0.[MemberPath] = 'CustomerNo' LEFT JOIN [CustomerStrings] mem1 ON mem1.[StructureId] = s.[StructureId] AND mem1.[MemberPath] = 'Name' WHERE ((mem0.[Value] = 20) AND (mem1.[Value] = 'Vahid'));
در کل این هم یک روش تفکر و طراحی Schema less است که با بسیاری از بانکهای اطلاعاتی موجود سازگاری دارد.
برای مشاهده اطلاعات بیشتری در مورد جزئیات این روش میتوان به Wiki آن مراجعه کرد.
نحوهی فعالسازی Batching در EF Core
Batching به صورت پیش فرض در EF Core بدون نیاز به هیچگونه تنظیم اضافهتری فعال است. اما اگر خواستید برای مثال، حالت پیش فرض EF 6.x را توسط آن شبیه سازی کنید، میتوانید مقدار MaxBatchSize را به عدد 1 تنظیم نمائید (تا غیرفعال شود):
optionsBuilder.UseSqlServer( @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;", options => options.MaxBatchSize(1) );
مقدار پیش فرض MaxBatchSize را در کلاس SqlServerModificationCommandBatch میتوانید مشاهده کنید:
public class SqlServerModificationCommandBatch : AffectedCountModificationCommandBatch { private const int DefaultNetworkPacketSizeBytes = 4096; private const int MaxScriptLength = 65536 * DefaultNetworkPacketSizeBytes / 2; private const int MaxParameterCount = 2100; private const int MaxRowCount = 1000;
آیا محدودیتی هم در مورد عملیات Batching وجود دارد؟
SQL Server به ازای هر batch تنها 2100 پارامتر را پشتیبانی میکند. در این حالت EF Core به صورت خودکار یک چنین کوئریهای حجیمی را به چند Batch جهت تنظیم این محدودیت تقسیم خواهد کرد و در نهایت برنامه به مشکلی بر نمیخورد.
یک آزمایش: Batching پیش فرض به چه صورتی کار میکند و چه اثری را دارد؟
کدهای کامل این آزمایش را از اینجا میتوانید دریافت کنید: Batching.zip
در اینجا کلاس Blog را به همراه Context متناظر با آن مشاهده میکنید:
public class Blog { public int BlogId { get; set; } public string Name { get; set; } public string Url { get; set; } } public class BloggingContext : DbContext { public DbSet<Blog> Blogs { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer( @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;"/*, options => options.MaxBatchSize(2)*/ ); optionsBuilder.EnableSensitiveDataLogging(); } }
در این حالت اگر به روز رسانیها (2 مورد) و ثبتهای ذیل (6 مورد) را انجام دهیم:
using (var db = new BloggingContext()) { db.GetService<ILoggerFactory>().AddProvider(new MyLoggerProvider()); // Modify some existing blogs var existing = db.Blogs.ToArray(); existing[0].Url = "http://sample.com/blogs/dogs"; existing[1].Url = "http://sample.com/blogs/cats"; // Insert some new blogs db.Blogs.Add(new Blog { Name = "The Horse Blog", Url = "http://sample.com/blogs/horses" }); db.Blogs.Add(new Blog { Name = "The Snake Blog", Url = "http://sample.com/blogs/snakes" }); db.Blogs.Add(new Blog { Name = "The Fish Blog", Url = "http://sample.com/blogs/fish" }); db.Blogs.Add(new Blog { Name = "The Koala Blog", Url = "http://sample.com/blogs/koalas" }); db.Blogs.Add(new Blog { Name = "The Parrot Blog", Url = "http://sample.com/blogs/parrots" }); db.Blogs.Add(new Blog { Name = "The Kangaroo Blog", Url = "http://sample.com/blogs/kangaroos" }); db.SaveChanges(); }
Executed DbCommand (41ms) [Parameters=[@p1='57', @p0='http://sample.com/blogs/dogs' (Size = 4000), @p3='58', @p2='http://sample.com/blogs/cats' (Size = 4000), @p4='The Horse Blog' (Size = 4000), @p5='http://sample.com/blogs/horses' (Size = 4000), @p6='The Snake Blog' (Size = 4000), @p7='http://sample.com/blogs/snakes' (Size = 4000), @p8='The Fish Blog' (Size = 4000), @p9='http://sample.com/blogs/fish' (Size = 4000), @p10='The Koala Blog' (Size = 4000), @p11='http://sample.com/blogs/koalas' (Size = 4000), @p12='The Parrot Blog' (Size = 4000), @p13='http://sample.com/blogs/parrots' (Size = 4000), @p14='The Kangaroo Blog' (Size = 4000), @p15='http://sample.com/blogs/kangaroos' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; UPDATE [Blogs] SET [Url] = @p0 WHERE [BlogId] = @p1; SELECT @@ROWCOUNT; UPDATE [Blogs] SET [Url] = @p2 WHERE [BlogId] = @p3; SELECT @@ROWCOUNT; DECLARE @inserted2 TABLE ([BlogId] int, [_Position] [int]); MERGE [Blogs] USING ( VALUES (@p4, @p5, 0), (@p6, @p7, 1), (@p8, @p9, 2), (@p10, @p11, 3), (@p12, @p13, 4), (@p14, @p15, 5)) AS i ([Name], [Url], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Name], [Url]) VALUES (i.[Name], i.[Url]) OUTPUT INSERTED.[BlogId], i._Position INTO @inserted2; SELECT [t].[BlogId] FROM [Blogs] t INNER JOIN @inserted2 i ON ([t].[BlogId] = [i].[BlogId]) ORDER BY [i].[_Position];
- فقط یکبار Executed DbCommand مشاهده میشود.
- کل دستورات update و insert در طی یک درخواست و یک تراکنش به سمت بانک اطلاعاتی ارسال شدهاند.
- ثبت دستهای توسط merge using انجام شدهاست.
- در آخر نیز طبق معمول کار EF، شماره Idهای رکوردهای ثبت شده به سمت کلاینت بازگشت داده میشود.
در ادامه MaxBatchSize را به عدد 2 تنظیم میکنیم:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer( @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;", options => options.MaxBatchSize(2) ); optionsBuilder.EnableSensitiveDataLogging(); }
Executed DbCommand (17ms) [Parameters=[@p1='65', @p0='http://sample.com/blogs/dogs' (Size = 4000), @p3='66', @p2='http://sample.com/blogs/cats' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; UPDATE [Blogs] SET [Url] = @p0 WHERE [BlogId] = @p1; SELECT @@ROWCOUNT; UPDATE [Blogs] SET [Url] = @p2 WHERE [BlogId] = @p3; SELECT @@ROWCOUNT; Executed DbCommand (18ms) [Parameters=[@p0='The Horse Blog' (Size = 4000), @p1='http://sample.com/blogs/horses' (Size = 4000), @p2='The Snake Blog' (Size = 4000), @p3='http://sample.com/blogs/snakes' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]); MERGE [Blogs] USING ( VALUES (@p0, @p1, 0), (@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Name], [Url]) VALUES (i.[Name], i.[Url]) OUTPUT INSERTED.[BlogId], i._Position INTO @inserted0; SELECT [t].[BlogId] FROM [Blogs] t INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId]) ORDER BY [i].[_Position]; Executed DbCommand (34ms) [Parameters=[@p0='The Fish Blog' (Size = 4000), @p1='http://sample.com/blogs/fish' (Size = 4000), @p2='The Koala Blog' (Size = 4000), @p3='http://sample.com/blogs/koalas' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]); MERGE [Blogs] USING ( VALUES (@p0, @p1, 0), (@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Name], [Url]) VALUES (i.[Name], i.[Url]) OUTPUT INSERTED.[BlogId], i._Position INTO @inserted0; SELECT [t].[BlogId] FROM [Blogs] t INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId]) ORDER BY [i].[_Position]; Executed DbCommand (15ms) [Parameters=[@p0='The Parrot Blog' (Size = 4000), @p1='http://sample.com/blogs/parrots' (Size = 4000), @p2='The Kangaroo Blog' (Size = 4000), @p3='http://sample.com/blogs/kangaroos' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]); MERGE [Blogs] USING ( VALUES (@p0, @p1, 0), (@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Name], [Url]) VALUES (i.[Name], i.[Url]) OUTPUT INSERTED.[BlogId], i._Position INTO @inserted0; SELECT [t].[BlogId] FROM [Blogs] t INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId]) ORDER BY [i].[_Position];
- اینبار تعداد 4 دستور Executed DbCommand مشاهده میشود ( برای انجام 2 به روز رسانی و 6 ثبت).
- هر batch بر اساس تنظیم MaxBatchSize به 2 دستور T-SQL محدود شدهاست که البته در انتها در حالتهای insert، یک select هم برای بازگشت Idها به سمت کلاینت وجود دارد.
بنابراین اینبار بجای یکبار رفت و برگشت حالت قبل (استفاده از مقدار پیش فرض 1000 برای MaxBatchSize)، 4 بار رفت و برگشت به سمت بانک اطلاعاتی صورت گرفتهاست.
زمان کل انجام عملیات در حالت اول 41 میلی ثانیه و در حالت دوم 84 میلی ثانیه است که سرعت آن 51 درصد نسبت به حالت اول کاهش یافتهاست.