Solved

SQL stored procedure vs query in Query analyser

Posted on 2011-03-04
7
360 Views
Last Modified: 2012-05-11
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
Comment
Question by:Niall292
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35037963
can you copy/paste both the proc and the query you're running in query analyzer?
0
 
LVL 21

Accepted Solution

by:
mastoo earned 300 total points
ID: 35038423
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35039453
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Niall292
ID: 35059745
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 35060096
As suggested previously, you have the classic symptoms of parameter sniffing and you really need to read up on it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35060141
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
 

Author Closing Comment

by:Niall292
ID: 35060803
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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