?
Solved

SQL stored procedure vs query in Query analyser

Posted on 2011-03-04
7
Medium Priority
?
363 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 1200 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 800 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

800 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