Starting the same Saved procedure from a C # .Net application over the network is becoming slower with each subsequent execution. It seems that it takes twice the amount of time as the previous execution (to the maximum value, reading). The runtime gets slower until 1 of 2 scenarios occurs, after which the first SPROC run is again “fast”.
- If
SqlConnection open and remains open during testing, SPROC becomes slower until any other SPROC or query is executed. - If
SqlConnection opens and closes with each execution, SPROC becomes slower until at least 8 minutes have passed.
This only happens with a few stored procedures. One of them is a simple SELECT query with 2 JOINs , (SPROC 1) the other is a massive SPROC line (SPROC 2) with support for 1600+.
The run time does not exceed 60 seconds for SPROC 1 and 67 seconds for SPROC 2. SPROC 1 takes less than a second to execute it first, and SPROC 2 takes 7 seconds.
This also happens if SPROC is started using the same SqlConnection in the application. As soon as 2 separate SqlConnection objects are used, they behave in the same way as above, but are independent. Running SPROC several times on SqlConnection1 is gradually slower, but the first time the same SPROC is running on SqlConnection2 , it is "fast." Then it will run slower when running multiple times on SqlConnection2 .
This does not happen if the application runs on the same computer with SQL Server 2008 R2 installed (running Windows Server 2008). The lead time is always agreed.
Running a stored procedure from Studio also does not slow down with each execution; it is always agreed upon.
Removing the execution plan cache (in SQL Server) does not affect the observed behavior.
It took quite a few days to narrow down this question, which was initially observed in a much larger application, in order to create a test application to easily test / reproduce it.
From what I read here , there is a time of 4 to 8 minutes (after SqlConnection.Close() is called in the code), after which it closes the database connection to the data source. This seems to be consistent with scenario 2 mentioned above.
This leads me to believe that this is due to the SqlConnection used (and the basic connection of the database to the data source), since the connection pool is enabled in my case, but why am I observing this behavior and how can I fix it?
We use the .NET Framework, if that matters.
There are many small details listed above, so please let me know if I need to clarify something.
The only question about stack overflow with any similarities is this one , but was not related to my problem.
Edit: In my WinForms test application, the following code is executed at startup:
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(); connectionStringBuilder.DataSource = m_DataSource; connectionStringBuilder.InitialCatalog = m_InitialCatalog; connectionStringBuilder.UserID = m_UserID; connectionStringBuilder.Password = m_Password; connectionStringBuilder.IntegratedSecurity = false; connectionString = connectionStringBuilder.ConnectionString; m_DatabaseConnection = new SqlConnection(connectionString);
I have 2 buttons; one of which calls SPROC 1 mentioned above, and the other calls another SPROC, which does not have the same slowdown problem. The following code is executed when the button is pressed (only the difference is the name SPROC):
m_DatabaseConnection.Open(); m_DatabaseCommand = new SqlCommand("GetCompanies", m_DatabaseConnection); m_DatabaseCommand.Parameters.AddWithValue("@StatusID", StatusID); m_DatabaseCommand.CommandType = CommandType.StoredProcedure; m_DatabaseCommand.CommandTimeout = 0; SqlDataAdapter databaseDataAdapter = new SqlDataAdapter(m_DatabaseCommand); DataSet databaseDataSet = new DataSet(); databaseDataAdapter.Fill(databaseDataSet); m_DatabaseConnection.Close();