private static void GetAdsWhereStatusPublishedOptimized(AdsEntities context) { var ads = context.Ads .Where(a => a.AdStatus.Status == "Published") .Select(a => new { Title = a.Title, Category = a.Category == null ? "No category" : a.Category.Name, Town = a.Town == null ? "No town" : a.Town.Name, PublishDate = a.Date }) .OrderBy(a => a.PublishDate); foreach (var ad in ads) { Console.WriteLine("Title: {0}, Category: {1}, Town: {2}, Publish Date: {3}", ad.Title, ad.Category, ad.Town, ad.PublishDate); } }
static void Main(string[] args) { var context = new AdsEntities(); // Problem 1. Show Data from Related Tables // Using Entity Framework write a SQL query to select all ads from the database and later print their title, // status, category, town and user. Do not use Include(…) for the relationships of the Ads. // Check how many SQL commands are executed with the SQL ExpressProfiler (or a similar tool). /* foreach (var ad in context.Ads) { Console.WriteLine("Title: {0}, Status: {1}, Category: {2}, Town: {3}, User: {4}", ad.Title, ad.AdStatus == null ? "No status": ad.AdStatus.Status, ad.Category == null ? "No category":ad.Category.Name, ad.Town == null ? "No town":ad.Town.Name, ad.AspNetUser.Name); } */ // 27 commands executed // Add Include(…) to select statuses, categories, towns and users along with all ads. // Compare the number of executed SQL statements and the performance before and after adding Include(…). /* foreach (var ad in context.Ads .Include(a => a.AdStatus) .Include(a => a.Category) .Include(a => a.Town) .Include(a => a.AspNetUser)) { Console.WriteLine("Title: {0}, Status: {1}, Category: {2}, Town: {3}, User: {4}", ad.Title, ad.AdStatus == null ? "No status" : ad.AdStatus.Status, ad.Category == null ? "No category" : ad.Category.Name, ad.Town == null ? "No town" : ad.Town.Name, ad.AspNetUser.Name); } */ // 1 command executed. //---------------------------------------------------------------------------------------------------// // Problem 2. Play with ToList() // 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 sw = new Stopwatch(); // sw.Start(); // not optimize version /* GetAdsWhereStatusPublishedWithToListNonOptimized(context); Console.WriteLine(sw.Elapsed); */ // sw.Restart(); // optimize version /* GetAdsWhereStatusPublishedOptimized(context); Console.WriteLine(sw.Elapsed); */ // Problem 3. Select Everything vs. Select Certain Columns var sw = new Stopwatch(); sw.Start(); SelectEverythingOfAds(context); Console.WriteLine(sw.Elapsed); sw.Restart(); SelectTitleOfAds(context); Console.WriteLine(sw.Elapsed); }
private static void SelectTitleOfAds(AdsEntities context) { var ads = context.Ads .Select(a => a.Title) .ToList(); foreach (var ad in ads) { Console.WriteLine(ad); } Console.WriteLine(); }
private static void SelectEverythingOfAds(AdsEntities context) { var ads = context.Ads.ToList(); foreach (var ad in ads) { Console.WriteLine(ad.Title); } Console.WriteLine(); }
static void Main(string[] args) { // --------------Problem 1 --------------- var context = new AdsEntities(); //var ads = context.Ads; //foreach (var ad in ads) //{ // Console.WriteLine("Title: {0}", ad.Title); // Console.WriteLine("Status: {0}", ad.AdStatus.Status); // Console.WriteLine("Category: {0}", ad.Category != null ? ad.Category.Name : "not specified"); // Console.WriteLine("Town: {0}", ad.Town != null ? ad.Town.Name : "not specified"); // Console.WriteLine("User: {0}", ad.AspNetUser.UserName); // Console.WriteLine(); //} //var adsWithInclude = context.Ads.Include(a => a.AdStatus) // .Include(a => a.Category) // .Include(a => a.Town) // .Include(a => a.AspNetUser); //foreach (var ad in adsWithInclude) //{ // Console.WriteLine("Title: {0}", ad.Title); // Console.WriteLine("Status: {0}", ad.AdStatus.Status); // Console.WriteLine("Category: {0}", ad.Category != null ? ad.Category.Name : "not specified"); // Console.WriteLine("Town: {0}", ad.Town != null ? ad.Town.Name : "not specified"); // Console.WriteLine("User: {0}", ad.AspNetUser.UserName); // Console.WriteLine(); //} //+--------------------------+---------------+-----------------+ //| | No Include(…) | With Include(…) | //+--------------------------+---------------+-----------------+ //| Number of SQL statements | 28| 1| //+--------------------------+---------------+-----------------+ // --------------Problem 2 --------------- var sw = new Stopwatch(); //context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE"); //sw.Start(); //var adsSlow = // context.Ads.ToList() // .Where(a => a.AdStatus.Status == "Published") // .Select(a => new { Title = a.Title, Category = a.Category, Town = a.Town, Date = a.Date }) // .ToList() // .OrderBy(a => a.Date); //Console.WriteLine(sw.ElapsedMilliseconds); //context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE"); //sw.Restart(); //var adsFast = // context.Ads // .Where(a => a.AdStatus.Status == "Published") // .Select(a => new { Title = a.Title, Category = a.Category, Town = a.Town, Date = a.Date }) // .OrderBy(a => a.Date).ToList(); //Console.WriteLine(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) | 191 | 195 | 248 | 163 | 197 | 230 | 164 | 177 | 175 | 183 | 192.3 | // | Optimized (ms) | 77 | 71 | 118 | 78 | 81 | 96 | 78 | 83 | 78 | 83 | 84.3 | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ // --------------Problem 3 --------------- sw.Restart(); context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE"); var notOptimized = context.Ads; foreach (var ad in notOptimized) { Console.WriteLine(ad.Title); } Console.WriteLine(); Console.WriteLine(sw.ElapsedMilliseconds); sw.Restart(); context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE"); var optimized = context.Ads.Select(a => a.Title); foreach (var ad in optimized) { Console.WriteLine(ad); } Console.WriteLine(); Console.WriteLine(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) | 386 | 379 | 502 | 491 | 516 | 348 | 418 | 330 | 490 | 423 | 428.3 | // | Optimized (ms) | 91 | 121 | 105 | 103 | 78 | 85 | 67 | 78 | 94 | 73 | 89.5 | // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+ }