Are Views optimized when I add a WHERE clause to them?

Question

Does it make a difference if you filter a View inside or outside the View?

For example, is there any difference between these two queries?

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

Or

SELECT Id
FROM MyView
WHERE SomeColumn = 1

And MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

And is the answer any different if the source table is located on a Linked Server?

I'm asking because I have to query a large table (44mil rows) twice from a linked server, and get an aggregate of the results. I want to know if I should create two views to access the data, one for each query, or if I can get away with a single view and a WHERE clause.

why would you even use a view if you only have one table in it? — HLGEM, Mar 21 '12 at 17:11
@HLGEM The View actually contains multiple queries to multiple databases on different servers, and it joins them all by a UNION ALL. It's much easier to use a View than to have to rewrite the UNION query anytime I need the data. — Rachel, Mar 21 '12 at 17:35
@gbn The question you linked seems to contradict what the answers here say about the performance being the same. Is the correct answer that SQL will insert the text of a View into your query where the View name is, and then figure out the execution plan for it, which will usually result in the same execution plan as putting the View's query in a sub query? — Rachel, Mar 21 '12 at 18:06
all I can say is "it depends": but the predicate may be applied too late and not optimised. and yes, a view is just a macro as I keep saying http://dba.stackexchange.com/search?q=user%3A630+%2Bview+%2Bmacro — gbn, Mar 21 '12 at 18:10
Be warned: views that make your life simpler, will make somebody else's life simpler. Somebody is eventually going to discover your view and say "Wow, this is great! I can't wait to join this view to 5 other views". Pretty soon you a very slow processes, and you won't be able to change your view because so many other queries out there rely on it. — datagod, Mar 21 '12 at 19:45
@datagod I'll keep that in mind, thanks :) In this case, there's a fairly small app that gathers data from a bunch of servers, runs some calculations, and spits out a bunch of reports. It has it's own database because some of the calculations are fairly resource intensive, and I wanted to separate it from everything else. — Rachel, Mar 21 '12 at 20:00
Regarding the linked server part. One example where this depends on linked server options (collation compatibility) — Martin Smith, Mar 21 '12 at 22:53

score 17 · Answer 1 · answered Mar 21 '12 at 17:17

17

You should see absolutely no difference in the plans or the performance between these two choices. When the view is queried, it is expanded out to a query against the base table, which means the same seek or scan will be used.

Now, depending on the data type and selectivity of MyColumn, if you wanted to create a filtered index on the base table (when you move to SQL Server 2008+), you might get better performance, but this again will not be different via the view or without.

answered Mar 21 '12 at 17:17

Aaron Bertrand

180,303
28
400
614

3

What about this question, which is asking why a query with the where clause outside the view takes so much longer than when it's put in the view? – Rachel Mar 21 '12 at 19:07
1

If views are not for performance they are just for structure? – profimedica Jul 23 '14 at 16:19
2

@profimedica indexed views can be created for performance reasons (e.g. to store intermediary results like aggregates instead of calculating them at runtime). If a view is not materialized, it can be for a variety of reasons: DRY (common join or filter performed in many different queries), security, obfuscation, schema simplification. – Aaron Bertrand Jul 23 '14 at 17:13

score 9 · Answer 2 · answered Mar 21 '12 at 17:26

9

Here's just a quick example showing that there should be no difference. The database is the AdventureWorks database.

Two View definitions:

create view Person.vContactWhere
as

    select *
    from person.Contact
    where ContactID = 24

go

create view Person.vContactNoWhere
as

    select *
    from person.Contact

go

Here would be the first query, with the WHERE clause included in the view definition:

select *
from person.vContactWhere

Here is the execution plan:

enter image description here

And the second query, with the WHERE clause not in the view definition, but in the SELECT query:

select *
from person.vContactNoWhere
where ContactID = 24

Here is that execution plan:

enter image description here

As you can see from these execution plans, they are identical with identical results. I don't know of a situation where this type of logic/design would ouput different results. So I'd be willing to say you are safe either way, and go with personal preference (or shop procedures).

answered Mar 21 '12 at 17:26

Thomas Stringer

42,224
9
117
154

1

What about this question, which is asking why a query with the where clause outside the view takes so much longer than when it's put in the view? – Rachel Mar 21 '12 at 19:07
1

@Rachel I think gbn explained it rather well in his post, and the article he pointed to. I don't know how else to put it. – Thomas Stringer Mar 21 '12 at 19:19
1

I linked that because in that case, the execution plans were not the same, which is different from what your answer says. – Rachel Mar 21 '12 at 20:09
2

@Rachel The issue in that example is a missing transformation rule. It doesn't just apply to views but also CTEs and other table expressions. In the general case it is not valid to push the predicate down into table expressions containing ranking functions as that will change the result. The reason it is valid in this case is because the Where clause fits the PARTITION BY. SQL Server 2008 seems to have a new rule SelOnSeqPrj to recognize this particular case. – Martin Smith Mar 21 '12 at 21:12
2

@Rachel - More info about this issue here – Martin Smith Mar 10 '13 at 09:38

score 4 · Answer 3 · edited May 23 '17 at 12:40

4

Based on what I'm reading, SQL will use a standard view like a sub query when determining the execution plan.

So using my example query,

SELECT Id
FROM MyView
WHERE SomeColumn = 1

where MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

it should generate the same execution plan as

SELECT Id
FROM 
(
    SELECT Id, SomeColumn
    FROM MyTable
) as T
WHERE SomeColumn = 1

but this execution plan may be different from what would be generated with

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

I'm not sure if this answer would be the same for Indexed Views

edited May 23 '17 at 12:40

Community

1

answered Mar 21 '12 at 18:13

Rachel

8,477
20
50
74

I don't think it's an explicit text replacement like that. – Aaron Bertrand Mar 21 '12 at 18:19
@AaronBertrand You might be right. I honestly have no idea... I'm learning as I go :) That assumption was based on other things I've been reading about how views are like macros. I edited the question slightly to specify that I'm referring to standard views, not indexed views. – Rachel Mar 21 '12 at 18:34
@Rachel - The substitution happens with the algebrized tree not at a textual level. – Martin Smith Mar 21 '12 at 18:49
@MartinSmith Hrrmm isn't that what I said? That the execution plans should be the same, not that the text would be the same? I'm not sure that I'm understanding "algebrized tree" – Rachel Mar 21 '12 at 18:53
It was just in response to your comment on the Q itself which says that it "inserts the text of a View into your query" and Aaron's comment above. Some info about parse/compile stages here. Actually your answer does mention text substitution as well. Whether this is a distinction worth making. Not sure! But I guess it explains why sp_refreshview is needed which the text substitution concept wouldn't. – Martin Smith Mar 21 '12 at 18:56
@MartinSmith Oh you're right, I missed that. I'm still trying to figure out how this works.... thanks :) – Rachel Mar 21 '12 at 19:32
Just remembered this question where the execution plan for a View is different from the plan for a CTE/sub query. – Martin Smith Mar 22 '12 at 10:58

Are Views optimized when I add a WHERE clause to them?

3 Answers3