CODEWITHSUNDEEP
c#.netsqlssms
Slippy
Slippy

Reputation: 55

Stored Procedure Much Slower in .NET SqlCommand than in SSMS

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;
      uCmd.Connection.Open();

      //About 15 parameters added using:
      uCmd.Parameters.AddWithValue("@ParamName", value);
      ...

      //One output parameter
      SqlParameter paramOUT = new SqlParameter("@OutPutKey", SqlDbType.UniqueIdentifier);
      paramOUT.Direction = ParameterDirection.Output;
      uCmd.Parameters.Add(paramOUT);

      uCmd.ExecuteNonQuery();
      uCmd.Connection.Close();
   }
}

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.

Here is an example of the insert stored procedure it's calling:

alter procedure InsertStuff
@Field1 uniqueidentifier,
@Field2 datetime,
...
@CreateDate datetime,
@PrimaryKEY uniqueidentifier OUTPUT
AS

declare @newCreateDate datetime 
set @newCreateDate=getDate()

set @PrimaryKEY = NEWID()

INSERT INTO [dbo].[Table]
(
    Field1,
    Field2,
    ...
    CreateDate,
    PrimaryKEY
)
VALUES
(
    @Field1,
    @Field2,
    ...
    @newCreateDate,
    @PrimaryKEY
)

Upvotes: 1

Views: 3176

Answers (1)

Andy
Andy

Reputation: 8562

Likely the issue is that every execute command call does a network hop, where as ssms will send all 30 commands to the server at once in a batch. I believe by default SSMS will send all 30 statements as a single batch, but if you've changed other settings that may impact things as well.

Also, make sure youre not opening and closing the connection each time. While connection pools may make that a non issue, I wouldn't leave it to chance.

Upvotes: 2

Related Questions