• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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