How are Execution Paths built?

Lets say you create a view.

then you select from that view for the first time. with no where clause.

Does it build a different execution path than it does if you select from that view with a where clause?

Hi,

Dynamic SQL statements are parameterized if possible (simple where ID = 5 might be cached as ID = ?) or the exact query text is used as a reference. So querying against your view with different criteria will generate different query plans.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Think of a view as basically a "macro" which inserts the view's select statement into your code at execution time. The execution plan is created when you use the view.

So, yes, the execution plan is different based on your where clause.

|||so would you say its better to have your first query against a view not have a where clause?
|||No. There is no reason to run a query on a view with no where clause. The execution plan is recreated every time the view is used.

|||I am not saying you are incorrect and i do appreciate your comments, but the first time i use a view it takes a little longer than all the other times i use the view. I understand this to be because it is building the Execution path and optimizing the views Execution path.|||

Yes, it would be slower the first time because of creating the execution plan. And it will use the same execution plan for similar where statements.

But, running a view with no where does nothing, unless you do that over and over. If you put in a "WHERE field=x" it creates an execution plan based on that. Then if you do "WHERE field2=y" it makes a new exeuction plan. It MIGHT have the plan cached if you have used a similar plan before, but you can't count on it.

|||

Thank you sir,

That answer helps me unserstand immensely. so it builds a different Execution path per each unquely patterened where clause..