Execution of a View

Question

Execution of a View

J Gross

Valued Member

Points: 61
More actions
June 2, 2008 at 12:45 pm

#93483

Hi Everyone,
Quick question regarding how SQL Server 2000 handles the execution fo a view. If I am writing a query against a view that has a WHERE clause, does SQL Server build the entire Virtual table from the view before applying my conditional statements?
Thanks!
Jason

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

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

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

The View is treated just like a table. In essence, I believe that your query is merged into the view for the execution plan. It doesn't materialize the view and then attempt to apply your criteria,

Is there something you are concerned about?

J Gross Valued Member Points: 61 More actions · Answer 2

Thanks for the reply. I was concerned that the View would create the virutal table and populate with all the data prior to executing my query. The view I was given access to was poorly written and the owner of this database will not allow access to the tables or create a new view for me.

The main concern would have been regarding performance but if my query is merged, it shouldn't be as big of a problem.

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 3

Gotta be a little bit careful here... what Steve says is absolutely true... unless the WHERE clause is on a calculated or aggragated column in the SELECT list of the view... then it is very possible that the entire view must resolve in order to return what you want.

A good test is, of course, to first do a SELECT TOP 10 * from the view with no criteria. If it doesn't return almost immediately, you may have a problem (we had one that took 2 hours to return the top 10... VERY poorly written view of a view...).

Next test after that, of course, is to use whatever criteria you had planned in the WHERE clause with a top 10 in the Select, again... if it doesn't return within a reasonable amount of time, then you could have a serious performance problem with the view... it may be fully materializing before returning even a Top 10.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

There's actually two scenarios. If the view is indexed, then it essentially IS a table of sorts: the view is "materialized" as a worktable, and the results are then indexed. Of course - this happens BEFORE the view is used in another query.

If on the other hand, the view is a "regular", non-indexed view, then when the outer query (the one that uses the view) is called - the TEXT of the view is dumped into the outer query (I believe that's called "expanding" the view), and then the "new" outer query is executed. Whether any portion of the outer query is turned into a work table and materialized then depends on what the optimizer think is the best way to handle.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

J Gross Valued Member Points: 61 More actions · Answer 5

Great information everyone. That is why I love coming here. Thanks for all your help!