Solved

SQL stored procedure vs query in Query analyser

Posted on 2011-03-04
7
358 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Split string into 3 separate fields 5 22
Need to trim my database size 9 29
SQL Query Across Multiple Tables - Help 5 25
SQL Help 4 19
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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