February 2, 2010 at 2:24 am
I have this rpoblem with a SP that when I execute the code it runs in +- 30 sec. If I run the SP with the exact same code it times out. The only thing I can thing that causes the is execution plan but I created a new SP with dif name and same thing happens. Does anyone have any Idease?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 2, 2010 at 4:03 am
example
exec sp_temp - this times out
code from sp -
select * from t1
join viewa on a=b
join t2 on c=d
this executes in 30 sec
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 2, 2010 at 6:00 am
A couple of things, have you looked at the execution plan? If you have and you don't understand what's happening there, save it as a .sqlplan file and post it. The other is, you're joining between tables and views. Joining views can be problematic becuase you can get all the tables that make up a view when you only need one or two of them. Be sure the only way to access the data is through the view and understand what the view is doing itself so that you're not adding tons and tons over overhead.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2010 at 7:13 am
More information would lead to any guestimates, at this point, would only ask for checking the Indexes and Stats on those tables/views, mainly on those columns that are used in the Join clause.
Reply with table definitions and the exec plans would be easier ffor providing more help.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2010 at 10:58 pm
Yesterday afternoon I got the SP to run in 1 min by substituting one of the join with temp table. This works fine but what I do not understand is why SQL 2008 would create different execution paths for adhoc and SP. It does not make sense and feels to me like a bug. I am unable to get the excecution plan for the original sp as it never finishes. I have the same problem with our dataware house where SP excecution plan differs from adhoc. I will try and get an example that differs alot and post it here.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 3, 2010 at 1:11 am
February 3, 2010 at 1:36 am
tvantonder-992012 (2/2/2010)
This works fine but what I do not understand is why SQL 2008 would create different execution paths for adhoc and SP.
This is because when using adhoc , SQL Server KNOWS the SARG values and will generate an execution plan for those values. In a SP , parameter sniffing aside, SQL server with produce a plan based upon average statistics.
Try this link http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html
February 3, 2010 at 2:09 am
I did create a new sp under new name with recompile. This did exactly the same. Even trying to get estimated execution plan on the sp runs more than and 1 hour. I am unable to have a test running this long as it takes up too much resources even on dev server.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 3, 2010 at 6:25 am
Would you be able to share the code that exists in the SP, may be something in that needs to rewritten or changed..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 3, 2010 at 7:08 am
tvantonder-992012 (2/3/2010)
I did create a new sp under new name with recompile. This did exactly the same. Even trying to get estimated execution plan on the sp runs more than and 1 hour. I am unable to have a test running this long as it takes up too much resources even on dev server.
Taking an estimated execution plan should only take however long the compile time is on the query. A stored procedure and an ad hoc query with identical code will not have radically different compile times (although the plans may be different). Are you sure you're getting the estimated execution plan? You know that you don't need to run the query to get the estimated plan, right?
How big is this query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2010 at 10:37 pm
Thanks for all the help so far. When I try to get an estimate execution plan on just the code it gives me this error:
Msg 208, Level 16, State 0, Line 121
Invalid object name '#Temp'.
Does this mean anything? Can this cause QO to get stuck when I try the SP?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 3, 2010 at 11:07 pm
Hi here is the SP I run and the execution plan of the code only run hope it helps. Please let me know if you need more information.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 4, 2010 at 12:14 am
Here's what I would do:
- consider adding the missing index that the sqlplan mentions
- use a temp table #timesheets rather than a table variable @timesheets to see whether that makes a difference
- check whether the union is really needed or a union all will work as well
- try to eliminate the second part of the union all together; it may be more efficient to run a query on #timesheets after the first part has completed to add these records (so not having to do all the joins and calling the udf again)
February 4, 2010 at 12:23 am
What does the udf do that could not be done in a stored proc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 12:44 am
wschampheleer (2/4/2010)
Here's what I would do:- consider adding the missing index that the sqlplan mentions
- use a temp table #timesheets rather than a table variable @timesheets to see whether that makes a difference
- check whether the union is really needed or a union all will work as well
- try to eliminate the second part of the union all together; it may be more efficient to run a query on #timesheets after the first part has completed to add these records (so not having to do all the joins and calling the udf again)
Hi thanks for the suggestions. I did all this and did fix the SP(except index). My question is, why does the SP not work but the ad-hoc does work. If I know this I can check all SP for similar problem.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply