Performance of views

Gareth Osler 21 Reputation points
2022-06-21T10:50:57+00:00

I have a view that uses another view several times in the FROM clause of more than one of its subqueries. Is the second view in the FROM clause of several subqueries cached once it has been calculated, or is the query calculated every time the view is used in a FROM clause?

I have a query that seems to indicate that a view used in more than one subquery is run every time it is used.

Thanks,

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 116.5K Reputation points MVP
    2022-06-21T22:11:29.497+00:00

    As Olaf says, a view is essentially a macro, and at compilation the view text is replaced with the view definition. Optimization is then performed on the expanded query, and the view as such may not be computed.

    However, when a view appears multiple times in a query, this typically leads to extra processing, as SQL Server does not have a way to find that a pattern reoccurs in a query.

    The exception is if the view is an indexed view, then SQL Server may match the view against the clustered index, if you are on Enterprise Edition, or you use the NOEXPAND hint.

    So short summary: having the same view reappearing in the query may not be good for performance, although the exact consequence depends on the specific case.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 45,881 Reputation points
    2022-06-21T11:20:05.37+00:00

    A view is just a predefined query, there is nothing special calculated or chache.
    SQL Server use the buffer pool to cache data to fullfil queries.
    Check the execution to see how the view(s) are processed.

    0 comments No comments

  2. LiHong-MSFT 10,051 Reputation points
    2022-06-22T02:20:14.747+00:00

    Hi @Gareth Osler

    a view used in more than one subquery is run every time it is used.

    There is no such thing as an execution plan or cache for views.When Views are used as part of a query, it is inlined into the query plan as if you had pasted its definition into the text. The query optimizer knows nothing about views (except indexed views).
    Whether the execution plan of a query that uses a view is cached depends on the same factors as any other query.

    Best regards,
    LiHong

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.