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)); }