0 Replies Latest reply: Jan 7, 2014 2:57 PM by user7080275 RSS

Performance hit moving from SQLite to BDB for c#/.NET app

user7080275 Newbie
Currently Being Moderated

Hi All,

 

I've been searching all day for an answer to this. Just trying to do some basic tests to see if one db system performs noticeably better than others. SQLite does okay. It's not great, but it's not terrible. I thought I would check out BDB. And it is completely horrible. It's so bad, I must have done something horribly wrong.

 

My computer setup:

Win7 Pro x64

8GB Ram, Core i7-3770 @ 3.4GHz

SSD

 

My DBD setup: followed instructions to build SQL API and get the DLLs from that process referenced.

This is my test code. Don't laugh too hard; I've never worked in c# before.

 

So the results I get from SQLite are on average about 1/5ms for the read test. I don't care about the writes so much at the moment. After I do a drop in conversion to BDB and create and populate the database with random data, my readtimes are 350-400ms.

 

I'm hoping someone can point me in the right direction.

 

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.Common;

using System.Diagnostics;

using System.Data.SQLite;

 

 

namespace bdbench

{

    class Program

    {

        static void Main()

        {

 

 

            //var results = new resultsDB();  //just do this once to create the results file

            var DB1 = new DB("DB1");

            //DB1.createDB("DB1");  //just do this once to create the test file

 

 

            //DB1.populateDB(10000);

            DB1.testDB(10000);

 

 

 

 

        }

    }

 

 

    class DB

    {

        private string DBname;

        private int numRows;

 

 

 

 

        public DB(string DBname)

        {

            //create the dabase object

 

 

            this.DBname = DBname;

        }

 

 

 

 

        public void createDB(string DBname)

        {

            //create tables

            this.DBname = DBname;

 

 

            using (var conn = new SQLiteConnection("Data Source=" + DBname + ".sqlite;Version=3;"))

            using (var cmd = conn.CreateCommand())

            {

                conn.Open();

                cmd.CommandText = "CREATE TABLE IF NOT EXISTS perms (username VARCHAR(20), mid INT, permission INT); CREATE INDEX IF NOT EXISTS unameindex ON perms (username); CREATE INDEX IF NOT EXISTS midindex ON perms (mid)";

                cmd.ExecuteNonQuery();

                conn.Close();

            }

        }

 

 

        public void populateDB(int numRows)

        {

            //put some data into the db

            string DBname = this.DBname;

            this.numRows = numRows;

 

 

            using (var conn = new SQLiteConnection("Data Source=" + DBname + ".sqlite;Version=3;"))

            using (var cmd = conn.CreateCommand())

            {

                conn.Open();

 

 

                for (int i = 0; i < numRows; i++)

                {

 

 

                    Random unumber = new Random();

                    int uname = unumber.Next(0, 20);

                    Random idnumber = new Random();

                    int mid = idnumber.Next(0, 100);

                    Random valuenumber = new Random();

                    int perm = valuenumber.Next(0, 45);

                    cmd.CommandText = "INSERT into [table] ([name], [id], [value]) VALUES (@name, @id, @value)";

                    cmd.Parameters.Add(new SQLiteParameter("@name") { Value = "name" + uname.ToString() });

                    cmd.Parameters.Add(new SQLiteParameter("@id") { Value = 1000 + mid });

                    cmd.Parameters.Add(new SQLiteParameter("@value") { Value = perm });

                    cmd.ExecuteNonQuery();

 

 

 

 

                }

                conn.Close();

            }

 

 

 

 

 

 

        }

 

 

        public void testDB(int reads)

        {

            //run tests here

            string DBname = this.DBname;

            List<float> readtimes = new List<float>();

            Stopwatch sw = new Stopwatch();

 

 

            for (int i = 0; i < reads; i++)

            {

                Random unumber = new Random();

                int uname = unumber.Next(0, 20);

                Random midnumber = new Random();

                int mid = midnumber.Next(0, 100);

                Random permnumber = new Random();

                int perm = permnumber.Next(0, 45);

                using (var conn = new SQLiteConnection("Data Source=" + DBname + ".sqlite;Version=3;"))

                using (var cmd = conn.CreateCommand())

                {

                    conn.Open();

                    cmd.CommandText = "SELECT permission FROM perms where username = @username and mid = @mid";

                    cmd.Parameters.Add(new SQLiteParameter("@username") { Value = "username" + uname.ToString() });

                    cmd.Parameters.Add(new SQLiteParameter("@mid") { Value = 1000 + mid });

                    sw.Reset();

                    sw.Start();

                    cmd.ExecuteReader();

                    sw.Stop();

                    conn.Close();

                }

 

 

 

 

                Console.WriteLine("Elapsed={0}", sw.Elapsed.Ticks);

                var r = sw.Elapsed.Ticks;

                readtimes.Add(r);

 

 

 

 

            }

            var stDev = CalculateStdDev(readtimes);

 

 

            Console.WriteLine("Min:\t" + readtimes.Min().ToString() + "\tMax:\t" + readtimes.Max().ToString() + "\tAvg:\t" + readtimes.Average().ToString() + "\tStdDev:\t" + stDev.ToString());

 

 

            using (StreamWriter writer = File.AppendText("C:\\Users\\amartin\\results_all.txt"))

            {

                writer.WriteLine("Min:\t" + readtimes.Min().ToString() + "\tMax:\t" + readtimes.Max().ToString() + "\tAvg:\t" + readtimes.Average().ToString() + "\tStdDev:\t" + stDev.ToString());

                foreach (float i in readtimes)

                //{

                    //writer.WriteLine(i);

                //}

            }

        }

 

 

        public double CalculateStdDev(IEnumerable<float> values)

        {

            double ret = 0;

            if (values.Count() > 0)

            {

                //Compute the Average    

                double avg = values.Average();

                //Perform the Sum of (value-avg)_2_2    

                double sum = values.Sum(d => Math.Pow(d - avg, 2));

                //Put it all together    

                ret = Math.Sqrt((sum) / (values.Count() - 1));

            }

            return ret;

        }

    }

}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points