Performance test to showcase FastColumnSetter from https://github.com/praeclarum/sqlite-net/pull/902

SqliteNetPerformanceTest.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using SQLite;

namespace sqlite_performance
{
    class OrderDetail
    {
        public int Id { get; set; }
        public int OrderId { get; set; }
        public int ProductId { get; set; }
        public int Quantity { get; set; }
        public string Name { get; set; }
    }

    class Program
    {
        static string dbFilename = "testing.sqlite";

        static void Main(string[] args)
        {
            TimeSpan test1min = TimeSpan.MaxValue;
            TimeSpan test2min = TimeSpan.MaxValue;
            TimeSpan test1max = TimeSpan.MinValue;
            TimeSpan test2max = TimeSpan.MinValue;

            var conn = new SQLiteConnection(dbFilename);

            // Populate our database if it doesn't already.
            if (!TableExists<OrderDetail>(conn))
            {
                CreateDB(conn, 1000000);
            }

            for (int cnt = 0; cnt < 5; cnt++)
            {
                var elapsed1 = DoTest1(conn);
                var elapsed2 = DoTest2(conn);

                if (elapsed1 < test1min)
                    test1min = elapsed1;
                if (elapsed1 > test1max)
                    test1max = elapsed1;
                if (elapsed2 < test2min)
                    test2min = elapsed2;
                if (elapsed2 > test2max)
                    test2max = elapsed2;

                Console.WriteLine($"Test1: {elapsed1}");
                Console.WriteLine($"Test2: {elapsed2}");
            }

            conn.Close();

            Console.WriteLine($"Test 1 range: {test1min} to {test1max}");
            Console.WriteLine($"Test 2 range: {test2min} to {test2max}");
        }

        // h.t. https://forums.xamarin.com/discussion/1460/sqlite-how-to-check-if-table-exists#Comment_78996
        public static bool TableExists<T>(SQLiteConnection connection)
        {
            const string cmdText = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
            var cmd = connection.CreateCommand(cmdText, typeof(T).Name);
            return cmd.ExecuteScalar<string>() != null;
        }

        static void CreateDB(SQLiteConnection conn, int numEntries)
        {
            conn.BeginTransaction();
            conn.CreateTable<OrderDetail>();
            var orders = new List<OrderDetail>();
            var maxId = 0;
            var rand = new Random();

            for (int cnt = 0; cnt < numEntries; cnt++)
            {
                orders.Add(new OrderDetail()
                {
                    Id = maxId++,
                    OrderId = rand.Next(),
                    ProductId = rand.Next(),
                    Quantity = rand.Next(1, 100),
                    Name = "abcdefghijklmnopqrstuvwxyz".Substring(rand.Next(0, 20), 5)
                });
            }
            conn.InsertAll(orders);
            conn.Commit();
        }

        static TimeSpan DoTest1(SQLiteConnection conn)
        {
            Stopwatch stopWatch = new Stopwatch();

            stopWatch.Start();
            var orders = conn.Query<OrderDetail>("select * from [OrderDetail]");
            stopWatch.Stop();

            Console.WriteLine($"Method 1: Retrieved {orders.Count} records in {stopWatch.ElapsedMilliseconds} ms.");

            return stopWatch.Elapsed;
        }

        static TimeSpan DoTest2(SQLiteConnection conn)
        {
            Stopwatch stopWatch = new Stopwatch();

            stopWatch.Start();
            var stmt = SQLite3.Prepare2(conn.Handle, "select * from [OrderDetail]");
            var orders = new List<OrderDetail>();

            while (SQLite3.Step(stmt) == SQLite3.Result.Row)
            {
                var id = SQLite3.ColumnInt(stmt, 0);
                var orderId = SQLite3.ColumnInt(stmt, 1);
                var productId = SQLite3.ColumnInt(stmt, 2);
                var quantity = SQLite3.ColumnInt(stmt, 3);
                var name = SQLite3.ColumnString(stmt, 4);

                var orderDetail = new OrderDetail()
                {
                    Id = id,
                    OrderId = orderId,
                    ProductId = productId,
                    Quantity = quantity,
                    Name = name
                };
                orders.Add(orderDetail);
            }
            stopWatch.Stop();

            Console.WriteLine($"Method 2: Retrieved {orders.Count} records in {stopWatch.ElapsedMilliseconds} ms.");

            return stopWatch.Elapsed;
        }
    }
}