public static void Main() { var context = new AdsEntities(); //// Initialize connection Console.WriteLine("All ads = " + context.Ads.Count()); string clearCache = "CHECKPOINT; DBCC DROPCLEANBUFFERS;"; TimeSpan[] nonOptimizedArray = new TimeSpan[6]; TimeSpan[] optimizedArray = new TimeSpan[6]; for (int i = 0; i < 6; i++) { Stopwatch sw = new Stopwatch(); context.Database.ExecuteSqlCommand(clearCache); sw.Start(); foreach (var ad in context.Ads) { Console.WriteLine(ad.Title); } nonOptimizedArray[i] = sw.Elapsed; context.Database.ExecuteSqlCommand(clearCache); sw.Restart(); foreach (var adTitle in context.Ads.Select(a => a.Title)) { Console.WriteLine(adTitle); } optimizedArray[i] = sw.Elapsed; sw.Stop(); } string table = string.Format( @" Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Average time (ms) --------------- ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------- Non-optimized {0} {1} {2} {3} {4} {5} {6:F3} Optimized {7} {8} {9} {10} {11} {12} {13:F3}", nonOptimizedArray[0], nonOptimizedArray[1], nonOptimizedArray[2], nonOptimizedArray[3], nonOptimizedArray[4], nonOptimizedArray[5], nonOptimizedArray.Average(n => n.Milliseconds), optimizedArray[0], optimizedArray[1], optimizedArray[2], optimizedArray[3], optimizedArray[4], optimizedArray[5], optimizedArray.Average(o => o.Milliseconds)); File.WriteAllText(@"..\..\table.txt", table); Console.WriteLine("\nFind the output at table.txt"); }
public static void Main() { var context = new AdsEntities(); Console.Write("Do you want to use Include() y/n: "); string input = Console.ReadLine(); switch (input) { case "n": var adsOne = context.Ads; foreach (var ad in adsOne) { Console.WriteLine( "Title: {0}\nStatus: {1}\nCategory: {2}\nTown: {3}\nUser: {4}", ad.Title, ad.AdStatus.Status, ad.Category == null ? "None" : ad.Category.Name, ad.Town == null ? "None" : ad.Town.Name, ad.AspNetUser.Name); } break; case "y": var adsTwo = context.Ads .Include(a => a.AdStatus) .Include(a => a.Category) .Include(a => a.Town) .Include(a => a.AspNetUser); foreach (var ad in adsTwo) { Console.WriteLine( "Title: {0}\nStatus: {1}\nCategory: {2}\nTown: {3}\nUser: {4}", ad.Title, ad.AdStatus.Status, ad.Category == null ? "None" : ad.Category.Name, ad.Town == null ? "None" : ad.Town.Name, ad.AspNetUser.Name); } break; default: Console.WriteLine("Wrong input."); break; } string table = @" No Include(…) With Include(…) -------------------------- --------------- ----------------- Number of SQL statements 28 1 "; File.WriteAllText(@"..\..\table.txt", table); }
static void Main(string[] args) { var context = new AdsEntities(); // Problem 1 ------------------------- //foreach (var a in context.Ads) //{ // Console.WriteLine("- {0} - {1} - {2} - {3} - {4}", // a.Title, // a.AdStatus.Status, // a?.Category?.Name ?? "No category", // a?.Town?.Name ?? "No town", // a?.AspNetUser?.Name ?? "No name"); //} //foreach (var ad in context.Ads // .Include(x => x.AdStatus) // .Include(x => x.Category) // .Include(x => x.Town) // .Include(x => x.AspNetUser)) //{ // Console.WriteLine("- {0} - {1} - {2} - {3} - {4}", // ad.Title, // ad.AdStatus.Status, // ad?.Category?.Name ?? "No category", // ad?.Town?.Name ?? "No town", // ad?.AspNetUser?.Name ?? "No name"); //} //Problem 2 ------------------------------ //var stopwatch = System.Diagnostics.Stopwatch.StartNew(); //context.Ads.SqlQuery("CHECKPOINT; DBCC DROPCLEANBUFFERS;"); //slow version //var ads = context.Ads // .Where(x => x.AdStatus.Status == "Published") // .OrderBy(x => x.Date) // .Select(x => new // { // x.Title, // Town = x.Town.Name, // Category = x.Category.Name // }) // .ToList(); //stopwatch.Stop(); // optimized version ////var ads = context.Ads //// .Include(x => x.Town) //// .Include(x => x.Category) //// .Where(x => x.AdStatus.Status == "Published") //// .OrderBy(x => x.Date) //// .Select(x => new //// { //// x.Title, //// Town = x.Town.Name, //// Category = x.Category.Name //// }) //// .ToList(); //stopwatch.Stop(); //Console.WriteLine(stopwatch.ElapsedMilliseconds); //Problem 3 ------------------------------------------------------ //var stopwatch = System.Diagnostics.Stopwatch.StartNew(); //context.Ads.SqlQuery("CHECKPOINT; DBCC DROPCLEANBUFFERS;"); //var ads = context.Ads // .Select(x => x); //stopwatch.Stop(); ////var ads = context.Ads //// .Select(x => x.Title); ////stopwatch.Stop(); //Console.WriteLine(stopwatch.ElapsedMilliseconds); }
public static void Main() { var context = new AdsEntities(); //// Initialize connection var count = context.Ads.Count(); TimeSpan[] nonOptimizedArray = new TimeSpan[6]; TimeSpan[] optimizedArray = new TimeSpan[6]; Stopwatch sw = new Stopwatch(); sw.Start(); for (int i = 0; i < 6; i++) { sw.Restart(); var adsBadWay = context.Ads .ToList() .Where(a => a.AdStatus.Status == "Published") .Select( a => new { a.Title, a.Category, a.Town, a.Date }) .ToList() .OrderBy(a => a.Date).Count(); nonOptimizedArray[i] = sw.Elapsed; sw.Restart(); var adsOptimized = context.Ads .Where(a => a.AdStatus.Status == "Published") .OrderBy(a => a.Date) .Select(a => new { a.Title, a.Category, a.Town, a.Date }).Count(); optimizedArray[i] = sw.Elapsed; if (i == 5) { Console.WriteLine("Number of elements for non optimized query: " + adsBadWay); Console.WriteLine("Number of elements for optimized query: " + adsOptimized); } } sw.Stop(); string table = string.Format( @" Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Average time (ms) --------------- ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------- Non-optimized {0} {1} {2} {3} {4} {5} {6:F10} Optimized {7} {8} {9} {10} {11} {12} {13:F10} ", nonOptimizedArray[0], nonOptimizedArray[1], nonOptimizedArray[2], nonOptimizedArray[3], nonOptimizedArray[4], nonOptimizedArray[5], nonOptimizedArray.Average(n => n.Milliseconds), optimizedArray[0], optimizedArray[1], optimizedArray[2], optimizedArray[3], optimizedArray[4], optimizedArray[5], optimizedArray.Average(o => o.Milliseconds)); File.WriteAllText(@"..\..\table.txt", table); Console.WriteLine("Find the output at table.txt"); }