I have some software that regularly hits a database to check for items to process.  Items are added to the database at semi-regular intervals.  My issue is that sometimes the inserts/updates into the database take an extraordinary time.  

To test this, I created a new program with a simple database:

        public static void Setup()
        {
            try
            {
                string conString = "Data Source=(localdb)\\v11.0;Integrated Security=True";

                // Open the connection using the connection string.
                using (SqlConnection con = new SqlConnection(conString))
                {
                    con.Open();
                    SqlCommand com = new SqlCommand();
                    com.Connection = con;
                    com.CommandText = string.Format("Create Database Test15");
                    com.ExecuteNonQuery();
                    com.CommandText = string.Format("Use Test15");
                    com.ExecuteNonQuery();
                    com.CommandText = string.Format("Create Table Table1 (Id int, Value nvarchar(100))");
                    com.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }

So far so good.  1 table, with an int and a varchar.

Next the insert:

        static void InsertUsingPool(int records)
        {
            string conString = "Data Source=(localdb)\\v11.0;Initial Catalog=Test15;Integrated Security=True;";

            ConnectionPoolLocalDb pool = new ConnectionPoolLocalDb(conString);
            SqlCommand sqlCmd = new SqlCommand("INSERT INTO Table1 (Id, Value) VALUES (@Id, @Value)", (SqlConnection)(pool.Connection));
            sqlCmd.Prepare();

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            for (int num = 0; num < records; num++)
            {
                SqlTransaction trans = (SqlTransaction)pool.Connection.BeginTransaction();
                sqlCmd.Transaction = trans;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Id", num);
                sqlCmd.Parameters.AddWithValue("@Value", num.ToString());

                sqlCmd.ExecuteNonQuery();
                trans.Commit();
            }
            stopWatch.Stop();
            TimeSpan ts = stopWatch.Elapsed;
            double totalSeconds = ts.TotalSeconds;
            Console.WriteLine("Total time to insert {0} records in LocalDb is {1} seconds", records, totalSeconds.ToString());
        }

I delete all the rows before calling InsertUsingPool

                Console.WriteLine("Enter number of rows to be inserted:");
                string local = Console.ReadLine();
                for (int i = 0; i < 100000; i++)
                {
                    LocalDb.DeleteRows();
                    LocalDb.InsertUsingPool(Convert.ToInt32(local));
                    System.Threading.Thread.Sleep(50);
                }
            }
            Console.ReadLine();

Thats all of the code there is.  Setup the database once, delete all rows, insert X rows (I'm doing 10), sleep for 50ms and delete/insert again.

Only the actual insert has the timing wrapped around it.

After all iterations are done, I wrote a test program to analyze the results.  The average time was 0.0142 seconds and 92.8% of all the inserts were around that time.  However 0.2% were 5X or slower than the average.  3.8% were 2x the average.  2.7% were 3x the average.

What causes localdb to provide these inconsistent database times and is it possible to avoid this issue?

Hi

There are many things that can affect the performance of the inserts. And I can list a few but certainly not all possible reasons.

The first is the machine itself. Performance can be affected when the OS schedules another process on the thread or the disks performs some another action. Being a localBD Network would not be an issue but that is another consideration.

Within SQL itself you will also get inconsistent performance when SQL needs to perform additional actions such as data or log file growth or checkpoints.

There is also an overhead in creating an explicit transaction. Seeing as your insert is a single atomic action it automatically takes place in a transaction so removing that may help performance but would not explain the inconsistencies. 

If the insert speeds are critical to you then you could also look at the in-memory OLTP (Hekaton).

https://msdn.microsoft.com/en-us/library/dn133186.aspx

but there is not local DB implementation of this.