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");
}
