private static void RunQuery(string runParameter = null)
{
using (var db = new AdsEntities())
{
if (runParameter != null && runParameter.ToLower() == "include")
{
// WITH INCLUDE
foreach (var ad in db.Ads
.Include(a => a.AdStatus).Include(a => a.Category).Include(a => a.AspNetUser).Include(a => a.Town))
{
Console.WriteLine("Title: {0}\r\nStatus: {1}\r\nCategory: {2}\r\nTown: {3}\r\nUser: {4}\r\n",
ad.Title,
ad.AdStatus.Status,
ad.CategoryId == null ? null : ad.Category.Name,
ad.TownId == null ? null : ad.Town.Name,
ad.AspNetUser.Name);
}
}
else if (runParameter == null || runParameter.ToLower() == "notInclude")
{
// WITHOUT INCLUDE
foreach (var ad in db.Ads)
{
Console.WriteLine("Title: {0}\r\nStatus: {1}\r\nCategory: {2}\r\nTown: {3}\r\nUser: {4}\r\n",
ad.Title, ad.AdStatus.Status, ad.Category, ad.Town, ad.AspNetUser.Name);
}
}
else
{
// INVALID RUN PARAMETER
throw new ArgumentException("Unknown run parameter.");
}
}
}
/// <summary>
/// Return query performance result
/// </summary>
/// <param name="queryType">("selectCertain") OR ("selectAll")</param>
/// <param name="queryCount">int (1..n)</param>
private static void RunQuery(string queryType, int queryCount)
{
using (var db = new AdsEntities())
{
if (queryType != null && queryCount > 0 &&
(queryType.ToLower() == "certain" || queryType.ToLower() == "all"))
{
// CLEAN BUFFERS
db.Database.ExecuteSqlCommand("CHECKPOINT;");
db.Database.ExecuteSqlCommand(" DBCC DROPCLEANBUFFERS;");
// STOPWATCH
var sw = new Stopwatch();
sw.Start();
var totalTime = new TimeSpan();
Console.WriteLine("\r\n{0} mode.\r\n",
queryType.ToLower() == "certain" ? "Select certain" : "Select all");
for (int i = 0; i < queryCount; i++)
{
// CERTAIN COLUMN
if (queryType.ToLower() == "certain")
{
foreach (var adTitle in db.Ads.Select(a => a.Title))
{
var currentAdTitle = adTitle;
}
}
else
// ALL COLUMNS
{
foreach (var ad in db.Ads)
{
var currentAdTitle = ad.Title;
}
}
// ADD CURRENT ELAPSED TIME TO TOTAL TIME
totalTime = totalTime.Add(sw.Elapsed);
// SHOW CURRENT ELAPSED TIME
Console.WriteLine(sw.Elapsed);
// STOPWATCH RESET
sw.Restart();
}
// AVERAGE ELAPSED TIME
Console.WriteLine("\r\nAverage time: {0}", totalTime.TotalSeconds / queryCount);
Console.WriteLine("-------------------------------");
}
}
}
static void Main()
{
using (var db = new AdsEntities())
{
// QUERY WITHOUT INCLUDE
RunQuery();
// UNCOMENT TO USE (QUERY WITH INCLUDE)
RunQuery("include");
}
}
/// <summary>
/// Run SQL Query and print runtime stats
/// </summary>
/// <param name="queryMode">("Optimized") or ("NotOptimized")</param>
/// <param name="queryCount">int (how much times to run the query)</param>
private static void RunQuery(string queryMode, int queryCount)
{
using (var db = new AdsEntities())
{
// CLEAN BUFFERS
db.Database.ExecuteSqlCommand("CHECKPOINT;");
db.Database.ExecuteSqlCommand(" DBCC DROPCLEANBUFFERS;");
// INIT STOPWATCH
var sw = new Stopwatch();
sw.Start();
// INIT TOTAL TIME
var totalTime = new TimeSpan();
// RUN QUERY NOT OPTIMIZED MODE
if (queryMode == null || queryMode.ToLower() == "notoptimized")
{
Console.WriteLine("Not optimized mode:\r\n");
for (int i = 0; i < queryCount; i++)
{
db.Ads.ToList().Where(a => a.AdStatus.Status == "Published").Select(a => new
{
a.Title,
Category = a.CategoryId == null ? null : a.Category.Name,
Town = a.TownId == null ? null : a.Town.Name,
a.Date
}).ToList().OrderBy(a => a.Date);
// PRINT CURRENT STOPWATCH ELAPSED TIME
Console.WriteLine("Elapsed: {0}", sw.Elapsed);
// ADD CURRENT STOPWATCH ELAPSED TIME TO TOTAL TIME
totalTime = totalTime.Add(sw.Elapsed);
// RESET STOP WATCH
sw.Restart();
}
}
// RUN QUERY OPTIMIZED MODE
else if (queryMode.ToLower() == "optimized")
{
Console.WriteLine("Optimized mode:\r\n");
for (int i = 0; i < queryCount; i++)
{
db.Ads.Where(a => a.AdStatus.Status == "Published").Select(a => new
{
a.Title,
Category = a.Category.Name,
Town = a.Town.Name,
a.Date,
}).OrderBy(a => a.Date);
// PRINT CURRENT STOPWATCH ELAPSED TIME
Console.WriteLine("Elapsed: {0}", sw.Elapsed);
// ADD CURRENT STOPWATCH ELAPSED TIME TO TOTAL TIME
totalTime = totalTime.Add(sw.Elapsed);
// RESET STOP WATCH
sw.Restart();
}
}
// ELSE THROW EXCEPTION
else
{
throw new ArgumentException(String.Format("Unknown parameter {0}.", queryMode));
}
// AVERAGE ELAPSED TIME
Console.WriteLine("\r\nAverage time: {0}\r\n", totalTime.TotalSeconds / queryCount);
Console.WriteLine("-------------------------------");
sw.Stop();
}
}