• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

SQL stored procedure vs query in Query analyser

I have a stored procedure which we have been using for years at work  but suddenly this last week it is taking 14 to 15 hours to run compared to 5 mins before.
We put new indexes on the tables and I took the query out of the stored procedure, declare the parameters, give them values and run it on Query analyser and it only took 2 mins but still when I run the stored procedure with the same values it still hasn't returned after 17 mins.

I don't understand why this is happening or how to fix it.
Can anyone help?
0
Niall292
Asked:
Niall292
2 Solutions
 
derekkrommCommented:
can you copy/paste both the proc and the query you're running in query analyzer?
0
 
mastooCommented:
The sql inside a proc will use parameterization, which can be more susceptible to out-of-date statistics.  An easy test is to declare local variables at the beginning of the proc for each input parameter, copy the input variables to the local variables, and then switch the sql to use those local variables.  This fools the optimizer into not doing parameterization.
0
 
SharathData EngineerCommented:
Did you check the query plan? Analyze the query plan. Also you can run DBCC FREEPROCCACHE to remove the old (may be "bad") plan. When you execute the SP, it will be recompiled.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Niall292Author Commented:
I apologise for taking so long to get back but another busy day.
I have attached the query. It is the same query for both the stored procedure and when I run it on my machine the only difference being I declare the parameters when I run it on my machine and set values to them.

I think you will agree there is alot going on in the query and it migh not be the best way to do it.
QUERY.txt
0
 
Anthony PerkinsCommented:
As suggested previously, you have the classic symptoms of parameter sniffing and you really need to read up on it.
0
 
Anthony PerkinsCommented:
Unrelated to your question, you will find you will get preformance improvement if you use appropriate data type in other words instead of:
                '0' RPC_TALKTIME,
                '0' RPC_UPDATE,
                '0' AVG_TALK,
                '0' AVG_UPDATE,--TRN_DAYS,TRN_BALANCE,TRN_TIME,
                '0' AVG_IDLE
Use:
                0 RPC_TALKTIME,
                0 RPC_UPDATE,
                0 AVG_TALK,
                0 AVG_UPDATE,--TRN_DAYS,TRN_BALANCE,TRN_TIME,
                0 AVG_IDLE
0
 
Niall292Author Commented:
I thought I had to split the points because although mastoo gave me the answer acperkins told me what to read up on to understand the problem.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now