2

I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one.

Here is my view:

CREATE VIEW Schema1.Object1
AS
  SELECT Object2.Column1  AS Column2,
         Object2.Column3  AS Column4,
         Object3.Column5,
         Object3.Column6,
         Object4.Column1  AS Column7,
         Object5.Column1  AS Column8,
         Object5.Column9  AS Column10,
         Object5.Column11 AS Column12,
         Object5.Column13 AS Column14,
         Object5.Column15 AS Column16,
         Object5.Column17 AS Column18,
         Object5.Column19 AS Column20,
         Object5.Column21 AS Column22,
         Object5.Column23 AS Column24,
         CASE
           WHEN Object2.Column25 >= Object5.Column25
             THEN Object2.Column25
           ELSE Object5.Column25
         END              AS Column25,
         Object2.Column26
  FROM   Schema1.Object6 AS Object2
         CROSS JOIN Schema2.Object7 AS Object4
         JOIN Schema1.Object8 AS Object3
           ON Object3.Column5 = Object2.Column5
              AND Object3.Column7 = Object4.Column1
         LEFT JOIN Schema1.Object9 AS Object5
           ON Object5.Column4 = Object2.Column3
         JOIN Schema3.Object10 AS Object11
           ON Object5.Column26 = Object11.Column27 

Now, I just want to do a query like:

SELECT Column2 
FROM Schema1.Object1

...but the estimated plan includes all columns from a view even though the query doesn't return them.

Why has this happened? Can I avoid this?

Here is a link for query plan: https://1drv.ms/u/s!AhdjYi359YDTgYF6sLo8fBsj5H6Ilg

enter image description here

also available at https://www.brentozar.com/pastetheplan/?id=S1MhIB3Ff

2
  • 1
    The plan you have given doesn't seem correlated with the query text. Your query text has a single left join (on Object5) that is then turned into an inner join by the next join along (Object5.Column26 = Object11.Column27 predicate) but the plan shows a left join. Commented Mar 18, 2018 at 20:07
  • @MartinSmith, it may be the issue of plan anonymization. The real join is JOIN constant.FlagValuesView AS flag on Object5.Column26 = flag.[Empty] Where FlagValuesView: CREATE VIEW [constants].[FlagValue] AS SELECT CAST(-1 AS INT) AS [NotUsed], CAST(0 AS INT) AS [Empty], CAST(1 AS INT) AS [Checked]
    – dev
    Commented Mar 18, 2018 at 21:22

1 Answer 1

3

Each node in the plan only projects the columns minimally needed to satisfy the query correctly. You can see this by looking at the Output List of each operator. For example, the final join only lists one column. SQL Server is very good at removing unneeded projections.

I think perhaps you are expecting one or more of the joins to be eliminated. This is more tricky, because the optimizer has to be careful to not change the meaning of the query. There are four reasons to keep a join as Rob Farley describes in JOIN simplification in SQL Server:

  1. Extra columns. The join is needed to provide columns needed by the query, either because the column appears in the final result, or it is needed for some intermediate step (like filtering, or a different join).

  2. Row duplication. A join can increase the number of rows matched. For example a single row in table A might join with two rows in table B, so the result would contain two copies of the information in the table A row.

  3. Row elimination. An inner join can eliminate rows from table A that do not join with any row in table B.

  4. Added NULLs. A right or full join can introduce new NULLs where a row in table A does not match a row in table B.

SQL Server is quite good (though not perfect) at removing unnecessary joins where it is safe to do so. A join can only be removed if the optimizer has a guarantee that none of the four join effects above will affect the result.

In your case, it is likely that item #2 and #3 above applies. You may be able to make the view more simplifiable by using left joins instead of inner joins, and perhaps adding a DISTINCT to your outer query. See Rob's article for examples.

2
  • Thanks @Paul for such complete answer. IMO, It seems that row simplification is less practical in one-to-many relationship schema because of #2. Is it be better approach not to use those views for querying only subset of rows and use plain tables?
    – dev
    Commented Mar 19, 2018 at 7:58
  • 1
    @dev Quite possibly yes. It is hard to be sure with the limited detail in the question though.
    – Paul White
    Commented Mar 19, 2018 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.