public void ParallelSelectWithSubquery()
{
Guid g = StopWatch.Start();
int loops = 80;
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null)
.AllColumns(false)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
double one = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds);
int count = result.Count + result.SelectMany(x => x.Column <ResultTable>("ContactList")).Count();
g = StopWatch.Start();
int total = 0;
ParallelLoopResult r = Parallel.For(0, loops, new ParallelOptions()
{
MaxDegreeOfParallelism = 8
}, a => { total += SelectWithSubqueryInternal(); });
double twenty = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds);
Console.WriteLine("{2} selects parallel executed with a total of {3} rows in {0:0.00}ms. Estimated {1:0.00}ms", twenty, one * loops, loops, loops * count);
Assert.IsTrue(r.IsCompleted);
}
public void ExecuteDeep()
{
ResultTable result = ExecuteDeepInternal();
Console.WriteLine("The result has {0} Account rows", result.Count);
Console.WriteLine("These accounts has a total of {0} Contact rows", result.SelectMany(x => x.Column <ResultTable>("Contacts")).Count());
Console.WriteLine("These contacts has a total of {0} Activity rows", result.SelectMany(x => x.Column <ResultTable>("Contacts")).SelectMany(y => y.Column <ResultTable>("Activities")).Count());
Console.WriteLine("These contacts has a total of {0} Campaign Activity rows", result.SelectMany(x => x.Column <ResultTable>("Contacts")).SelectMany(y => y.Column <ResultTable>("CampaignActivityList")).Count());
foreach (dynamic row in result.Take(50))
{
ResultTable contacts = row.Contacts;
Console.WriteLine("The account {0} has {1} Contacts", row.Name, contacts.Count);
Console.WriteLine("The account {0} has a total of {1} Activities", row.Name, contacts.SelectMany(x => x.Column <ResultTable>("Activities")).Count());
}
Console.Write("Executing a second time with results in cahce:");
result = ExecuteDeepInternal();
}
private int SelectWithSubqueryInternal()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null)
.Columns("ContactID", "Name", "Title")
.Builder();
ResultTable result = builder.Execute();
Console.WriteLine("{0} rows executed from Thread {1}", result.Count + result.SelectMany(x => x.Column <ResultTable>("ContactList")).Count(), System.Threading.Thread.CurrentThread.ManagedThreadId);
return(result.Count);
}