// Note: I have referenced the AdsEntities from the previous project // instead of creating another one in this project. static void Main() { /* * Using Entity Framework select all ads from the database, * then invoke ToList(), then filter the categories whose status is Published; * then select the ad title, category and town, then invoke ToList() * again and finally order the ads by publish date. * Rewrite the same query in a more optimized way and compare the performance. */ var context = new AdsEntities(); Stopwatch sw = new Stopwatch(); sw.Start(); for (int count = 0; count < 10; count++) { var ads = context.Ads .Select(a => a) .ToList() .Where(a => a.AdStatus != null && a.AdStatus.Status == "Published") .Select(a => new { Title = a.Title, Category = a.Category != null ? a.Category.Name : "(null)", Town = a.Town != null ? a.Town.Name : "(null)", PublishDate = a.Date }) .ToList() .OrderBy(a => a.PublishDate); } // Unoptimised: 5 seconds Console.WriteLine("Unoptimised: {0}", sw.Elapsed); sw.Restart(); for (int count = 0; count < 10; count++) { var ads = context.Ads .Where(a => a.AdStatus != null && a.AdStatus.Status == "Published") .Select(a => new { Title = a.Title, Category = a.Category != null ? a.Category.Name : "(null)", Town = a.Town != null ? a.Town.Name : "(null)", PublishDate = a.Date }) .OrderBy(a => a.PublishDate) .ToList(); } // Optimised: 0.5 seconds Console.WriteLine("Optimised: {0}", sw.Elapsed); sw.Stop(); }
// Note: I have referenced the AdsEntities from the previous project // instead of creating another one in this project. static void Main() { /* * Write a program to compare the execution speed between these two scenarios: * Select everything from the Ads table and print only the ad title. * Select the ad title from Ads table and print it. */ var context = new AdsEntities(); Stopwatch sw = new Stopwatch(); sw.Start(); for (int count = 0; count < 10; count++) { var ads = context.Ads.Select(a => a); foreach (var ad in ads) { Console.WriteLine(ad.Title); } } TimeSpan unoptimisedTime = sw.Elapsed; sw.Restart(); for (int count = 0; count < 10; count++) { var ads = context.Ads.Select(a => a.Title); foreach (var adTitle in ads) { Console.WriteLine(adTitle); } } TimeSpan optimisedTime = sw.Elapsed; sw.Stop(); /* * Unoptimised: 5.5 seconds * Optimised: 0.2 seconds */ Console.WriteLine(); Console.WriteLine("Unoptimised: {0}", unoptimisedTime); Console.WriteLine("Optimised: {0}", optimisedTime); }
static void Main() { var context = new AdsEntities(); var ads = context.Ads.Select(a => a); // Without Include. - Around ~15 queries foreach (var ad in ads) { Console.WriteLine(" --- Title: {0} - Status: {1} - Category: {2}", ad.Title, ad.AdStatus != null ? ad.AdStatus.Status : "(null)" , ad.Category != null ? ad.Category.Name : "(null)"); Console.WriteLine(" -- Town: {0} - Author: {1}", ad.Town != null ? ad.Town.Name : "(null)", ad.AspNetUser != null ? ad.AspNetUser.UserName : "******"); } var adsWithInclude = context.Ads .Select(a => a) .Include(a => a.Town) .Include(a => a.AdStatus) .Include(a => a.Category) .Include(a => a.AspNetUser); // With Include. Only 1 query foreach (var ad in adsWithInclude) { Console.WriteLine(" --- Title: {0} - Status: {1} - Category: {2}", ad.Title, ad.AdStatus != null ? ad.AdStatus.Status : "(null)", ad.Category != null ? ad.Category.Name : "(null)"); Console.WriteLine(" -- Town: {0} - Author: {1}", ad.Town != null ? ad.Town.Name : "(null)", ad.AspNetUser != null ? ad.AspNetUser.UserName : "******"); } }