Select from view differs from its definition

Question

Select from view differs from its definition

Konrad Willmert

Old Hand

Points: 361
More actions
March 18, 2008 at 9:09 am

#183364

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Konrad Willmert Old Hand Points: 361 More actions · Answer 1

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:

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

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.

Konrad Willmert Old Hand Points: 361 More actions · Answer 3

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.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 4

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)