June 4, 2008 at 1:15 pm
I have a stored proc that returns a result set. Several of the columns in that result set a coming from a view. If I save the proc and execute, it takes 6 seconds or so to run, using a specific test. If I alter the view and save (without actually changing anything), that same proc running with the same criteria takes 1 second to run.
The execution plan of the proc completely changes when I save the view, which I'm sure is causing the difference in speed, but the amount of difference concerns me. Is there something I can or should be doing differently that might reduce the difference? I have a number of processes that I'm trying to speed up. If I alter the proc to test out a change, how do I know that the plan I'm using is correct? Based on this example, I'd be very likely to discard an attempt that might be the correct design.
June 4, 2008 at 2:16 pm
Saving the view probably caused the proc to recompile its execution plan.
I generally try to avoid using views in most procs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 2:18 pm
It would be easier to answer your question if you posted some DDL, your queries, and the execution plans. It could be as simple as the statistics were updated between runs of the stored procedure so a better execution plan was able to be found or the data was already in the cache so you did not have any physical reads.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2008 at 3:01 pm
I only used a view this time because the values I needed are needed across 5 different stored procedures, and I'd rather guarantee the various procs are using the same query.
I tried replacing the view with a table variable, with the insert statement matching the view definition, and I tried replacing the join to the view with a join to a subquery that matched the view definition. In both cases the proc takes 6 seconds to run. Based on this, it appears that I SHOULD be using views more often, though it's possible that it's dependant on the view.
June 4, 2008 at 3:05 pm
Jack Corbett (6/4/2008)
It would be easier to answer your question if you posted some DDL, your queries, and the execution plans. It could be as simple as the statistics were updated between runs of the stored procedure so a better execution plan was able to be found or the data was already in the cache so you did not have any physical reads.
I can't post the true queries, but I can see if I can put something together that emulates the results I got. I can duplicate the times consistently in SMS with 3 windows - one with an alter view statement, one with an alter proc statement, and the other with an execute statement.
June 5, 2008 at 6:45 am
First, based on what you said, don't use table variables. Performance will likely degrade.
Second... It really depends on what's going on here, but in general, views serve two purposes. They either mask the data from end users, not the case here, or they serve as a handy place to keep complex joins or odd syntax that you don't want to have to recreate. However, if the view is hitting a lot of tables or data that you don't need, you might want to consider not using it. First off, as an experiment, instead of using the view in your procedure, try taking the code straight out of the view and inserting it into your procedure code as a derived table (a select statement within parenthesis in the FROM clause). See what that does to your performance. Test it a couple of times and run DBCC FREEPROCCACHE to ensure you're getting a clean compile between tests. Then edit that derived table to eliminate any bits & pieces that the view references that your proc doesn't need. You'll be able to tell what's going on much better from there.
Post execution plans for more detailed or specific help.
"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
June 5, 2008 at 7:27 am
Rearranging the joins did it - it's now taking 1 second regardless of what is saved last.
Attaching execution plans would have been overcomplicated. My proc with the final statement does have a table variable that's needed for doing a recursive scan, and the example queries I gave in the other thread, while basically actual queries, were only half of the ones my view contains - and the final statement joins 5 tables, my view, and my table variable. I'm grateful for the help, the current speed is exactly what I was aiming for.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply