#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;
}
}
}
}
Comments