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?
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?
derekkromm
can you copy/paste both the proc and the query you're running in query analyzer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_
'0' AVG_IDLE
Use:
0 RPC_TALKTIME,
0 RPC_UPDATE,
0 AVG_TALK,
0 AVG_UPDATE,--TRN_DAYS,TRN_
0 AVG_IDLE
'0' RPC_TALKTIME,
'0' RPC_UPDATE,
'0' AVG_TALK,
'0' AVG_UPDATE,--TRN_DAYS,TRN_
'0' AVG_IDLE
Use:
0 RPC_TALKTIME,
0 RPC_UPDATE,
0 AVG_TALK,
0 AVG_UPDATE,--TRN_DAYS,TRN_
0 AVG_IDLE
ASKER
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
Thanks