public bool ComplicatedLinqFast(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
{
var q =
(
from n1 in db.Narrows
join n2 in db.Narrows on new { n1.ID, n1.Field1 } equals new { n2.ID, n2.Field1 }
where n1.ID < 100 && n2.Field1 <= 50
group n1 by n1.ID into gr
select new
{
gr.Key,
Count = gr.Count()
}
)
.OrderBy(n1 => n1.Key)
.Skip(1)
.Take(takeCount);
foreach (var item in q)
{
}
}
}
watch.Stop();
return(true);
}
public bool SimpleLinqQuery(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
{
var q =
(
from n1 in db.Narrows
where n1.ID < 100
select n1.ID
)
.Take(takeCount);
foreach (var item in q)
{
}
}
}
watch.Stop();
return(true);
}
static void CreateTestDatabase(bool enforceCreate, string serverName)
{
Console.WriteLine("Creating database...");
using (var db = SqlServerTools.CreateDataConnection($"Server={serverName};Database=master;Trusted_Connection=True"))
{
if (!enforceCreate)
{
if (db.Execute <object>("SELECT db_id('PerformanceTest')") != null)
{
if (db.Execute <object>("SELECT OBJECT_ID('PerformanceTest.dbo.Setting', N'U')") != null)
{
if (db.GetTable <Setting>()
.DatabaseName("PerformanceTest")
.Any(s => s.Name == "DB Version" && s.Value == DatabaseVersion))
{
return;
}
}
}
}
db.Execute("DROP DATABASE IF EXISTS PerformanceTest");
db.Execute("CREATE DATABASE PerformanceTest");
}
using (var db = new L2DB.L2DBContext())
{
CreateTable(db, new[] { new Setting {
Name = "DB Version", Value = DatabaseVersion
} });
CreateTable(db, new[] { new Narrow {
ID = 1, Field1 = 2
} });
CreateTable(db, Enumerable.Range(1, 1000000).Select(i => new NarrowLong {
ID = i, Field1 = -i
}));
CreateTable(db, Enumerable.Range(1, 1000000).Select(i => new WideLong
{
ID = i,
Field1 = -i,
ByteValue = i % 2 == 0 ? null : (byte?)(i % Byte.MaxValue / 2),
ShortValue = i % 2 == 0 ? null : (short?)(i % Int16.MaxValue / 2),
IntValue = i % 2 == 1 ? null : (int?)(i % Int32.MaxValue - 1),
LongValue = i % 2 == 0 ? null : (long?)(i * 2),
StringValue = i % 2 == 0 ? null : new string(Enumerable.Range(0, 95).Select(n => (char)(n % 30 + (int)' ')).ToArray()),
DateTimeValue = i % 2 == 1 ? null : (DateTime?)new DateTime(i),
TimeValue = i % 2 == 1 ? null : (TimeSpan?)new TimeSpan(i),
DecimalValue = i % 2 == 0 ? null : (decimal?)i,
DoubleValue = i % 2 == 1 ? null : (double?)i,
FloatValue = i % 2 == 0 ? null : (float?)i,
}));
}
Console.WriteLine("Database created.");
}
public bool ComplicatedLinqSlow(Stopwatch watch, int repeatCount, int takeCount, int nRows)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
{
var q =
(
from n in db.NarrowLongs
join w in db.WideLongs on n.Field1 equals w.Field1
where
n.ID >= 0 && n.ID <= nRows &&
!new[] { 0, 20, 50, 187635 }.Contains(w.Field1)
select new
{
n.ID,
w.Field1
}
)
//.Concat
.Union
(
from n in db.NarrowLongs
join w in db.WideLongs on n.Field1 equals w.Field1
where
n.ID >= 0 && n.ID <= nRows &&
!new[] { 0, 240, 500, 18635 }.Contains(w.Field1)
select new
{
n.ID,
w.Field1
}
)
//.Distinct()
//.OrderBy(n1 => Sql.Ext.RowNumber().Over().OrderByDesc(n1.Field1))
.OrderByDescending(n1 => n1.Field1)
.Skip(1000)
.Take(takeCount);
foreach (var item in q)
{
}
}
}
watch.Stop();
return(true);
}
public async Task <bool> GetWideListAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
await db.WideLongs.Take(takeCount).ForEachAsync(item => {});
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnSlowAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
await db.ExecuteAsync <int>(GetSingleColumnSql);
}
watch.Stop();
return(true);
}
public bool GetSingleColumnSlow(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
db.Execute <int>(GetSingleColumnSql);
}
watch.Stop();
return(true);
}
public bool GetSingleColumnSlow(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
db.Narrows.Where(t => t.ID == 1).Select(t => t.ID).AsEnumerable().First();
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnSlowAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
await db.Narrows.Where(t => t.ID == 1).Select(t => t.ID).FirstAsync();
}
watch.Stop();
return(true);
}
public bool GetWideList(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
foreach (var item in db.WideLongs.Take(takeCount))
{
}
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnParamAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
using (var db = new L2DBContext(TrackChanges))
for (var i = 0; i < repeatCount; i++)
{
var id = 1;
var p = 2;
await db.Narrows.Where(t => t.ID == id && t.Field1 == p).Select(t => t.ID).FirstAsync();
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnParamAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
using (var db = new L2DBContext(TrackChanges))
for (var i = 0; i < repeatCount; i++)
{
await db.ExecuteAsync <int>(GetParamSql,
new DataParameter("@id", 1),
new DataParameter("@p", 2));
}
watch.Stop();
return(true);
}
public bool GetSingleColumnParam(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
using (var db = new L2DBContext(TrackChanges))
for (var i = 0; i < repeatCount; i++)
{
var id = 1;
var p = 2;
db.Narrows.Where(t => t.ID == id && t.Field1 == p).Select(t => t.ID).AsEnumerable().First();
}
watch.Stop();
return(true);
}
public bool GetSingleColumnSlow(Stopwatch watch, int repeatCount, int takeCount)
{
var query = CompiledQuery.Compile((L2DBContext db) =>
db.Narrows.Where(t => t.ID == 1).Select(t => t.ID).First());
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
query(db);
}
watch.Stop();
return(true);
}
public async Task <bool> GetSingleColumnFastAsync(Stopwatch watch, int repeatCount, int takeCount)
{
var query = CompiledQuery.Compile((L2DBContext db) =>
db.Narrows.Where(t => t.ID == 1).Select(t => t.ID).FirstAsync(default(CancellationToken)));
watch.Start();
using (var db = new L2DBContext(TrackChanges))
for (var i = 0; i < repeatCount; i++)
{
await query(db);
}
watch.Stop();
return(true);
}
public bool GetNarrowList(Stopwatch watch, int repeatCount, int takeCount)
{
var sql = GetNarrowListSql(takeCount);
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
foreach (var item in db.Query <NarrowLong>(sql))
{
}
}
watch.Stop();
return(true);
}
public async Task <bool> GetWideListAsync(Stopwatch watch, int repeatCount, int takeCount)
{
var sql = GetWideListSql(takeCount);
watch.Start();
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
foreach (var item in await db.QueryToListAsync <WideLong>(sql))
{
}
}
watch.Stop();
return(true);
}
public async Task <bool> GetNarrowListAsync(Stopwatch watch, int repeatCount, int takeCount)
{
watch.Start();
var token = default(CancellationToken);
for (var i = 0; i < repeatCount; i++)
{
using (var db = new L2DBContext(TrackChanges))
//await db.NarrowLongs.Take(takeCount).ForEachAsync(item => {});
using (var en = db.NarrowLongs.Take(takeCount).AsAsyncEnumerable().GetEnumerator())
while (await en.MoveNext(token))
{
var item = en.Current;
}
}
watch.Stop();
return(true);
}