Steven Borges Steven Borges - 1 month ago 8
C# Question

SqlCommand execution slower if Management Studio Query Window is not open

I've been currently working on an application that is supposed to test certain scenarios while SQL Server is running, like for example, PC reset as if power went down.

The code is fairly simple, I declared a static class that contains the declaration of the SqlConnection:

public static void CreateConnection(string connectionString)
{
connection = new SqlConnection (connectionString);
}


And two classes that both Open and Close the connection (closing irrelevant for this question but still inserted it for completion purposes):

public static void OpenConnection()
{
if (connection == null) return;
connection.Open ();
}

public static void CloseConnection()
{
if (connection == null) return;
connection.Close ();
}


The connectionString is nothing special either, just the Data Source, login and password, no more parameters set for that one.

So, the process of the application is the following.


  1. Start app

  2. Create Connection

  3. Open Connection

  4. Create Database if it does not exist

  5. Create Table if it does not exist and add an index to a column

  6. Start a Timer that executes a class that contains the INSERT statement

  7. Execute closing app (irrelevant)

  8. Closing event closes connection (irrelevant)

  9. App got closed (irrelevant)



Sorry for step 7 through 9, couldn't resist inserting them.

The Timer that is executing that very INSERT statement is running it everytime in a 50ms interval, and of course, from step 3 through 6 never did I close the app or connection, I just let it be open.

Now here comes the strange behavior which I found interesting and I decided to ask here since I couldn't find anything online about it:
You can literally notice the stuttering in the counter I inserted on the GUI to track how fast it is being executed, example: 1 2 3 4 .. 5 6 7 8 .. 9 10 11 12....and so on, there is a visible stutter.

Now here comes the strange thing, as soon as I open Management Studio 2014, navigate to my Database and open the "New Query" window, the INSERT statement becomes blazing fast on my app!
What previously had stuttering was running like a Golf MK2 with 800 horsepower, absolutely fluid like I was expecting the moment I wrote it, no more stuttering like explained above.

And after I close Management Studio, it goes back to stuttering until I do the same process of getting into New Query again.
What could be the cause to it?

Oh, of course, here is my INSERT statement, sorry for the long read:

public static void InsertValues(string tableName, string dbName)
{
string query = @"INSERT INTO " + dbName + ".dbo." + tableName + " VALUES ('" + Text + "','" + Date + "')";
using (var command = new SqlCommand (query, connection)) {
command.ExecuteNonQuery ();
}
}

Answer

Set the AUTO_CLOSE database option to OFF using the ALTER DATABASE statement. The effect you're seeing may be caused by the database being closed after execution of your command due to the connection being cleanly closed, making it necessary to fully reopen the entire database for the subsequent execution of the command. Opening a query window opens and maintains a connection, thus preventing the automatic closing of the entire database.