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