در ادامهی مطلب قبلی، نکاتی دیگر را جهت افزایش کارآیی سیستمهای مبتنی بر EF اشاره خواهیم کرد:
عدم استفاده از کوئریهای کلی
فرض کنید در یک فرم جستجو، 4 تکست باکس FirstName, LastName, City و PostalZipCode برای عملیات جستجو در نظر گرفته شده است و کاربر میتواند بر اساس آنها جستجو را انجام دهد.
var searchModel = new Pupil { FirstName = "Ben", LastName = null, City = null, PostalZipCode = null }; List<Pupil> pupils = db.Pupils.Where(p => (searchModel.FirstName == null || p.FirstName == searchModel.FirstName) && (searchModel.LastName == null || p.LastName == searchModel.LastName) && (searchModel.City == null || p.LastName == searchModel.City) && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode)) .Take(100) .ToList()
USE [EFSchoolSystem] DECLARE @p__linq__0 NVarChar(4000) SET @p__linq__0 = 'Ben' DECLARE @p__linq__1 NVarChar(4000) SET @p__linq__1 = 'Ben' DECLARE @p__linq__2 NVarChar(4000) SET @p__linq__2 = '' DECLARE @p__linq__3 NVarChar(4000) SET @p__linq__3 = '' DECLARE @p__linq__4 NVarChar(4000) SET @p__linq__4 = '' DECLARE @p__linq__5 NVarChar(4000) SET @p__linq__5 = '' DECLARE @p__linq__6 NVarChar(4000) SET @p__linq__6 = '' DECLARE @p__linq__7 NVarChar(4000) SET @p__linq__7 = '' -- Executed query SELECT TOP (100) [Extent1].[PupilId] AS [PupilId] , [Extent1].[FirstName] AS [FirstName] , [Extent1].[LastName] AS [LastName] , [Extent1].[Address1] AS [Address1] , [Extent1].[Adderss2] AS [Adderss2] , [Extent1].[PostalZipCode] AS [PostalZipCode] , [Extent1].[City] AS [City] , [Extent1].[PhoneNumber] AS [PhoneNumber] , [Extent1].[SchoolId] AS [SchoolId] , [Extent1].[Picture] AS [Picture] FROM [dbo].[Pupils] AS [Extent1] WHERE (@p__linq__0 IS NULL OR [Extent1].[FirstName] = @p__linq__1) AND (@p__linq__2 IS NULL OR [Extent1].[LastName] = @p__linq__3) AND (@p__linq__4 IS NULL OR [Extent1].[LastName] = @p__linq__5) AND (@p__linq__6 IS NULL OR [Extent1].[PostalZipCode] = @p__linq__7)
مشکلی که در این دسته از کوئریهای عمومی ایجاد میگردد آن است که ممکن است پلنی که برای یک گروه از پارامترهای ورودی مناسب باشد (جستجو بر اساس نام) برای سایر پارامترهای ورودی نامناسب باشد. تصور کنید تمام دانش آموزان، در شهر نیویورک یا بوستون زندگی میکنند. بنابراین این ستون از تنوع انتخاب کمتری برخوردار است در مقایسه با ستون نام خانوادگی و فرض کنید پلن، براساس پارامتر شهر ایجاد شده است. بنابراین ایجاد این پلن برای سایر پارامترها از کارآیی کافی برخوردار نیست. این مشکل با نام Bad Parameter Sniffing شناخته میشود و دربارهی Parameter Sniffing در اینجا به تفصیل اشاره شده است.
این مشکل زمانی بیشتر مشکل ساز خواهد شد که 99 درصد دانش آموزان در شهر نیویورک و فقط 1 درصد آنها در شهر بوستون زندگی میکنند و پلن ایجاد شده بر اساس پارامتر شهر بوستون باشد.
راه حل اول:
برای حل این مشکل تنها یک راه حل خاص وجود ندارد و باید براساس شرایط برنامه، کوئری از حالت عمومی خارج گردد؛ مانند زیر:
if (searchModel.City == null) { pupils = db.Pupils.Where(p => (searchModel.FirstName == null || p.FirstName == searchModel.FirstName) && (searchModel.LastName == null || p.LastName == searchModel.LastName) && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode)) .Take(100) .ToList(); } else { pupils = db.Pupils.Where(p => (searchModel.FirstName == null || p.FirstName == searchModel.FirstName) && (searchModel.LastName == null || p.LastName == searchModel.LastName) && (searchModel.City == null || p.LastName == searchModel.City) && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode)) .Take(100) .ToList(); }
راه حل دوم:
کامپایل مجدد پلن در اجرای هر کوئری، اما این راه حل سرباری را تحمیل میکند. بدین منظور مترجم زیر را ایجاد کنید:
public class RecompileDbCommandInterceptor : IDbCommandInterceptor { public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { if(!command.CommandText.EndsWith(" option(recompile)")) { command.CommandText += " option(recompile)"; } } //and implement other interface members }
var interceptor = new RecompileDbCommandInterceptor(); DbInterception.Add(interceptor); var pupils = db.Pupils.Where(p => p.City = city).ToList(); DbInterception.Remove(interceptor);
راه حل سوم:
استفاده از اجرای به تعویق افتاده به شکل زیر است:
var result = db.Pupils.AsQueryable(); if(searchModel.FirstName != null ) result = result.Where(p => p.FirstName == searchModel.FirstName); if(searchModel.LastName != null ) result = result.Where(p => p.LastName == searchModel.LastName); if(searchModel.PostalZipCode != null ) result = result.Where(p => p.PostalZipCode == searchModel.PostalZipCode); if(searchModel.City != null ) result = result.Where(p => p.City == searchModel.City);
افزونگی کشِ پلن
استفادهی مجدد از پلن بدلیل عدم ایجاد مجدد آن در زمان اجرای هر کوئری، بسیار خوب است. برای استفادهی مجدد از پلن، باید دستورات ارسالی یکسان باشند؛ مانند کوئریهای پارامتریک. در EF هنگامیکه از متغیرها استفاده کنید، کوئریها پارامتریک تولید میکند؛ اما یک استثناء وجود دارد: ()Skip و ()Take
2 متد فوق بیشتر جهت صفحه بندی استفاده میشوند:
var schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(model.Page * model.ResultsPerPage) .Take(model.ResultsPerPage) .ToList();
حال اگر قصد دریافت اطلاعات صفحهی 500 را داشته باشید، مقادیر کوئری بعدی بترتیب 100 و 50000 خواهد بود و بجای مقادیر تصویر بالا 100 و 50000 قرار داده میشوند و کوئری متفاوتی با پلن متفاوتی ایجاد خواهد شد و اس کیو ال پلن کوئری قبلی را مورد استفاده قرار نخواهد داد و با اجرای کوئری دوم، پلن متفاوتی ایجاد خواهد کرد که این باعث ایجاد افزونگی پلنها خواهد شد و همانگونه که قبلا اشاره شد ایجاد پلن جدید هزینه بر است.
نکته: جهت مشاهده پلنهای کش شده در اس کیو ال، دستور زیر اجرا کنید:
SELECT text, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle)
- صدمه به کارآیی؛ هربار EF یک کوئری و اس کیو ال یک پلن جدید را ایجاد میکنند.
- افزایش اشغال حافظه؛ کش شدن کوئریها توسط EF سمت کلاینت و کش شدن پلنها در اس کیو ال سرور (کش بی رویهی پلنها باعث حذف سایر پلنهای مورد استفاده بدلیل محدودیت حافظه میشود که امکان بروز اختلال در کارآیی را بههمراه خواهد داشت.)
علت بروز مشکل:
هنگامیکه یک مقدار int، به متدهای ()Skip و ()Take ارسال میشود، EF نمیتواند تشخیص دهد این مقدار ارسالی ثابت (absolute) مانند (100)Take است یا توسط یک متغیر مانند (متغیر)Take تولید شده است. به همین خاطر EF مقدار ارسال شده را پارامتریک در نظر نمیگیرد.
راه حل:
در EF6 پیاده سازی دیگری برای متدهای ()Skip و ()Take ارائه شده است که برای حل مشکل فوق میتوان به کار گرفت، این پیاده سازی امکان دریافت lambada بجای int را دارد که باعث ایجاد کوئریهای پارامتریک خواهد شد.
int resultsToSkip = model.Page * model.ResultsPerPage; var schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(() => resultsToSkip) //must pre-calculate this value .Take(() => model.ResultsPerPage) .ToList();
همانطور که مشاهده میکنید این بار EF کوئری پارامتریک ایجاد و ارسال کرده است.