private static void GetAdsWithInclude(AdsContext context)
{
var ads = context.Ads
.Include(a => a.AdStatus)
.Include(a => a.Category)
.Include(a => a.Town)
.Include(a => a.AspNetUser);
PrintAds(ads);
}
private static void GetOptimizedQueryExecutionTime(AdsContext context)
{
var ads = context.Ads
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
a.Category,
a.Town
}).ToList();
}
public static void Main()
{
var context = new AdsContext();
GetAdsNoInclude(context);
GetAdsWithInclude(context);
// +--------------------------+------------+--------------+
// | | No Include | With Include |
// +--------------------------+------------+--------------+
// | Number of SQL statements | 28 | 1 |
// +--------------------------+------------+--------------+
}
public static void Main()
{
var context = new AdsContext();
var sw = new Stopwatch();
context.Ads.Count();
CleanCache(context);
sw.Start();
GetNonOptimizedQueryExecutionTime(context);
Console.WriteLine("Non-optimized: {0}", sw.ElapsedMilliseconds);
CleanCache(context);
sw = Stopwatch.StartNew();
GetOptimizedQueryExecutionTime(context);
Console.WriteLine("Optimized: {0}", 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) | 230 | 239 | 235 | 243 | 233 | 236 | 232 | 233 | 246 | 242 | 237 |
// | Optimized (ms) | 20 | 19 | 21 | 20 | 20 | 19 | 19 | 21 | 19 | 19 | 20 |
// +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
}
public static void Main()
{
var context = new AdsContext();
var sw = new Stopwatch();
context.Ads.Count();
CleanCache(context);
sw.Start();
GetNonOptimizedQueryExecutionTime(context);
Console.WriteLine("Non-optimized: {0}", sw.ElapsedMilliseconds);
CleanCache(context);
sw = Stopwatch.StartNew();
GetOptimizedQueryExecutionTime(context);
Console.WriteLine("Optimized: {0}", 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) | 461 | 465 | 461 | 480 | 465 | 531 | 469 | 470 | 485 | 463 | 475 |
// | Optimized (ms) | 229 | 230 | 247 | 219 | 226 | 274 | 248 | 236 | 242 | 224 | 238 |
// +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
}
private static void CleanCache(AdsContext context)
{
context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE");
}
private static void GetAdsNoInclude(AdsContext context)
{
var ads = context.Ads;
PrintAds(ads);
}
private static void GetOptimizedQueryExecutionTime(AdsContext context)
{
var ads = context.Ads
.Select(a => a.Title)
.ToList();
}
private static void GetNonOptimizedQueryExecutionTime(AdsContext context)
{
var ads = context.Ads.ToList();
}