Stored procedure execution time increased by 50%

Question

1.00/5 (1 vote)

See more:

I have a Spoc that has insert statements. There was no code change. But since past 2 weeks it has been taking 50% more time to execute. Would anybody be able to help me in understanding what could have made this?.

Posted 2-Nov-14 0:05am

Member 11199545

Add a Solution

Comments

George Jonsson 2-Nov-14 6:17am

Is it the time to do the insert or the total time including response time?
Maybe you should look at the size of the table(s) you are inserting data into.
Also check if more tasks have been added to the server. Maybe it is shared by many resources.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2014-11-02T00:16:00

Just by looking at the question, it's very difficult to pinpoint what is the real reason.

However, you need to look at the following factors:

1.) Has there been a substantial change in the dataset i.e. the amount of data your database/tables.

2.) Usually, SQL Server keeps an optimal execution plan saved for the stored procs. Clearing the execution plan, patching etc. resets those plans and a new plan is created. You new plan might be making different or incorrect assumptions. Check the execution plan [^]using SQL Profiler.

3.) Please use SQL Profiler[^] to see what's going on. It's your best bet.

4.) Understand that NO CODE CHANGE is not the only factor which impacts performance and their are lot of other factors involved.

Maciej Los · Answer 2 · 2014-11-02T00:32:00

Solution 2

I agree with Manas Bhardwaj and suggest you to read about database / query performance[^]. There is few ways to improve the time of query execution.

Posted 2-Nov-14 0:32am

Maciej Los

Comments

Manas Bhardwaj 2-Nov-14 6:34am

+5.

Always good to refer existing articles on CP. More efficient than asking questions.

Maciej Los 2-Nov-14 7:18am

Thank you, Manas ;)