In-memory database slower - retrieving data from DB

Question

I have been trying to evaluate BrightStar DB for my project initially what I did is -

Using brightstar DB (server sw) I stored my SQL Server database table into its default store i.e. file location (C:\Program Files\BrightstarDB\Data) and then to measure performance gain compared to SQL Server database, I tried to query a table both in memory and SQL Server and tried to load into a gridview.

To my surprise I could see the time taken to load the data directly from SQL Server is half that of in memory db. I don't know if this is right way to check performance - if anyone has experienced working with in memory please guide.

Below is the code used to load direct from SQL Server and for in memory I followed this link http://brightstardb.com/documentation/Developing_With_BrightstarDB2.html.

protected void BtnDatabase_Click(object sender, EventArgs e)
{
        try
        {
            GridView2.DataSource = null;

            TDNdc = TextBox1.Text;
            if (!string.IsNullOrEmpty(TDNdc))
                selectCommand = "select * from dbo.TD_List where ID='" + TDNdc + "'";

            String connectionString = @"data source=TD-abc\SQLEXPRESS;initial catalog=ScriptSave;integrated security=True";
            DateTime varDateTime = DateTime.Now; 
            dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
            // Create a command builder to generate SQL update, insert, and 
            // delete commands based on selectCommand.
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
           // table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table);
            TimeSpan result = DateTime.Now - varDateTime;
            GridView2.DataSource = table;
            GridView2.DataBind();

            Response.Write("Time Taken to load " + GridView2.Rows.Count + " Record(s) From DB : " + result);
            //LblInMem.Text = GridView2.Rows.Count + " Record(s) From DB : " + result;  
            //Response.Write("Time Taken to load datafrom DataBase " + result.ToString() + "Total Record :" + GridView2.Rows.Count);
        }
        catch (SqlException)
        {
        }
}

Do you have any numbers to support your claim? Did you load a single integer or perform a complex series of operations on 10M rows with 100 columns? Indexes? — HABO, Mar 27 '13 at 14:00
Based on the code you've got here, my guess is that you've chosen a poor way to benchmark the performance of any database. Have you tested the same dataset, with the same queries, same data types, and performed the operations many times over (to eliminate any first-access slowdowns)? `select *` will be processed differently by the query optimizer than selecting the fields by name, and is not representative of good practices in querying any database. — alroc, Mar 27 '13 at 14:49
Well I used the same table in both the cases same query and data types. I perfomed this n number of time but the result was same either when I select some 500 records or a single record.I am aware select * is not good practice since I just wanted to know the preformance I used in both the test scenario and observed ado object were much faster comapre to in memory db. — Harry, Mar 28 '13 at 02:57

score 2 · Answer 1 · answered Jun 26 '13 at 13:02

I think there is a misunderstanding here. BrightstarDB is not an in-memory database - in fact it is a fully transactional persistent data store. So you still have disk access penalties to pay. In my testing I've found BrightstarDB to generally be disk-bound...running it on a fast disk / SSD really helps performance, though I guess you will find the same to be true of SQL Server.

As alroc mentions, I think you are better to benchmark on the types of queries your application will use and to balance performance considerations against the other features that BrightstarDB offers, which you may (or may not) decide outweigh the performance penalties for particular types of query.

You should also consider using SPARQL rather than LINQ to write your BrightstarDB queries. There is a performance penalty to be paid for using LINQ as the LINQ query is converted to SPARQL before being executed.

In-memory database slower - retrieving data from DB

1 Answers1