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 |
// +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
}