TestLiteDBVsAccessPerformance

// Related to:
// https://stackoverflow.com/questions/6470469/how-fast-is-sqlite-compared-to-microsoft-access-mdb

namespace TestLiteDBVsAccessPerformance
{
    using LiteDB;
    using System;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.IO;
    using System.Reflection;

    internal static class Program
    {
        private const int loopCount = 1000;

        private static void Main()
        {
            Console.WriteLine("Starting tests.");
            Console.WriteLine();

            // Einmalig löschen.
            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.db");
            if (File.Exists(path)) File.Delete(path);

            testMdbWrite();
            testLiteDBWrite();
            testLiteDBWriteInMemory();

            testMdbRead();
            testLiteDBRead();
            testLiteDBReadInMemory();

            Console.WriteLine();
            Console.WriteLine("Tests finished.");
        }

        private static void testMdbWrite()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.mdb");
            //Console.WriteLine($"Using Access at '{path}'.");

            if (!File.Exists(path))
            {
                File.WriteAllBytes(path, Resources.MyData);
            }

            // create connection and command
            using (var cn = new OleDbConnection($@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path}"))
            {
                const string query =
                    @"INSERT INTO Customers ([Name], Phones, IsActive) VALUES (@Name, @Phones, @IsActive)";

                for (var i = 0; i < loopCount; ++i)
                {
                    // Create your new customer instance
                    var customer = new Customer
                    {
                        Name = $"John Doe {i + 1}",
                        Phones = new[] { $"8000-0000 {i + 1}", $"9000-0000 {i + 1}" },
                        IsActive = true
                    };

                    using (var cmd = new OleDbCommand(query, cn))
                    {
                        // define parameters and their values
                        cmd.Parameters.Add(new OleDbParameter(@"Name", customer.Name));
                        cmd.Parameters.Add(new OleDbParameter(@"Phones", string.Join(@",", customer.Phones)));
                        cmd.Parameters.Add(new OleDbParameter(@"IsActive", customer.IsActive));

                        cn.Open();
                        cmd.ExecuteNonQuery();
                        cn.Close();
                    }
                }
            }

            sw.Stop();

            Console.WriteLine($"Access             of {loopCount} WRITE iterations took {sw.Elapsed}.");
        }

        private static void testMdbRead()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.mdb");
            //Console.WriteLine($"Using Access at '{path}'.");

            if (!File.Exists(path))
            {
                File.WriteAllBytes(path, Resources.MyData);
            }

            // create connection and command
            using (var cn = new OleDbConnection($@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path}"))
            {
                const string query = @"SELECT * FROM Customers WHERE [Name] like 'Jo%'";

                for (var i = 0; i < loopCount; ++i)
                {
                    using (var cmd = new OleDbCommand(query, cn))
                    {
                        cn.Open();
                        cmd.ExecuteNonQuery();
                        cn.Close();
                    }
                }
            }

            sw.Stop();

            Console.WriteLine($"Access             of {loopCount} READ  iterations took {sw.Elapsed}.");
        }

        private static void testLiteDBWrite()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.db");
            //if (File.Exists(path)) File.Delete(path);

            //Console.WriteLine($"Using LiteDB at '{path}'.");

            // Open database (or create if doesn't exist)
            using (var db = new LiteDatabase(path))
            {
                // Get a collection (or create, if doesn't exist)
                var col = db.GetCollection<Customer>(@"customers");

                for (var i = 0; i < loopCount; ++i)
                {
                    // Create your new customer instance
                    var customer = new Customer
                    {
                        Name = $"John Doe {i + 1}",
                        Phones = new[] { $"8000-0000 {i + 1}", $"9000-0000 {i + 1}" },
                        IsActive = true
                    };

                    // Insert new customer document (Id will be auto-incremented)
                    col.Insert(customer);

                    //// Update a document inside a collection
                    //customer.Name = "Joana Doe";

                    col.Update(customer);
                }

                // Index document using document Name property
                col.EnsureIndex(x => x.Name);

                // Use LINQ to query documents
                //var results = col.Find(x => x.Name.StartsWith("Jo"));
            }

            sw.Stop();

            Console.WriteLine($"LiteDB             of {loopCount} WRITE iterations took {sw.Elapsed}.");
        }

        private static void testLiteDBWriteInMemory()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.db");
            //if (File.Exists(path)) File.Delete(path);

            //Console.WriteLine($"Using LiteDB at '{path}'.");

            // Open database (or create if doesn't exist)
            using (var mem = readStream(path))
            {
                using (var db = new LiteDatabase(mem))
                {
                    // Get a collection (or create, if doesn't exist)
                    var col = db.GetCollection<Customer>(@"customers");

                    for (var i = 0; i < loopCount; ++i)
                    {
                        // Create your new customer instance
                        var customer = new Customer
                        {
                            Name = $"John Doe {i + 1}",
                            Phones = new[] { $"8000-0000 {i + 1}", $"9000-0000 {i + 1}" },
                            IsActive = true
                        };

                        // Insert new customer document (Id will be auto-incremented)
                        col.Insert(customer);

                        //// Update a document inside a collection
                        //customer.Name = "Joana Doe";

                        col.Update(customer);
                    }

                    // Index document using document Name property
                    col.EnsureIndex(x => x.Name);

                    // Use LINQ to query documents
                    //var results = col.Find(x => x.Name.StartsWith("Jo"));
                }

                File.WriteAllBytes(path, mem.ToArray());
            }

            sw.Stop();

            Console.WriteLine($"LiteDB (in-memory) of {loopCount} WRITE iterations took {sw.Elapsed}.");
        }

        private static void testLiteDBRead()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.db");
            //if (File.Exists(path)) File.Delete(path);

            //Console.WriteLine($"Using LiteDB at '{path}'.");

            // Open database (or create if doesn't exist)
            using (var db = new LiteDatabase(path))
            {
                // Get a collection (or create, if doesn't exist)
                var col = db.GetCollection<Customer>(@"customers");

                for (var i = 0; i < loopCount; ++i)
                {
                    var results = col.Find(x => x.Name.StartsWith("Jo"));
                    Debug.Assert(results != null);
                }
            }

            sw.Stop();

            Console.WriteLine($"LiteDB             of {loopCount} READ  iterations took {sw.Elapsed}.");
        }

        private static void testLiteDBReadInMemory()
        {
            var sw = new Stopwatch();
            sw.Start();

            var path = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"MyData.db");
            //if (File.Exists(path)) File.Delete(path);

            //Console.WriteLine($"Using LiteDB at '{path}'.");

            // Open database (or create if doesn't exist)
            using (var mem = readStream(path))
            {
                using (var db = new LiteDatabase(mem))
                {
                    // Get a collection (or create, if doesn't exist)
                    var col = db.GetCollection<Customer>(@"customers");

                    for (var i = 0; i < loopCount; ++i)
                    {
                        var results = col.Find(x => x.Name.StartsWith("Jo"));
                        Debug.Assert(results != null);
                    }
                }

                File.WriteAllBytes(path, mem.ToArray());
            }

            sw.Stop();

            Console.WriteLine($"LiteDB (in-memory) of {loopCount} READ  iterations took {sw.Elapsed}.");
        }

        private static MemoryStream readStream(string path)
        {
            using (var temp = new MemoryStream(File.ReadAllBytes(path)))
            {
                var ms = new MemoryStream();
                temp.CopyTo(ms);

                return ms;
            }
        }

        // Create your POCO class entity
        public class Customer
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string[] Phones { get; set; }
            public bool IsActive { get; set; }
        }
    }
}