Solved

Stored Procedure Much Slower in .NET SqlCommand than in SSMS

Posted on 2013-02-04
8
1,265 Views
Last Modified: 2013-02-11
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();
   }
}

Open in new window


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.
0
Comment
Question by:DigitechComputer
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 38853156
if i understand you correctly there is a significant difference between calling from your application a stored procedure 15 times and locally looping 15 times around in ssms calling the same stored procedure....


can you post the actual stored procedure code being invoked...

can you also explain what the intention of the application process is... if you have a scenario where you wish to insert/add/amend a set of data to your database you are probably better of thing and designing your application to pass the whole "set" of data to the database in as few steps as necessary  ie on a collection basis? rather than an individual object basis....
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38854005
hi

you should trace the execution of the proc in PROFILER and view the execution plan that the .net app connection receives and compare it with the execution plan your get when you execute the proc directly from SSMS.

Aaron.
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 total points
ID: 38854177
When you send your commands one by one, there is a communication with some overhead for each command. This overhead is not there when running inside of Management Studio, which is also more closely linked to the server.

You could improve the execution by sending your commands in batch instead of one by one. Lookup UpdateBatchSize in the documentation.
0
 

Author Comment

by:DigitechComputer
ID: 38857525
It turns out there was an issue in my initial testing environment, and the execution times of the insert stored procedure from SSMS and .NET are actually about the same.  Both are still slower than I'd like them to be, though.

I looked into adding them all at once as you suggested.  The application is typically saving 1-30 (usually fewer) business objects at a time, calling a different insert stored procedure for each.  I don't think I would benefit from any sort of bulk inserting, but maybe you have something else in mind.

I did try having the calls to these sprocs all share a single SqlConnection which is only opened once at the start and closed once when all of the inserts are done.  I also changed like business objects to share the same SqlCommand object, just modifying the parameter values instead of redeclaring them.  These changes gave me about a 25% boost in performance.  Do you have any other ideas for how this setup can be improved?  

Here is an example of the insert stored procedure I use:

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
	) 

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 38857587
if you are only inserting one row then there is no need for this

declare @newCreateDate datetime
set @newCreateDate=getDate()


just reference getdate() directly in the values list...


the only indication of a saving is the fact that you have an output variable defined for the key.... why do you need that?

presumably to use in some parent/child relationship....

so could you not pass that collection of parent/child data just once to sql and let a larger stored procedure handle all the necessary update in one interaction....
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 38857679
You cannot have a more basic INSERT as this one. And you find that it is too slow with no more than 30 inserts at a time?

I would look for the structure of the database, specially at the proper use of indexes and triggers.

Triggers are not evident in an INSERT SQL command, but can slow down things in the background. Improving a trigger can thus improve your INSERT. Working in transaction mode can sometimes improve things. Most programmers will tell you that it does nothing with INSERT since you are not hitting the same record more than once, but if the triggers do end up hitting the same row(s) many times, working inside of a transaction could help a bit.

Many database designers are not aware that indexes improve things on SELECT and UPDATE, but slow down things on INSERT. I have seen a few instances of indexes that where used only a few times a year for special reports. These slow down INSERT operations all year long only to save time once or twice on yearly reports.

I also wonder why you need the NewID function. Couldn't you use a GUID primary key instead of generating your own?
0
 

Author Comment

by:DigitechComputer
ID: 38860356
I will directly call getdate() in the values list. I had thought that calling a function in an insert wasn't allowed, as is the case with sproc parameters.  I should also be able to eliminate the output parameter, creating the primary key value on the .NET side.  

The table in question doesn't use triggers, but I was able to eliminate 3 mostly-unneeded indexes.  

It's very hard to tell since the more I run this query, the more SQL optimizes it, but unfortunately these changes don't seem to have made much of a difference.  It even seems to be taking slightly longer to save now, but that's almost certainly because SQL hasn't fully optimized the new structures yet.  A single call to the sproc with empty values in its parameters is currently taking about 25ms, which still seems higher than it should be.  Is there anything else I can look at to improve performance other than hardware and network setup?
0
 

Author Closing Comment

by:DigitechComputer
ID: 38877826
So it turns out that having a clustered index on a non-sequential uniqueidentifier field is a bad idea.  Changing the clustered index to a sequential field like CreateDate made a very noticeable improvement in save times.  Combined with your other suggestions, INSERTs are performing a lot better than they used to.  Thanks to all for your suggestions!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now