Hi,
I have been writing some simple benchmarks to compare different backends. I want to perform some initial performance test to determine which would fit our application the best. According to http://www.sqlite.org/cvstrac/wiki?p=KeyValueDatabase sqllite.net should outpeform (persistant) berkely DB. However my results show that although sqllite is faster for inserts it is significantly slower for selects. Berkeley DB .net wrapper from: http://www.oracle.com/technology/software/products/berkeley-db/index.html
I want to check to see if my test is correctly configurationg SQLite and that the results are correct. I do realize that berkely DB is not as fully featured as SQLite.
Thanks for any information.
the test is performing key value inserts for two cases, then reading keys from each case:
1. int,int. (50k entries)
2,int ,byte[ (1000bytes) (1000 entries)
Test results
SQLLITE:
Write time for SqlLite is 00:00:00.3705241 00:00:00.3705241
1000 Record read time for SqlLite 00:00:00.6086368
1000 Byte arr write speed is 00:00:00.0391815
1000 Byte arr read time is 00:00:00.6120065
BDB
Write time for BerkelyDB is 00:00:01.0765183 00:00:01.0765183
1000 Record read time for BerkelyDB 00:00:00.0490953
1000 Byte arr write speed is 00:00:00.0232596
1000 Byte arr read time is 00:00:00.0202115
This is my test class for SQLite. I defined an interface for testing and use the same exact test code for each.
class sqlLiteDB : IdataTest
{
#region IdataTest Members
public const string CREATE_TABLE_NODE_TABLE = "CREATE TABLE NodeTable (key INT PRIMARY KEY, val INT)";
public const string CREATE_TABLE_DATA_TABLE = "CREATE TABLE DataTable (key INT PRIMARY KEY, val BLOB)";
SQLiteConnection conn;
SQLiteTransaction tran;
SQLiteCommand command;
public sqlLiteDB()
{
string dbname = @"C:\temp\mysqlTest10.db";
SQLiteConnection.CreateFile(dbname);
conn = new SQLiteConnection(String.Format(@"Data Source={0} ;Synchronous=Off; Journal Mode=Off; ", dbname)); //Cache Size=10000000
conn.Open();
command = conn.CreateCommand();
command.CommandText = CREATE_TABLE_NODE_TABLE;
command.ExecuteNonQuery();
command.CommandText = CREATE_TABLE_DATA_TABLE;
command.ExecuteNonQuery();
command.CommandText = "PRAGMA locking_mode = exclusive";
command.ExecuteNonQuery();
conn.Close();
}
public string getName()
{
return "SqlLite";
}
public void startWrite()
{
conn.Open();
command = conn.CreateCommand();
tran = conn.BeginTransaction();
command.Transaction = tran;
command.CommandText = "INSERT INTO NodeTable(key,val) VALUES(@k,@v) ";
command.Parameters.Add("v", System.Data.DbType.Int32);
command.Parameters.Add("k", System.Data.DbType.Int32);
}
public void Write(int key, int val)
{
command.Parameters["v"].Value = val;
command.Parameters["k"].Value = key;
command.ExecuteNonQuery();
}
public void stopWrite()
{
tran.Commit();
conn.Close();
}
public int ReadVal(int key)
{
conn.Open();
SQLiteCommand readCom = conn.CreateCommand();
readCom.CommandText = String.Format(@"SELECT val FROM NodeTable WHERE key=@k");
readCom.Parameters.AddWithValue("k", key);
int val = Convert.ToInt32(readCom.ExecuteScalar());
conn.Close();
return val;
}
public int ReadKey(int val)
{
conn.Open();
SQLiteCommand readCom = conn.CreateCommand();
readCom.CommandText = String.Format(@"SELECT key FROM NodeTable WHERE val='{0}'", val);
int key = Convert.ToInt32(readCom.ExecuteScalar());
conn.Close();
return key;
}
public void close()
{
conn.Clone();
}
#endregion
#region IdataTest Members
public void startByteWrite()
{
conn.Open();
command = conn.CreateCommand();
tran = conn.BeginTransaction();
command.Transaction = tran;
command.CommandText = "INSERT INTO DataTable(key,val) VALUES(@k,@v) ";
command.Parameters.Add("v", System.Data.DbType.Binary);
command.Parameters.Add("k", System.Data.DbType.Int32);
//conn.Close();
}
public void stopByteWrite()
{
tran.Commit();
conn.Close();
}
public void WriteByte(int key, byte[ arr)
{
command.Parameters["v"].Value = arr;
command.Parameters["k"].Value = key;
command.ExecuteNonQuery();
}
public byte[ ReadByte(int key)
{
conn.Open();
SQLiteCommand readCom = conn.CreateCommand();
readCom.CommandText = String.Format(@"SELECT val FROM DataTable WHERE key=@k");
readCom.Parameters.AddWithValue("k", key);
byte[ val =(byte[)(readCom.ExecuteScalar());
conn.Close();
return val;
}
#endregion
}
----------------------------------------------------------------------------------------------------------------
Test code
static void test()
{
System.Diagnostics.Stopwatch stp = new System.Diagnostics.Stopwatch();
List<IdataTest> testList= new List<IdataTest>();
testList.Add(new sqlLiteDB());
//testList.Add(new MySerialize(@"C:\temp\mys.db"));
//testList.Add(new DBFTest());
testList.Add(new BerkDB());
//testList.Add(new bplusTest());
foreach (IdataTest testDB in testList)
{ stp.Reset();
stp.Start();
testDB.startWrite();
for (int i = 1; i < 50000; i++)
{
testDB.Write(i, i);
}
testDB.stopWrite();
stp.Stop();
Console.WriteLine(String.Format("Write time for {0} is {1} {2}", testDB.getName(), stp.Elapsed.ToString(), stp.Elapsed.ToString()));
stp.Reset();
testDB.ReadVal(24532);
stp.Start();
Random rand = new Random();
for(int i=0;i<1000;i++)
testDB.ReadVal(rand.Next(40000+1));
stp.Stop();
Console.WriteLine(String.Format("1000 Record read time for {0} {1} ", testDB.getName(), stp.Elapsed.ToString()));
//if(!testDB.GetType().Name.Equals("BerkDB"))
//{
// stp.Reset();
// stp.Start();
// testDB.ReadKey(8000);
// stp.Stop();
// Console.WriteLine(String.Format("Non indexed record search time for {0} {1}",testDB.getName(), stp.ElapsedTicks));
//}
byte[ arr = new byte[1000];
stp.Reset();
stp.Start();
testDB.startByteWrite();
for (int i = 0; i < 1000; i++)
{
testDB.WriteByte(i, arr);
}
testDB.stopByteWrite();
stp.Stop();
Console.WriteLine(String.Format("1000 Byte arr write speed is {0}",stp.Elapsed.ToString()));
stp.Reset();
stp.Start();
for(int i=0;i<1000;i++)
testDB.ReadByte(rand.Next(900+1));
stp.Stop();
Console.WriteLine(String.Format("1000 Byte arr read time is {0}", stp.Elapsed.ToString()));
testDB.close();
}
//Console.ReadKey();
}
----------------------------------------------------------------------
BDB test class
class BerkDB : IdataTest
{
#region IdataTest Members
Database btreeDB;
Database dataDB;
DatabaseEnvironment env;
public BerkDB()
{
DatabaseEnvironmentConfig envCfg = new DatabaseEnvironmentConfig();
envCfg.Create = true;
envCfg.UseMPool = true;
envCfg.UseLocking = true;
envCfg.UseLogging = true;
envCfg.UseTxns = true;
envCfg.Private = true;
// Specify in-memory logging.
envCfg.LogSystemCfg = new LogConfig();
envCfg.LogSystemCfg.InMemory = true;
/*
* Specify the size of the in-memory log buffer
* Must be large enough to handle the log data
* created by the largest transaction.
*/
envCfg.LogSystemCfg.BufferSize = 16 * 500000;
/*
* Specify the size of the in-memory cache,
* large enough to avoid paging to disk.
*/
envCfg.MPoolSystemCfg = new MPoolConfig();
envCfg.MPoolSystemCfg.CacheSize =
new CacheInfo(0, 16*500000, 1);
BTreeDatabaseConfig dbCfg = new BTreeDatabaseConfig();
dbCfg.Creation = CreatePolicy.IF_NEEDED;
dbCfg.ErrorPrefix = "blah";
//dbCfg.UseRecordNumbers = true;
//dbCfg.Env = new DatabaseEnvironment();
//dbCfg.Env.TxnNoSync = true;
//dbCfg.Env.TxnNoWait = true;
//envCfg.RunRecovery = true;
//env = DatabaseEnvironment.Open(@"C:\temp", envCfg);
/*
* Open the database. Do not provide a txn handle. This
* Open is autocommitted because BTreeDatabaseConfig.AutoCommit
* is true.
*/
env = DatabaseEnvironment.Open(@"C:\temp", envCfg);
// dbCfg.Env = env;
// btreeDB= BTreeDatabase.Open(@"berkDB9.db", "test", dbCfg);
HashDatabaseConfig hCfg = new HashDatabaseConfig();
hCfg.Creation = CreatePolicy.IF_NEEDED;
hCfg.ErrorPrefix = "blah";
hCfg.Env = env;
btreeDB = HashDatabase.Open("berkdb", hCfg);
dataDB = HashDatabase.Open("datadb", hCfg);
}
public string getName()
{
return "BerkelyDB";
}
Transaction txn;
public void startWrite()
{
//throw new NotImplementedException();
//btreeDB.tr
txn = env.BeginTransaction();
}
public void Write(int key, int val)
{
DatabaseEntry ky = new DatabaseEntry(BitConverter.GetBytes(key));
DatabaseEntry vl = new DatabaseEntry(BitConverter.GetBytes(val));
btreeDB.Put(ky, vl);
}
public void stopWrite()
{
// throw new NotImplementedException();
txn.Commit();
}
public int ReadVal(int key)
{
DatabaseEntry ky = new DatabaseEntry(BitConverter.GetBytes(key));
System.Collections.Generic.KeyValuePair<DatabaseEntry,DatabaseEntry> kvp = btreeDB.Get(ky);
return BitConverter.ToInt32(kvp.Value.Data,0);
}
public int ReadKey(int val)
{
//btreeDB.
return -1;
}
public void close()
{
//throw new NotImplementedException();
}
#endregion
#region IdataTest Members
public void startByteWrite()
{
txn = env.BeginTransaction();
}
public void stopByteWrite()
{
txn.Commit();
}
public void WriteByte(int key, byte[ arr)
{
DatabaseEntry ky = new DatabaseEntry(BitConverter.GetBytes(key));
DatabaseEntry vl = new DatabaseEntry(arr);
dataDB.Put(ky, vl);
}
public byte[ ReadByte(int key)
{
DatabaseEntry ky = new DatabaseEntry(BitConverter.GetBytes(key));
System.Collections.Generic.KeyValuePair<DatabaseEntry, DatabaseEntry> kvp =dataDB.Get(ky);
return kvp.Value.Data;
}
#endregion
}