In my last post, I covered getting the Berkeley DB SQL API running under the covers of the System.Data.SQLite library for .NET. Another weekend has come and I’ve had some time to run some tests. The whitepaper Oracle Berkeley DB SQL API vs. SQLite API indicates that the Berkeley engine will have relatively the same read speed as SQLite but better write speed due to it’s page locking vs. file locking.
Berkeley Read Bug
Originally, my code would write a certain number of rows in a given transaction on one or more threads and then read all rows on one or more threads, but I had to give up on my read code because the Berkeley implementation of System.Data.SQLite threw a "malloc() failed out of memory" exception whenever I ran the read test with more than one thread. You can read the code below and decide for yourself whether I made a mistake or not, but the test ran fine with the SQLite assembly from the sqlite.org site.
The Tests
There were four tests, each inserting similar but different data into a single table with a column representing each of the main data types that might be used in a typical application. Two single threaded tests, the first inserting 6,000 rows in a single transaction and the second 30,000 rows. The third and fourth tests were multithreaded: 6 threads inserting 1,000 rows each and then 10 threads inserting 500 rows each.
Here’s the SQL for the table:
CREATE TABLE [TT]
(
[src] TEXT,
[td] DATETIME,
[tn] NUMERIC,
[ti] INTEGER,
[tr] REAL,
[tb] BLOB
);
The Results
The results do not show a vast difference in insert speed for Berkeley as I might have expected. The tests were run on an AMD 6 core machine with 8GB of RAM. I’m writing to an SSD drive which may or may not affect results as well.
You may choose to interpret the results differently, but I don’t see enough difference to make me abandon the more stable SQLite 3 engine at this time.
The Code
I invite you to review the code. If I’ve missed something, please let me know. It’s pretty straightforward. With each test, I wipe out the database environment entirely, starting with a fresh database file. I use the System.Threading.Tasks library.
class Program
{
static void Main(string[] args)
{
Console.WriteLine("SQLite Tests");
var tester = new Tester();
tester.DoTest("1 thread with 6000 rows", 1, 6000);
tester.DoTest("6 threads with 1000 rows each", 6, 1000);
tester.DoTest("1 thread with 30000 rows", 1, 30000);
tester.DoTest("10 threads with 500 rows each", 10, 500);
Console.WriteLine("tests complete");
Console.ReadLine();
}
}
class Tester
{
public void DoTest(string testName, int threadCount, int rowCount)
{
DataMan.Create();
Console.WriteLine("");
Console.WriteLine(testName + " started:");
List<Task> insertTasks = new List<Task>();
DateTime beginInsert = DateTime.Now;
for (int i = 0; i < threadCount; i++)
{
insertTasks.Add(Task.Factory.StartNew(() => InsertTest(i, rowCount), TaskCreationOptions.None));
}
Task.WaitAll(insertTasks.ToArray());
DateTime endInsert = DateTime.Now;
TimeSpan tsInsert = endInsert - beginInsert;
//List<Task> readTasks = new List<Task>();
//DateTime beginRead = DateTime.Now;
//for (int i = 0; i < threadCount; i++)
//{
// readTasks.Add(Task.Factory.StartNew(() => ReadTest(i), TaskCreationOptions.None));
//}
//Task.WaitAll(readTasks.ToArray());
//DateTime endRead = DateTime.Now;
//TimeSpan tsRead = endRead - beginRead;
double rowsPerSecondInsert = (threadCount * rowCount) / tsInsert.TotalSeconds;
//double rowsPerSecondRead = (threadCount * rowCount) / tsRead.TotalSeconds;
Console.WriteLine("{0} threads each insert {1} rows in {0} ms", threadCount, rowCount, tsInsert.TotalMilliseconds);
//Console.WriteLine("{0} threads each read all rows in {1} ms", threadCount, tsRead.TotalMilliseconds);
Console.WriteLine("{0} rows inserted per second", rowsPerSecondInsert);
//Console.WriteLine("{0} rows read per second", rowsPerSecondRead);
}
void InsertTest(int threadId, int rowCount)
{
DateTime begin = DateTime.Now;
DataMan.InsertRows(rowCount);
DateTime end = DateTime.Now;
TimeSpan ts = end - begin;
//Console.WriteLine("{0} lines inserted in {1} ms on thread {2}", rowCount, ts.TotalMilliseconds, threadId);
}
void ReadTest(int threadId)
{
DateTime begin = DateTime.Now;
int count = DataMan.ReadAllRows();
DateTime end = DateTime.Now;
TimeSpan ts = end - begin;
//Console.WriteLine("{0} rows read in {1} ms on thread {2}", count, ts.TotalMilliseconds, threadId);
}
}
internal static class DataMan
{
private const string DbFileName = @"C:\temp\bdbvsqlite.db";
private const string DbJournalDir = @"C:\temp\bdbvsqlite.db-journal";
public static void Create()
{
if (File.Exists(DbFileName)) File.Delete(DbFileName);
if (Directory.Exists(DbJournalDir)) Directory.Delete(DbJournalDir, true);
//Console.WriteLine(SQLiteConnection.SQLiteVersion);
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = "Data Source=" + DbFileName + ";";
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 180;
cmd.CommandText = "DROP TABLE IF EXISTS [TT]; CREATE TABLE [TT] ([src] TEXT, [td] DATETIME, [tn] NUMERIC, [ti] INTEGER, [tr] REAL, [tb] BLOB);";
cmd.CommandType = System.Data.CommandType.Text;
int result = cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public static void InsertRows(int rowCount)
{
SQLiteParameter srParam = new SQLiteParameter();
SQLiteParameter tdParam = new SQLiteParameter();
SQLiteParameter tnParam = new SQLiteParameter();
SQLiteParameter tiParam = new SQLiteParameter();
SQLiteParameter trParam = new SQLiteParameter();
SQLiteParameter tbParam = new SQLiteParameter();
Random rnd = new Random(DateTime.Now.Millisecond);
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = "Data Source=" + DbFileName + ";";
conn.Open();
using (SQLiteTransaction trans = conn.BeginTransaction())
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 180;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [TT] ([src],[td],[tn],[ti],[tr],[tb]) VALUES (?,?,?,?,?,?);";
cmd.Parameters.Add(srParam);
cmd.Parameters.Add(tdParam);
cmd.Parameters.Add(tnParam);
cmd.Parameters.Add(tiParam);
cmd.Parameters.Add(trParam);
cmd.Parameters.Add(tbParam);
int count = 0;
while (count < rowCount)
{
var data = MakeRow(rnd);
srParam.Value = data.src;
tdParam.Value = data.td;
tnParam.Value = data.tn;
tiParam.Value = data.ti;
trParam.Value = data.tr;
tbParam.Value = data.tb;
int result = cmd.ExecuteNonQuery();
count++;
}
trans.Commit();
}
conn.Close();
}
}
public static int ReadAllRows()
{
List<DRow> list = new List<DRow>();
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = "Data Source=" + DbFileName + ";";
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 180;
cmd.CommandText = "SELECT [rowid],[src],[td],[tn],[ti],[tr],[tb] FROM [TT];";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var row = new DRow
{
rowid = reader.GetInt64(0),
src = reader.GetString(1),
td = reader.GetDateTime(2),
tn = reader.GetDecimal(3),
ti = reader.GetInt64(4),
tr = reader.GetDouble(5)
//tb = (byte[])reader.GetValue(6)
};
list.Add(row);
}
reader.Close();
}
}
conn.Close();
}
return list.Count;
}
private static DRow MakeRow(Random rnd)
{
int start = rnd.Next(0, 250);
byte[] b = new byte[512];
rnd.NextBytes(b);
return new DRow
{
src = spear.Substring(start, 250),
td = DateTime.Now,
tn = rnd.Next(2999499,987654321),
ti = rnd.Next(3939329,982537553),
tr = rnd.NextDouble(),
tb = b
};
}
private const string spear = @"FROM fairest creatures we desire increase,That thereby beauty's rose might never die,But as the riper should by time decease,His tender heir might bear his memory:But thou, contracted to thine own bright eyes,Feed'st thy light'st flame with self-substantial fuel,Making a famine where abundance lies,Thyself thy foe, to thy sweet self too cruel.Thou that art now the world's fresh ornamentAnd only herald to the gaudy spring,Within thine own bud buriest thy contentAnd, tender churl, makest waste in niggarding.Pity the world, or else this glutton be,To eat the world's due, by the grave and thee.";
}
internal class DRow
{
public long rowid { get; set; }
public string src { get; set; }
public DateTime td { get; set; }
public decimal tn { get; set; }
public long ti { get; set; }
public double tr { get; set; }
public byte[] tb { get; set; }
}