private static TimeSpan[] RunSecondQueryMeasurements(AdsEntities ctx, Stopwatch stopwatch)
{
ctx.Database.SqlQuery<Ad>("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
var secondQueryRunDurations = new TimeSpan[10];
for (int index = 0; index < 10; index++)
{
stopwatch.Reset();
stopwatch.Start();
var adsQueryGood = ctx.Ads
.Where(a => a.AdStatus.Status.Equals("Published"))
.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
a.Category,
a.Town,
})
.ToList();
secondQueryRunDurations[index] = stopwatch.Elapsed;
}
stopwatch.Reset();
return secondQueryRunDurations;
}
public static void Main()
{
var ctx = new AdsEntities();
var stopwatch = new Stopwatch();
ExecuteMeasurements(ctx, stopwatch);
}
public static void Main()
{
var ctx = new AdsEntities();
var stopwatch = new Stopwatch();
SelectEverythingVSSelectCertainColumns(ctx, stopwatch);
}
public static void Main()
{
var ctx = new AdsEntities();
// No Include
GetAllAdsWithoutInclude(ctx);
// With Include
GetAllAdsWithInclude(ctx);
}
private static void GetAllAdsWithInclude(AdsEntities ctx)
{
var ads = ctx.Ads
.Select(a => new
{
a.Title,
TownName = a.Town.Name,
Category = a.Category.Name,
a.AdStatus.Status,
a.AspNetUser.UserName
}).ToList();
}
private static void GetAllAdsWithoutInclude(AdsEntities ctx)
{
var ads = ctx.Ads.ToList();
foreach (var ad in ads)
{
Console.WriteLine("Title: {0}\nStatus: {1}\nCategory: {2}\nTown: {3}\nUser: {4}\n",
ad.Title,
ad.AdStatus.Status,
ad.Category != null ? ad.Category.Name : "No category",
ad.Town != null ? ad.Town.Name : "No town",
ad.AspNetUser.UserName);
}
}
private static TimeSpan[] RunSelectCertainColumnsMeasurements(AdsEntities ctx, Stopwatch stopwatch)
{
ctx.Database.SqlQuery<Ad>("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
var certainQueryRunDurations = new TimeSpan[10];
for (int index = 0; index < 10; index++)
{
stopwatch.Reset();
stopwatch.Start();
var certainColumns = ctx.Ads.Select(a => a.Title).ToList();
certainQueryRunDurations[index] = stopwatch.Elapsed;
}
stopwatch.Reset();
return certainQueryRunDurations;
}
private static TimeSpan[] RunSelectEverythingMeasurements(AdsEntities ctx, Stopwatch stopwatch)
{
ctx.Database.SqlQuery<Ad>("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
var everythingQueryRunDurations = new TimeSpan[10];
for (int index = 0; index < 10; index++)
{
stopwatch.Reset();
stopwatch.Start();
var everything = ctx.Ads.ToList();
everythingQueryRunDurations[index] = stopwatch.Elapsed;
}
stopwatch.Reset();
return everythingQueryRunDurations;
}
private static void ExecuteMeasurements(AdsEntities ctx, Stopwatch stopwatch)
{
var firstQueryRunDurations = RunFirstQueryMeasurements(ctx, stopwatch);
var secondQueryRunDurations = RunSecondQueryMeasurements(ctx, stopwatch);
var firstQueryAverage = firstQueryRunDurations.Sum(ts => ts.Ticks);
var secondQueryAverage = secondQueryRunDurations.Sum(ts => ts.Ticks);
Console.WriteLine("Bad query:");
for (int i = 0; i < firstQueryRunDurations.Count(); i++)
{
Console.WriteLine("Run" + i + " ticks: " + firstQueryRunDurations[i].Ticks);
}
Console.WriteLine("\nGood query:");
for (int i = 0; i < secondQueryRunDurations.Count(); i++)
{
Console.WriteLine("Run" + i + " ticks: " + secondQueryRunDurations[i].Ticks);
}
Console.WriteLine("Average time of BAD query in ticks: " + firstQueryAverage);
Console.WriteLine("Average time of GOOD query in ticks: " + secondQueryAverage);
}
private static void SelectEverythingVSSelectCertainColumns(AdsEntities ctx, Stopwatch stopwatch)
{
var selectEverythingDurations = RunSelectEverythingMeasurements(ctx, stopwatch);
var selectCertainDurations = RunSelectCertainColumnsMeasurements(ctx, stopwatch);
var selectEverythingAverage = selectEverythingDurations.Sum(ts => ts.Ticks);
var selectCertainAverage = selectCertainDurations.Sum(ts => ts.Ticks);
Console.WriteLine("Select Everything query:");
for (int i = 0; i < selectEverythingDurations.Count(); i++)
{
Console.WriteLine("Run" + i + " ticks: " + selectEverythingDurations[i].Ticks);
}
Console.WriteLine("\nSelect Certain query:");
for (int i = 0; i < selectCertainDurations.Count(); i++)
{
Console.WriteLine("Run" + i + " ticks: " + selectCertainDurations[i].Ticks);
}
Console.WriteLine("Average time of EVERYTHING query in ticks: " + selectEverythingAverage);
Console.WriteLine("Average time of CERTAIN query in ticks: " + selectCertainAverage);
}
static void Main()
{
var context = new AdsEntities();
context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS");
//Problem 1
//foreach (var ad in context.Ads
//.Include(ad => ad.AdStatus)
//.Include(ad => ad.Category)
//.Include(ad => ad.Town)
//.Include(ad => ad.AspNetUser))
//{
// System.Console.WriteLine("{0} {1} {2} {3} {4}",
// ad.Title,
// ad.AdStatus.Status,
// (ad.Category != null) ? ad.Category.Name : "",
// (ad.Town != null) ? ad.Town.Name : "",
// ad.AspNetUser.Name);
//}
//Problem 2
var stop = new Stopwatch();
stop.Start();
var allAdsRaw = context.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(ad => new
{
ad.Title,
ad.Category,
ad.Town
})
.ToList();
stop.Stop();
System.Console.WriteLine("Raw: {0}",stop.Elapsed);
stop.Reset();
stop.Start();
var allAdsOptimised = context.Ads
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(ad => new
{
ad.Title,
ad.Category,
ad.Town
})
.ToList();
stop.Stop();
stop.Reset();
System.Console.WriteLine("Optimised: {0}",stop.Elapsed);
//Problem 3
}