#define KEEP_CONNECTION_OPEN

namespace PerformanceMdbVsSqlCe
{
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using System.Data.SqlServerCe;
    using System.Data.SQLite;
    using System.Diagnostics;
    using VistaDB.Provider;

    /*
    Performance comparisons (maybe somewhat naive) between:

        - SQL Server Express 2014 on the same local PC and local SSD drive as the test application.
        - SQL Server Express 2014 on a server in a gigabit LAN.
        - SQL Server Compact (CE) on the same local PC and local SSD drive as the test application.
        - Microsoft Access MDB/Jet on the same local PC and local SSD drive as the test application.
        - Microsoft SQLite on the same local PC and local SSD drive as the test application.
        - Microsoft VistaDB 5 on the same local PC and local SSD drive as the test application.

    See also:

        - http://pastebin.com/edit/NNJni52W - This file on Pastebin.
        - http://stackoverflow.com/a/35225563/107625 - My question/answer on Stack Overflow.
        - http://entwicklergate.de/t/125 - Posting in my German developer community.

        - http://uwe.co - My personal weblog :-)
    */

    internal static class Program
    {
        private const string Folder = @"C:\Users\ukeim\Dropbox\Beruf\Prog\PerformanceMdbVsSqlCe\PerformanceMdbVsSqlCe";

        private static readonly string SqlCeConnectionString = $@"DataSource=""{Folder}\test.sdf""";
        private static readonly string SqlServerLocaleConnectionString = $@"Server=W8-UWEKEIM\SQLEXPRESS2014;Database=Test;Integrated Security=True;";
        private static readonly string SqlServerNetworkConnectionString = $@"Server=ZETAS23\SQLEXPRESS2014;Database=Test;Integrated Security=True;";
        private static readonly string VistaDBConnectionString = $@"Data Source={Folder}\test.vdb5";
        private static readonly string SqliteConnectionString = $@"Data Source={Folder}\test.db";

        private static readonly string MdbConnectionString =
            $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""{Folder}\test.mdb""";

        private static int _counter;

        private static void Main()
        {
#if KEEP_CONNECTION_OPEN
            connSqlCe.Open();
            connSqlLocale.Open();
            sqlNetworkConn.Open();
            mdbConn.Open();
            sqliteConn.Open();
            vistaDBConn.Open();
#endif
            /*
            File.Delete($@"{Folder}\test.sdf");

            createSqlCeDB();
            executeSqlCe(
                @"CREATE TABLE [Tabelle1] (
                    [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
                    Name1 NVARCHAR(255),
                    Wert1 [int]
                )");
            */

            //executeSqlite(
            //    @"CREATE TABLE [Tabelle1] (
            //        [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
            //        Name1 NVARCHAR(255),
            //        Wert1 [int]
            //    )");

            test1(1, @"DELETE FROM Tabelle1");

            test1(1, @"INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}')");
            test1(10, @"INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}')");
            test1(100, @"INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}')");
            test1(1000, @"INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}')");

            test1(1, @"SELECT * FROM Tabelle1");
            test1(10, @"SELECT * FROM Tabelle1");
            test1(100, @"SELECT * FROM Tabelle1");
            test1(1000, @"SELECT * FROM Tabelle1");

            test1(1, @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID");
            test1(10, @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID");
            test1(100, @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID");
            test1(1000, @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID");

            test1(1,
                @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID");
            test1(10,
                @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID");
            test1(100,
                @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID");
            test1(1000,
                @"SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID");
        }

        private static void test1(int rowCount, string sql)
        {
            var sw = new Stopwatch();

            // --

#if KEEP_CONNECTION_OPEN
            const string keep = @"keeping connection open";
#else
            const string keep = @"Closing connections";
#endif

            Console.WriteLine(@"{0}.: {1} x {2} ({3}):", ++_counter, rowCount, sql, keep);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeSqlServerLocale(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- SQL Express local : {0}", sw.Elapsed);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeSqlServerNetwork(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- SQL Express remote: {0}", sw.Elapsed);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeSqlCe(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- SQL CE            : {0}", sw.Elapsed);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeMdb(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- MS Access         : {0}", sw.Elapsed);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeSqlite(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- SQLite            : {0}", sw.Elapsed);

            // --

            sw.Start();
            for (var i = 0; i < rowCount; i++)
            {
                executeVistaDB(sql.Replace(@"{LOOPCTR}", i.ToString()));
            }
            sw.Stop();

            Console.WriteLine(@"- VistaDB           : {0}", sw.Elapsed);

            // --

            Console.WriteLine();
        }

        private static void createSqlCeDB()
        {
            var en = new SqlCeEngine(SqlCeConnectionString);
            en.CreateDatabase();
        }

#if KEEP_CONNECTION_OPEN
        private static readonly SqlCeConnection connSqlCe = new SqlCeConnection(SqlCeConnectionString);
#endif

        private static DataTable executeSqlCe(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var connSqlCe = new SqlCeConnection(SqlCeConnectionString))
#endif
            using (var da = new SqlCeDataAdapter(sql, connSqlCe))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }

#if KEEP_CONNECTION_OPEN
        private static readonly SqlConnection connSqlLocale = new SqlConnection(SqlServerLocaleConnectionString);
#endif

        private static DataTable executeSqlServerLocale(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var connSqlLocale = new SqlConnection(SqlServerLocaleConnectionString))
#endif
            using (var da = new SqlDataAdapter(sql, connSqlLocale))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }

#if KEEP_CONNECTION_OPEN
        private static readonly SqlConnection sqlNetworkConn = new SqlConnection(SqlServerNetworkConnectionString);
#endif

        private static DataTable executeSqlServerNetwork(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var sqlNetworkConn = new SqlConnection(SqlServerNetworkConnectionString))
#endif
            using (var da = new SqlDataAdapter(sql, sqlNetworkConn))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }

#if KEEP_CONNECTION_OPEN
        private static readonly VistaDBConnection vistaDBConn = new VistaDBConnection(VistaDBConnectionString);
#endif

        private static DataTable executeVistaDB(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var vistaDBConn = new VistaDBConnection(VistaDBConnectionString))
#endif
            using (var da = new VistaDBDataAdapter(sql, vistaDBConn))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }

#if KEEP_CONNECTION_OPEN
        private static readonly OleDbConnection mdbConn = new OleDbConnection(MdbConnectionString);
#endif

        private static DataTable executeMdb(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var mdbConn = new OleDbConnection(MdbConnectionString))
#endif
            using (var da = new OleDbDataAdapter(sql, mdbConn))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }

#if KEEP_CONNECTION_OPEN
        private static readonly SQLiteConnection sqliteConn = new SQLiteConnection(SqliteConnectionString);
#endif

        private static DataTable executeSqlite(string sql)
        {
#if !KEEP_CONNECTION_OPEN
            using (var sqliteConn = new SQLiteConnection(SqliteConnectionString))
#endif
            using (var da = new SQLiteDataAdapter(sql, sqliteConn))
            {
                var table = new DataTable();
                da.Fill(table);

                return table;
            }
        }
    }
}