March 18, 2008 at 9:09 am
What would cause a view to behave differently when its defining SELECT statement is run versus when you SELECT * FROM dbo.VIEWNAME?
I have a view which takes about two and a half minutes to run when I execute it's defining SELECT, but 15+ minutes when selecting from it by name. The (estimated) execution plans are identical.
March 18, 2008 at 9:32 am
Well I copied my SELECT * FROM dbo.VIEWNAME into a different query window and boom, 2.5 minutes. I may have had that query window open for a day or two, but still a strange behavior.:crazy:
March 18, 2008 at 9:49 am
Depending on the view, it's possible that you have cached the data after the first run and the second runs faster.
Without more information, it's hard to tell what might be wrong.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 18, 2008 at 10:37 am
The results were consistent across multiple runs. It had to be some left-over resources or something tied to the original query window. I guess I'll just have to remember to reconnect when strange things like that happen.
March 18, 2008 at 11:08 am
Execution plans changed because of a recompile? That's a pretty likely possibility.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2008 at 11:50 am
More likely, since you said you had the window open for days, the data in the tables the view references changed. Doesn't take a lot... if your execution plan managed to stay cached all that time, it could have been for a relative handful of rows and suddenly the tables are bigger... same execution plan gets used and BOOM... slow query.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply