C# Sql LocalDB Application Slow Performance

Question

I am completely new sql/database applications and am trying out a simple contact management applicaton using Visual Studio 2015 C#. I am using 'SQL Express LocalDB'. I have read on google that it is meant for development purpose, but microsoft also mentions that it could be used for production purpose too.

My problem is that when I try out the application from my developement system, the application first time takes few seconds to load but after that every query runs quickly. When I tried this on one my friends system, it takes time everytime I try to use any query. The database is just with 20-30 records.

I create new connection using 'new SqlConnection' and then execute command created by 'new SqlCommand' and after executing query I close the connection.

Here is the code snippet from my app

SqlConnection sqlConnection = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = ""C:\ContactsDB.mdf""; Integrated Security = True; Connect Timeout = 30";);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConnection;

sqlCmd.CommandText = "SELECT Id, first_name, last_name from ContactsMaster ORDER BY first_name";
sqlConnection.Open();
SqlDataReader reader = sqlCmd.ExecuteReader();
try
{
    while (reader.Read())
    {
        ListViewItem lvi = new ListViewItem(reader["first_name"]);
        listViewItems.Add(lvi);
        lvi.SubItems.Add(reader[0].ToString());
    }
}
finally
{
    reader.Close();
}
sqlConnection.Close();

Q. Should I keep the connection open all the time while app is running? I don't think this should be suggested. As if app crashes database can get corrupt.

One of the backdrop which ppl saying that LocalDB closes the connection every new milliseconds. So should I keep pinging the database every few milliseconds? Or I should not use localdb in production at all?

I want to make the app such that the requirement goes really low regaridng the database prerequisites. Like LocalDB installation is really seamless.

I have not used SQL Server Express, does Express installation is also seamless like LocalDB and can I use the connection string like LocalDB in Express too, giving the .mdf filename directly?

Answer 1

There are many things to take in count for ddbb performance, it's not a simple question. For such small amount of records there shouldn't be performance problems. Try storing the ddbb files in another disk different from OS disk, and even better, place data file and log file in different disks too. About your question, connections must be always closed and disposed properly in a finally block or inside a using block. Sql Express is very easy to install, and also use a connection string, been the biggest difference that it can be used across the network.