I have a stored procedure that is executing an INSERT statement that we are seeing significant delays when executing. When running it from our C# .NET application to insert 30 records in a row, it's taking roughly 4 seconds total to complete (only counting the tame it takes to run the SqlCommand.ExecuteNonQuery
() method). However, calling the same exact stored procedure from within SQL Server Management Studio the same number of times only takes about 0.4 seconds. I can't figure out what's different between the 2 setups that would make such a difference of 10x speed.
I have tried all of the following with no noticeable change in speed:
1. Creating the stored procedure "WITH RECOMPILE"
2. Checking all of the "SET" values that are configured within SSMS and C#. The only difference one was SET ARITHABORT, which was ON in SSMS and OFF when called from the .NET application. Adding "SET ARITHABORT ON" to the start of the stored procedure made no difference, though.
3. Removed all default values from the sproc parameters
The code used to call the stored procedure from the .NET application is:
using (SqlConnection newConn = new SqlConnection(connectionString))
using (SqlCommand uCmd = new SqlCommand("sproc_name", newConn))
uCmd.CommandType = CommandType.StoredProcedure;
//About 15 parameters added using:
//One output parameter
SqlParameter paramOUT = new SqlParameter("@OutPutKey", SqlDbType.UniqueIdentifier);
paramOUT.Direction = ParameterDirection.Output;
The stored procedure itself is just a list of set commands (SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ARITHABORT ON), a list of non-defaulted parameters, and the setting of the output variable that will be the new uniqueidentifier that will be inserted as the primary key in the table, followed by the INSERT statement itself.
The application is build on .NET 4 and the SQL server is MS SQL Server 2005.