4

Let's say I have a (hypothetical) table called Table1 with 500 columns and there is a view called View1 which is basically 

 select Column1, Column2,..., Column500, ComputedOrForeignKeyColumn1,...     
 from Table1 
 inner join ForeignKeyTables .....

Now, when I execute something like 

Select Column32, Column56 
from View1

which one of the below 3 does SQL Server turn it into?

Query #1:

select Column32, Column56 
from 
    (select 
         Column1, Column2,..., Column500, ComputedOrForeignKeyColumn1,... 
     from 
         Table1 
     inner join 
         ForeignKeyTables ......) v

Query #2:

Select Column32, Column56 
from Table1

Query #3:

select Column32, Column56 
from 
      (select Column32, Column56 
       from Table1) v

The reason I'm asking this is that I do have a very wide table and a view sitting on top of it (that basically inner joins to bring texts from all foreign key ids) and I can't figure out if SQL Server fetches all columns and then selects the ones that are needed or fetches only those that are needed (while also ignoring unnecessary joins etc)...if it is former then a view would not be the best for performance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Achilles
  • 1,099
  • 12
  • 29
  • 1
    Most likely something quite close to #1 - the **view** is just a *stored* statement - views are typically *not* designed to improve performance - that's not their main goal - but to provide customized "views" of the data for different groups of users / processes – marc_s Sep 13 '16 at 05:08
  • 2
    You should be aware that your "query #1" *doesn't* fetch all columns. Queries are optimized as a whole. If a subquery includes a column that the outer query subsequently ignores, the optimizer *should* be able to generate and use a plan that would not be capable of returning that column. In SQL, you tell the system *what you want*, not *how to do it*. – Damien_The_Unbeliever Sep 13 '16 at 12:18

3 Answers3

2

SQL Server query compilation can be split into phases:

  1. Parsing
  2. Binding
  3. Optimization

View resolution is performed during binding. At this stage the view reference is replaced with its definition. At this point, unused view columns will be present.

The next stage is optimization, where the bound syntax tree is transformed into an execution plan. The optimizer considers many kinds of manipulations on the execution plan to increase efficiency, and removing unused columns is one of the most basic. At this point, the unused column references will be removed.

So to answer your question, unused columns in the view definition will not impact performance, since the optimizer will be smart enough to remove them.

Note: this answer assumes the view is not indexed. For indexed views, the resolution process works differently, and there is view maintenance overhead for UPDATEs of the base tables.

Sergei Patiakin
  • 266
  • 1
  • 8
1

None of the above. SQL Server will parse the query and it will create and execution plan. The resulting execution plan is calculated based on many factors, like indexes joins, etc.

Your question cannot be truly answered by anyone other than you, examining such execution plan.

See How do I obtain a Query Execution Plan? for more information.

Ricardo C
  • 2,205
  • 20
  • 24
  • Thanks. I analyzed the query plan now, there seem to be more keylookups when the query is run via a view (when the actual query is more or less same). The query ran directly on the table there are just index scans and hash matches. Guess this needs more investigation, I had assumed that they would be equivalent. – Achilles Sep 12 '16 at 23:54
  • @Achilles The reason you see a difference in the execution plan is because you removed the the inner join in #2 and #3 making the queries non equivalent. If you used outer join instead that entire branch/table could be removed from the execution plan even if you use the view as a source. At least as far as you don't get any columns from the outer joined table. But for inner joins there has to be a check for matching rows. – Mikael Eriksson Sep 13 '16 at 06:10
1

The view definition is merged with the outer query in very early stage of compilation. You may or may not get the same execution plan for query on a view vs an equivalent query touching base tables, depending on complexity of the view and given the limitations of QO.

For your particular case it's worth noting that an inner join doesn't only fetch data from joined tables, but it also limits the result (in the same way as an IF EXISTS check does). If there is a declarative FK between the tables, the QO will be smart enough not to check the referenced tables, as the existence is guaranteed by the constraint, but otherwise it has to.

dean
  • 9,960
  • 2
  • 25
  • 26