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?

Here are the updates, after launching app the first time we make a connection to sql localdb .mdf database, the sqlConnection.open() function takes around 10-15 seconds. Later in that session, opening the db takes around 2 seconds. I want to minimize this 10-15 seconds time. — , Apr 16 '16 at 15:18

bh_earth0 · Answer 1 · 2019-05-16T09:40:53.543

1

localdb has auto shutdown. default is 5 min. you can set it to higher value (ie: 12hour). max is 65535 min.

see: How to prevent SQL Server LocalDB auto shutdown?

also sqlexpress autoshutdown is 1hour if im not wrong.

symptoms on my pc:

first open is 10- 30 seconds slow. if i reopen app right after it is below 1 second. if i wait for a while it is slow again

edited May 16 '19 at 09:40

answered May 16 '19 at 09:30

bh_earth0

2,537
22
24

score 0 · Accepted Answer · answered Apr 13 '16 at 18:52

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.

score 0 · Answer 3 · answered Apr 19 '16 at 16:18

0

Finally moved to SQLite and that is much faster in compare to SQLLocalDB.

answered Apr 19 '16 at 16:18

C# Sql LocalDB Application Slow Performance

3 Answers3