private static void GetAdsWithInclude(AdsContext context) { var ads = context.Ads .Include(a => a.AdStatus) .Include(a => a.Category) .Include(a => a.Town) .Include(a => a.AspNetUser); PrintAds(ads); }
private static void GetOptimizedQueryExecutionTime(AdsContext context) { var ads = context.Ads .Where(a => a.AdStatus.Status == "Published") .OrderBy(a => a.Date) .Select(a => new { a.Title, a.Category, a.Town }).ToList(); }
public static void Main() { var context = new AdsContext(); GetAdsNoInclude(context); GetAdsWithInclude(context); // +--------------------------+------------+--------------+ // | | No Include | With Include | // +--------------------------+------------+--------------+ // | Number of SQL statements | 28 | 1 | // +--------------------------+------------+--------------+ }
public static void Main() { var context = new AdsContext(); var sw = new Stopwatch(); context.Ads.Count(); CleanCache(context); sw.Start(); GetNonOptimizedQueryExecutionTime(context); Console.WriteLine("Non-optimized: {0}", sw.ElapsedMilliseconds); CleanCache(context); sw = Stopwatch.StartNew(); GetOptimizedQueryExecutionTime(context); Console.WriteLine("Optimized: {0}", sw.ElapsedMilliseconds); // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ // | | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average Time | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ // | Non-optimized (ms) | 230 | 239 | 235 | 243 | 233 | 236 | 232 | 233 | 246 | 242 | 237 | // | Optimized (ms) | 20 | 19 | 21 | 20 | 20 | 19 | 19 | 21 | 19 | 19 | 20 | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ }
public static void Main() { var context = new AdsContext(); var sw = new Stopwatch(); context.Ads.Count(); CleanCache(context); sw.Start(); GetNonOptimizedQueryExecutionTime(context); Console.WriteLine("Non-optimized: {0}", sw.ElapsedMilliseconds); CleanCache(context); sw = Stopwatch.StartNew(); GetOptimizedQueryExecutionTime(context); Console.WriteLine("Optimized: {0}", sw.ElapsedMilliseconds); // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ // | | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average Time | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ // | Non-optimized (ms) | 461 | 465 | 461 | 480 | 465 | 531 | 469 | 470 | 485 | 463 | 475 | // | Optimized (ms) | 229 | 230 | 247 | 219 | 226 | 274 | 248 | 236 | 242 | 224 | 238 | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ }
private static void CleanCache(AdsContext context) { context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE"); }
private static void GetAdsNoInclude(AdsContext context) { var ads = context.Ads; PrintAds(ads); }
private static void GetOptimizedQueryExecutionTime(AdsContext context) { var ads = context.Ads .Select(a => a.Title) .ToList(); }
private static void GetNonOptimizedQueryExecutionTime(AdsContext context) { var ads = context.Ads.ToList(); }