private static void RunQueryBuilderTest(CrossLite.SQLiteContext context)
{
// Quote keywords only with accents
context.IdentifierQuoteKind = IdentifierQuoteKind.Accents;
context.IdentifierQuoteMode = IdentifierQuoteMode.KeywordsOnly;
// Start logging times
Stopwatch timer = Stopwatch.StartNew();
// Simple query test, using a reserved word for quote testing
var query = new SelectQueryBuilder(context);
query.From("table1") // Indicate table (can be done after select)
.Select("col1", "col2", "plan") // Main table select
.As("Id", "Name") // Alias 2 of the 3 columns (zero-based index)
.InnerJoin("table2").As("t2").On("col22").Equals("table1", "col1")
.Select("col21", "col22") // Inner join a table, selecting 2 columns
.Alias(1, "Type") // Alias at zero-based index
.CrossJoin("table3").As("t3").Using("Id") // Cross Join another table!
.SelectCount() // I could specify the return alias here, but for testing reasons...
.As("count") // ...We are going to do this inefficiently for a testing reason!
// Finally, a where clause
.Where("Type").Equals(AccountType.Admin).And("Id").GreaterThan(6).Or("plan").NotEqualTo(3);
var queryString = query.BuildQuery();
// Log query builder time (14ms for me on an i7-950)
long time = timer.ElapsedMilliseconds;
Console.WriteLine("Complicated QueryBuilder Test:");
Console.WriteLine();
Console.WriteLine(queryString);
Console.WriteLine($"Query generated in {time}ms");
}
private static void RunQueryBuilderTests(CrossLite.SQLiteContext context)
{
// No quotes
context.IdentifierQuoteMode = IdentifierQuoteMode.None;
// Run the queries, both plain and query builder
double timesToRun = 10000;
StringBuilder builder = new StringBuilder();
// Write to console
Console.WriteLine("Testing querybuilder against plain SQL ({0:N0} queries)", timesToRun);
// Start logging times
Stopwatch timer = Stopwatch.StartNew();
for (double i = timesToRun; i > 0; i--)
{
// Clear buffer
builder.Clear();
builder.Append("SELECT * FROM test WHERE Id BETWEEN 1 AND 4");
var result = context.Query <Account>(builder.ToString());
}
// Log time
long time1 = timer.ElapsedMilliseconds;
timer.Restart();
for (double i = timesToRun; i > 0; i--)
{
var query = new SelectQueryBuilder(context);
//query.From("test").Select("Id", "Name", "AccountType", "Col1", "Col2", "Col3").Where("Id").Between(1, 4);
query.From("test").SelectAll().Where("Id").Between(1, 4);
var result2 = query.ExecuteQuery <Account>();
}
// Log Time
long time2 = timer.ElapsedMilliseconds;
Console.WriteLine("Plain Query: {0}ms ({1}ms per query)", time1, Math.Round(time1 / timesToRun, 6));
Console.WriteLine("QueryBuilder: {0}ms ({1}ms per query)", time2, Math.Round(time2 / timesToRun, 6));
}
